| Microsoft Office 2000/Visual Basic Programmer's Guide | |
In a relational database, a relationship is created between a foreign key in one table and typically a primary key (or some other field that contains unique values) in another table. For simplicity's sake, we'll assume that the foreign key and primary key are both single fields, but it is possible that either or both can be made up of more than one field. To get a handle on the tables that contain the foreign and primary keys, we'll use the term foreign table for the table that contains the foreign key, and the term related table for the table that contains the primary key.
A foreign table is most typically on the "many" side of a relationship. For example, in the Northwind sample database there is a one-to-many relationship between the Categories table and the Products table, so the Categories table is the related table and the Products table is the foreign table, and the relationship is established between the primary key field in the Categories table, CategoryID, and the foreign key field in the Products table, which is also named CategoryID.
To create a relationship by using ADOX, you use a Key object to create an object that defines the relationship by specifying the related table and key fields. You then open a Table object on the foreign table and add the new Key object to the Keys collection of the table. The following code sample shows how to create a relationship by using ADOX.
Sub CreateRelationship(strDBPath As String, _
strForeignTbl As String, _
strRelName As String, _
strFTKey As String, _
strRelatedTbl As String, _
strRTKey As String)
Dim catDB As ADOX.Catalog
Dim tbl As ADOX.Table
Dim key As ADOX.Key
Set catDB = New ADOX.Catalog
' Open the catalog.
catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source =" & strDBPath
Set key = New ADOX.Key
' Create the foreign key to define the relationship.
With Key
' Specify name for the relationship in the Keys collection.
.Name = strRelName
' Specify the related table's name.
.RelatedTable = strRelatedTbl
.Type = adKeyForeign
' Add the foreign key field to the Columns collection.
.Columns.Append strFTKey
' Specify the field the foreign key is related to.
.Columns(strFTKey).RelatedColumn = strRTKey
End With
Set tbl = New ADOX.Table
' Open the table and add the foreign key.
Set tbl = catDB.Tables(strForeignTbl)
tbl.Keys.Append key
Set catDB = Nothing
End Sub
For example, to use this procedure to create the relationship described above between the Categories and Products tables in the Northwind database, you can use a line of code like this:
CreateRelationship _
"c:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb", _
"Products","CategoriesProducts","CategoryID","Categories","CategoryID"
The CreateRelationship 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 Key object also supports two additional properties that are used to define whether related records will be automatically updated or deleted if the value in the primary key in the related table is changed. These features of the Jet database engine are called cascading updates and cascading deletions. By default, cascading updates and deletions are not active when you create a new relationship. To turn on cascading updates for the relationship, set the UpdateRule property for the Key object to adRICascade; to turn on cascading deletions for the relationship, set the DeleteRule property for the Key object to adRICascade.
Note When you use OLE DB, there is no way to create a relationship that is not enforced, therefore there is no equivalent in ADOX to the DAO dbRelationDontEnforce setting of the DAO Attributes property of a Relation object. Also, ADOX and the Microsoft Jet 4.0 OLE DB Provider don't provide a way to specify the default join type that will be used in the Access query Design view window, as can be done by using the dbRelationRight and dbRelationLeft settings of the Attributes property.