Creating and Modifying Access Tables

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

You can view, create, and modify the structure of Access databases by using ADOX objects, methods, and properties. The following sections provide details on how to do this.

Tip   You can use a transaction to "wrap" a set of changes you make to the structure of a database; by using the transaction as a wrapper around the changes, you ensure that all work is performed as a single unit. For information about how to use transactions, see "Using Transactions" in Chapter 16, "Multiuser Database Solutions."

Creating a Table

To create a table by using ADOX

1. Open a Catalog object on the database you want to add a table to.

2. Create a new Table object.

3. Use the Append method of the Columns collection to add the field definitions (Column objects) to the Columns collection of the new Table object.

4. Append the new Table object to the Tables collection of the Catalog object.

It is not necessary to use the Create method to create Column objects for the field definitions before you append them to the Columns collection. The Append method can be used to both create and append the Column object. The following procedure creates a table named Contacts by using ADOX.

Sub CreateAccessTable(strDBPath As String)
   Dim catDB As ADOX.Catalog
   Dim tblNew As ADOX.Table

   Set catDB = New ADOX.Catalog
   ' Open the catalog.
   catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & strDBPath

   Set tblNew = New ADOX.Table
   ' Create a new Table object.
   With tblNew
      .Name = "Contacts"
      ' Create fields and append them to the
      ' Columns collection of the new Table object.
      With .Columns
         .Append "FirstName", adVarWChar
         .Append "LastName", adVarWChar
         .Append "Phone", adVarWChar
         .Append "Notes", adLongVarWChar
      End With
   End With
   
   ' Add the new Table to the Tables collection of the database.
   catDB.Tables.Append tblNew
   
   Set catDB = Nothing
End Sub

The CreateAccessTable procedure can be found in the CreateDatabase module in the DataAccess.mdb sample file, which is available in the ODETools\V9\Samples\OPG\Samples\CH14 subfolder on the Office 2000 Developer CD-ROM.

The data type names for DAO fields are different from ADOX names. The following table shows how the data types in the Access user interface and DAO map to the ADOX data types.

Setting Additional Field Attributes and Properties

In addition to specifying the data type for a field, you may also wish to specify other attributes of a field, such as whether the field is auto-incrementing (the AutoNumber data type in the Access user interface) or will be used to store active hyperlinks (the Hyperlink data type in the Access user interface). When you create an auto-incrementing or hyperlink field by using DAO, you add the appropriate constant to the field's Attributes property. To create the same fields in ADOX, you set the appropriate property in the Properties collection of the Column object that is used to create the field. The following code shows how to create an auto-incrementing field with ADOX, by setting the field's AutoIncrement property to True.

Sub CreateAutoNumberField(strDBPath As String)
   Dim catDB As ADOX.Catalog
   Dim tbl As ADOX.Table

   Set catDB = New ADOX.Catalog
   ' Open the catalog.
   catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & strDBPath

   Set tbl = New ADOX.Table
   With tbl
      .Name = "Contacts"
      Set .ParentCatalog = catDB
      ' Create fields and append them to the
      ' Columns collection of the new Table object.
      With .Columns
         .Append "ContactId", adInteger
         ' Make the ContactId field auto-incrementing.
         .Item("ContactId").Properties("AutoIncrement") = True
         .Append "CustomerID", adVarWChar
         .Append "FirstName", adVarWChar
         .Append "LastName", adVarWChar
         .Append "Phone", adVarWChar, 20
         .Append "Notes", adLongVarWChar
      End With
   End With

   ' Add the new Table to the Tables collection of the database.
   catDB.Tables.Append tbl

   Set catDB = Nothing
End Sub

The CreateAutoNumberField procedure can be found in the CreateDatabase module in the DataAccess.mdb sample file, which is available in the ODETools\V9\Samples\OPG\Samples\CH14 subfolder on the Office 2000 Developer CD-ROM.

The following table maps DAO Attributes property constants for fields to ADO provider-specific Column properties for auto-incrementing fields, as well as others.

For information about additional field properties that you can set by using the Properties collection of an ADOX Column object, see "The Properties Collection of the Column Object" in ADOProperties.doc in the ODETools\V9\Samples\OPG\Appendixes folder on the Office 2000 Developer CD-ROM.

Setting Additional Table Properties

You can also set a number of table-level properties, such as the Description property, which you can use to provide descriptive information about the table, and the ValidationRule property, which you can use to enter an expression that specifies the requirements for data entered into a record. The following table lists the three provider-specific table properties, exclusive of those used to create linked tables, that can be defined by using the Properties collection of an ADOX Table object.

To see a code example that creates a table validation rule, see the CreateTableWithValidationRule procedure in the CreateDatabase module in DataAccess.mdb, which is available in the ODETools\V9\Samples\OPG\Samples\CH14 subfolder on the Office 2000 Developer CD-ROM.

You must establish a reference to the Microsoft DAO 3.6 object library and use DAO code to programmatically set these remaining table properties: Description, Filter, OrderBy, LinkChildFields, LinkMasterFields, SubdatasheetExpanded, SubdatasheetName, and SubdatasheetHeight. For information about working with these properties, search Microsoft Access Help.