Alison Balteramp;#039;s Mastering Microsoft Office Access 1002003 [Electronic resources]

Creating and Modifying Database Objects Using ADO Code

Although most of the time you will design your database structure before you deploy your application, there might be times when you will need to design or modify database objects at runtime. Fortunately, you can accomplish these tasks using ADO code. The following sections cover adding and removing tables, modifying relationships, and building queries, all using ADO code. These are only a few of the tasks that you can accomplish.

Adding a Table Using Code

It is relatively easy to add a table using ADO code. Listing 14.34 provides an example.

Listing 14.34 Adding a Table

Sub CreateTable() Dim tdf As ADOX.Table Dim idx As ADOX.Index 'Declare and instantiate a Catalog object Dim cat As ADOX.Catalog Set cat = New ADOX.Catalog 'Establish a connection cat.ActiveConnection = CurrentProject.Connection ' Instantiate a Table object Set tdf = New ADOX.Table ' Name the table and add fields to it With tdf .Name = "tblFoods" Set .ParentCatalog = cat .Columns.Append "FoodID", adInteger .Columns("FoodID").Properties("AutoIncrement") = True .Columns.Append "Description", adWChar .Columns.Append "Calories", adInteger End With 'Append the table to the Tables collection cat.Tables.Append tdf 'Instantiate an Index object Set idx = New ADOX.Index 'Set properties of the index With idx .Name = "PrimaryKey" .Columns.Append "FoodID" .PrimaryKey = True .Unique = True End With 'Add the index to the Indexes collection 'of the table tdf.Indexes.Append idx Set idx = Nothing Set cat = Nothing End Sub

Listing 14.34 begins by instantiating an ADOX table object. It sets the Name and ParentCatalog properties of the Table object. Then it uses the Append method of the Columns collection of the table to append each field to the table. After all the columns are appended, it uses the Append method of the Tables collection of the Catalog object to append the Table object to the database.

After the table is appended to the Catalog, you can add indexes to the table. An Index object is instantiated. The Name property of the index is set. Next, the Append method of the Columns object of the Index adds a column to the Index. The PrimaryKey and Unique properties of the index are both set to True. Finally, the Index object is appended to the Indexes collection of the Table object.

CAUTION

When running code that appends an object, an error occurs if the object already exists. You must either include error handling in your routine to handle this eventuality or delete the existing instance of the object before appending the new object.

Removing a Table Using Code

Sometimes it is necessary to remove a table from a database. Fortunately, this is very easily accomplished using ADO code. Listing 14.35 illustrates the process.

Listing 14.35 Removing a Table

Sub DeleteTable() 'Ignore error if it occurs On Error Resume Next 'Declare and instantiate a Catalog object Dim cat As ADOX.Catalog Set cat = New ADOX.Catalog 'Establish the connection for the Catalog object cat.ActiveConnection = CurrentProject.Connection 'Delete a table from the Tables collection cat.Tables.Delete "tblFoods" End Sub

First, the code declares and instantiates a Catalog object. Then it uses the Delete method of the Tables collection of the Catalog object to remove the table from the database.

Establishing Relationships Using Code

If your application adds new tables to a database, it might be necessary to establish relationships between those tables, as demonstrated in Listing 14.36.

Listing 14.36 Establishing a Relationship

Sub CreateRelation() Dim tbl As ADOX.Table Dim fk As ADOX.Key 'Declare and instantiate a Catalog object Dim cat As ADOX.Catalog Set cat = New ADOX.Catalog 'Establish a connection cat.ActiveConnection = CurrentProject.Connection 'Point the Table object at the tblPeople table Set tbl = cat.Tables("tblPeople") 'Instantiate a Key object Set fk = New ADOX.Key 'Set properties of the Key object to relate the 'tblPeople table to the tblFoods table With fk .Name = "PeopleFood" .Type = adKeyForeign .RelatedTable = "tblFoods" .Columns.Append "FoodID" .Columns("FoodID").RelatedColumn = "FoodID" End With 'Append the Key object to the Keys collection of 'the tblPeople table tbl.Keys.Append fk Set cat = Nothing Set tbl = Nothing Set fk = Nothing End Sub

The code begins by pointing a Table object at the foreign key table in the relationship. The code instantiates a Key object. It sets the Name property of the Key object. Next, it establishes the Type property of the Key object. It sets the RelatedTable property equal to the name of the primary key table involved in the relationship. The Append method of the Columns collection of the Key object appends the foreign key field to the Key object. Then the RelatedColumn property of the column is set equal to the name of the primary key field. Finally, the code appends the Key object to the Keys collection of the Table object.

Creating a Query Using Code

At times, you will want to build a query on the fly and permanently store it in the database. Listing 14.37 illustrates this process.

Listing 14.37 Creating a Query

Sub CreateQuery() Dim cmd As ADODB.Command Dim strSQL As String 'Declare and instantiate a Catalog object Dim cat As ADOX.Catalog Set cat = New ADOX.Catalog 'Establish a connection cat.ActiveConnection = CurrentProject.Connection 'Instantiate a Command object and set its 'CommandText property Set cmd = New ADODB.Command cmd.CommandText = "Select * From tblClients Where State='CA'" 'Append the Command object to the Views collection 'of the Catalog object cat.Views.Append "qryCAClients", cmd cat.Views.Refresh Set cat = Nothing Set cmd = Nothing End Sub

The code begins by creating and instantiating a Catalog object and a Command object. It sets the CommandText property of the Command object equal to the SQL statement that underlies the query. The Append method of the Views collection of the Catalog object appends the Command object to a query with the specified name. Finally, the code refreshes the Views collection of the Catalog object.