< Day Day Up > |
There's a pattern that you see over and over again when you're creating objects with ADOX:
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 ColumnsTo build a table, you need three ADOX objects:
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 IndexesJust 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:
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 RelationshipsCreating 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:
|
< Day Day Up > |