Creating a Microsoft Access Table

When you take a look at Figure 11-1 you will notice the Catalog object at the very top of the ADOX Object Model. The Catalog object is extremely important as it represents an entire database and contains database tables, columns, indexes, groups, users, procedures, and views. Use the ADOX Catalog object to create a table. The following steps outline the process of creating a new Microsoft Access table:

1. Declare the variables representing the Connection, Catalog, and Table objects:

Dim conn As ADODB.Connection Dim cat As ADOX.Catalog Dim tbl As ADOX.Table

2. Open the connection to your database:

set conn = New ADODB.Connection conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\NewAccessDb.mdb"

3. Supply the open connection to the ActiveConnection property of the ADOX Catalog object:

Set cat = New ADOX.Catalog set cat.ActiveConnection = conn

4. Create a new Table object: Set tbl = New ADOX.Table

5. Provide the name for your table: tbl.Name = "tblAssets"

The Table object is a member of the Tables collection, which in turn is a member of the Catalog object. Each Table object has a Name property and a Type property. The Type property specifies whether a Table object is a standard Microsoft Access table, a linked table, a system table, or a view. To see an example of using the Type property, refer to the section called "Listing Database Tables" later in this chapter.

6. Append the Table object to the Catalog object's Tables collection: cat.Tables.Append tbl

At this point your table is empty.

7. Add new fields (columns) to your new table:

With tbl.Columns

.Append "SiteId", adVarWChar, 10 .Append "Category", adSmallInt .Append "InstallDate", adDate End With

Part II

The code fragment above creates three fields named SiteId, Category, and InstallDate. You can create new fields in a table by passing the Column object Name, Type, and DefinedSize properties as arguments of the Columns collection's Append method. Notice that ADOX uses different data types than those used in the Access user interface (see Table 11-1 for the comparison of data types).

The Table object contains the Columns collection that contains Column objects. To add a new field to a table you could create a Column object and write the following code:

Dim col As ADOX.Column set col = New ADOX.Column With col

.Name = "SiteId" .DefinedSize = 10 End With tbl.Columns.Append col

The last statement in the example above appends the new Column object (field) to the Columns collection of a table. The Name property specifies the name of the column. The DefinedSize property designates the maximum size of an entry in the column. To create another field, you would have to create a new Column object and set its properties. Creating fields in this manner takes longer and is less efficient than using the method demonstrated earlier.

Table 11-1: ADOX data types vs. Microsoft Access data types

ADOX Data Type

Corresponding Data Type in Access




Number (FieldSize = Byte)


Number (FieldSize = Integer)


Number (FieldSize = Single)


Number (FieldSize = Double)


Number (FieldSize = Decimal)


Number (FieldSize = LongInteger) AutoNumber








OLE Object





Creating and Manipulating Databases with ADO

d Hands-On 11-1: Creating a Table

1. Create a new Microsoft Office Access database or open the Acc2003_ Chap11.mdb file from the book's downloadable files.

2. In the Database window, press Alt+F11 to switch to the Visual Basic Editor window.

3. In the Visual Basic Editor window, choose Insert | Module.

4. In the module's Code window, type the Create_Table procedure shown below.

5. Choose Run | Run Sub/UserForm to execute the procedure.

' make sure to set up a reference to ' the Microsoft ADO Ext. 2.5 for DDL and Security ' Object Library

Sub Create_Table()

Dim cat As ADOX.Catalog Dim myTbl As ADOX.Table

On Error GoTo ErrorHandler

Set cat = New Catalog cat.ActiveConnection = CurrentProject.Connection Set myTbl = New Table

With myTbl

.Name = "tblFilters" With .Columns

.Append "Id", adVarWChar, 10 .Append "Description", adVarWChar, 255 .Append "Type", adInteger End With End With cat.Tables.Append myTbl Set cat = Nothing

MsgBox "The new table 'tblFilters' was created." Exit Sub


If Err.Number = -2147217857 Then cat.Tables.Delete "tblFilters" Resume End If

MsgBox Err.Number & ": " & Err.Description End Sub

Figure 11-2: A Microsoft Access table can be created program-matically by using the Catalog object from the ADOX Object Library (see the procedure in Hands-On 11-1).

Part II

Figure 11-2: A Microsoft Access table can be created program-matically by using the Catalog object from the ADOX Object Library (see the procedure in Hands-On 11-1).

0 0

Post a comment