لطفا منتظر باشید ...
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.CAUTIONWhen 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.
