Creating Tables

There's a pattern that you see over and over again when you're creating objects with ADOX:

1. Instantiate the appropriate ADOX object.
   
   

2. Set the properties of the ADOX object.
   
   

3. Append the object to the appropriate collection.
   
   

Keep this pattern in mind as you work through creating some new tables from code.

NOTE

As you can see, the pattern for adding objects is very similar to the pattern you've already learned for adding data to a Recordset. The difference is that with ADOX you're working with your database's schema rather than its data.

Creating a Table and Columns

To build a table, you need three ADOX objects:

• The Catalog object represents the design information for an entire Jet database.

• The Table object represents a single table.

• The Column object represents a single field in a table.

To demonstrate how to add tables to a database from code, let's build a pair of tables that you might use to keep track of feature requests in any database. The Users table will consist of an AutoNumber primary key and a username; the FeatureRequests table will consist of an AutoNumber primary key, a foreign key into the Users table, and a text field to hold the feature request.

Being able to create these tables from code is useful if you're a developer who works on many different databases. Instead of building the same tables manually in every database, you can just import the appropriate module and run the procedure. Here's the code that builds these two tables, using ADOX:

 Sub CreateTables()   ' Build two new tables in the database   Dim cat As ADOX.Catalog   Dim tbl As ADOX.Table   Dim col As ADOX.Column   ' Set up a catalog pointing to this   ' database   Set cat = New ADOX.Catalog   Set cat.ActiveConnection = CurrentProject.Connection     ' Create a table   Set tbl = New ADOX.Table   tbl.Name = "Users"   ' Add some columns to the table   Set col = New ADOX.Column   col.Name = "UserID"   col.Type = adInteger    ' Jet Long Integer type   ' Associate with the provider so we can set   ' provider-specific properties   Set col.ParentCatalog = cat   col.Properties("Autoincrement") = True   col.Properties("Description") = "Unique User Number"   tbl.Columns.Append col   Set col = New ADOX.Column   col.Name = "UserName"   col.Type = adVarWChar   col.DefinedSize = 50   col.Attributes = 0  ' Not null   Set col.ParentCatalog = cat   col.Properties("Description") = "User Name"   tbl.Columns.Append col   ' And save the table   cat.Tables.Append tbl   ' Create another table   Set tbl = New ADOX.Table   tbl.Name = "FeatureRequests"   ' Add some columns to the table   Set col = New ADOX.Column   col.Name = "FeatureID"   col.Type = adInteger   Set col.ParentCatalog = cat   col.Properties("Autoincrement") = True   col.Properties("Description") = "Unique Request Number"   tbl.Columns.Append col   Set col = New ADOX.Column   col.Name = "UserID"   col.Type = adInteger   Set col.ParentCatalog = cat   col.Properties("Description") = "User making the request"   tbl.Columns.Append col   Set col = New ADOX.Column   col.Name = "FeatureRequest"   col.Type = adVarWChar   col.DefinedSize = 255   Set col.ParentCatalog = cat   col.Properties("Description") = "Feature being requested"   tbl.Columns.Append col   ' And save the table   cat.Tables.Append tbl End Sub 

NOTE

You need to set a reference (using the Tools, References menu item) to the Microsoft ADO Ext. for DDL and Security library before you can run this code. Depending on the software on your computer, you might have version 2.5, 2.6, or later available; choose the version with the highest number.

After you instantiate an ADOX Catalog object, you need to set its ActiveConnection property to tell it which database to use for storing newly created objects. In this case, you can just retrieve the connection for the current database from the CurrentProject object.

Creating a table is easy; you just instantiate a new ADOX Table object. From there, you can create Column objects and append them to the table. Note that to use any Access-specific properties on a new column, you need to set the column's ParentCatalog property first. Each Column object has a Name and a Type (types are indicated by a set of constants stored in the ADOX library) and can have other properties as well.

When you've created all the columns for a table, you can add the table to the database by appending it to the appropriate Tables collection.

TIP

If the Tables tab of the database container is open when you run this procedure, you need to select View, Refresh to see the new tables.

Creating Indexes

Just creating tables isn't enough, of course; in most cases, tables need indexes created to help you work with them more effectively. As a rough rule of thumb, create an index for any field that you intend to search or sort on a frequent basis.

In ADOX, you create an index by using the Index object, which consists of a collection of Column objects representing the indexed columns. A Table object has an Indexes collection containing all the indexes for the table. Here's some code to add indexes to the newly created tables:

 Sub CreateIndexes()   ' Build indexes for the new tables   Dim cat As ADOX.Catalog   Dim tbl As ADOX.Table   Dim idx As ADOX.Index   Dim col As ADOX.Column   ' Set up a catalog pointing to this   ' database   Set cat = New ADOX.Catalog   Set cat.ActiveConnection = CurrentProject.Connection   ' Retrieve a table   Set tbl = cat.Tables("Users")   ' Now create an index on the primary key field   ' As a side effect, this will create the   ' primary key   Set idx = New ADOX.Index   idx.Name = "PrimaryKey"   idx.PrimaryKey = True   idx.Unique = True   ' Specify the column for the index   idx.Columns.Append "UserID"   ' And add the index to the table   tbl.Indexes.Append idx   ' Create a second index on the name field   Set idx = New ADOX.Index   idx.Name = "NameIndex"   idx.Unique = False   idx.Columns.Append "UserName"   tbl.Indexes.Append idx   ' Retrieve the other table   Set tbl = cat.Tables("FeatureRequests")   ' And create its primary key   Set idx = New ADOX.Index   idx.Name = "PrimaryKey"   idx.PrimaryKey = True   idx.Unique = True   ' Specify the column for the index   idx.Columns.Append "FeatureID"   ' And add the index to the table   tbl.Indexes.Append idx End Sub 

Note a few new things in this code sample:

• Retrieving a table that already exists is just like retrieving an object from any other collection.

• Creating an Index object with its PrimaryKey property set to True builds a primary key for the table.

• You don't have to create the Column objects and then append them to the index's Columns collection. Instead, you can use the Indexes.Append method to do this all in one step.

Make sure you create the columns and append them to the table before you try to use those same columns in indexes. Otherwise, you get a runtime error.

Creating Relationships

Creating a relationship between two tables with ADOX requires creating a primary key on one table and a foreign key on the other. You can represent both of these with Key objects. But as mentioned previously, creating a primary key index automatically creates the primary key. So all that's left to do to build a relationship between these two tables is to create an appropriate foreign key:

 Sub CreateRelation()   ' Create a foreign key between the   ' FeatureRequests and Users tables   Dim cat As ADOX.Catalog   Dim tbl As ADOX.Table   Dim ky As ADOX.Key   Dim col As ADOX.Column   ' Set up a catalog pointing to this   ' database   Set cat = New ADOX.Catalog   Set cat.ActiveConnection = CurrentProject.Connection   ' Retrieve the foreign key table   Set tbl = cat.Tables("FeatureRequests")   ' Create the key and append it to the table   Set ky = New ADOX.Key   ky.Name = "UserKey"   ky.Type = adKeyForeign   ky.RelatedTable = "Users"   Set col = New ADOX.Column   col.Name = "UserID"   col.RelatedColumn = "UserID"   ky.Columns.Append col   tbl.Keys.Append ky End Sub 

The Key object representing a foreign key has properties reflecting both sides of the relationship:

• The Column.Name property is the name of the column in the foreign key table.

• The Column.RelatedColumn property is the name of the column in the primary key table.

• The Key.RelatedTable property is the name of the primary key table.

• The key itself is appended to the Keys collection of the foreign key table.