Relation objects can be created by using DAO in much the same way you can create an Index object or a TableDef object. You can append a new Relation object to the Relations collection of the database after first appending one or more Field objects to the Fields collection of the new Relation object. In addition, you can set the appropriate Attributes property for the Relation object to specify that the Relation object perform cascading updates or cascading deletes to maintain referential integrity.
The following example deletes and re-creates the relationship between the Employees table and the Orders table in the NorthwindTables database. In the following example, strDbPath is the path to the NorthwindTables database:
Dim dbs As Database
Dim rel As Relation
Dim fld As Field
Set dbs = OpenDatabase(strDbPath)
' Delete existing relationship.
On Error Resume Next
For Each rel In dbs.Relations
If rel.Table = "Employees" And rel.ForeignTable = "Orders" Then
dbs.Relations.Delete rel.Name
End If
Next rel
On Error GoTo 0
Set rel = dbs.CreateRelation("EmployeesOrders")
With rel
' Set Table property to table on "one" side of relation.
.Table = "Employees"
' Set ForeignTable property to table on "many" side of relation.
.ForeignTable = "Orders"
End With
Set fld = rel.CreateField("EmployeeID")
' Specify name of foreign key field in foreign table.
fld.ForeignName = "EmployeeID"
rel.Fields.Append fld
dbs.Relations.Append rel
This code first creates a new Relation object by using the CreateRelation method of the Database object. The Table property specifies the “one” table in the one-to-many relationship; the ForeignTable property specifies the “many” table. Setting these two properties ensures that no EmployeeID value can be entered in the Orders table if that value doesn’t already exist in the Employees table.
You can also set the Attributes property for a Relation object when you create the Relation object. For example, the NorthwindTables database includes a Suppliers table and a Products table. A relationship based on the SupplierID field exists between the two tables. To specify that cascading deletes be performed when a record is deleted from the Suppliers table, set the Attributes property to the built-in constant value dbRelationDeleteCascade. This attribute specifies that if a record is deleted from the Suppliers table, all records in the Products table that have a matching SupplierID field are also deleted. In this way, if a supplier goes out of business, you can automatically delete that supplier’s products from the Products table when you delete the supplier from the Suppliers table. To specify cascading updates, set the Attributes property to dbRelationUpdateCascade.
Multiple attributes can be specified for a relationship by adding the various constant values used to set the Attributes property. For example, the following code specifies an optional left join between two tables, where rel is the name of a relationship:
rel.Attributes = dbRelationLeft + dbRelationUnique + _ dbRelationDontEnforce
Microsoft Access Users Designating a Relation object attribute as “left” or “right” has no effect on referential integrity. It’s used in Microsoft Access to show the type of join that’s created by default when the tables are added to a new query in Design view.
In the SQL DDL example shown earlier in this chapter, creating a new relationship between two tables involves creating an index on the foreign key field. When you create a new Relation object by using DAO, Microsoft Jet automatically creates an index on the foreign key field or fields, even if one already exists. If you create a relationship between two tables with referential integrity enforced, avoid creating an index on the foreign key, as it will be a duplicate index. The foreign key index is necessary for enforcing referential integrity, so don’t delete it. The index that’s created on the foreign key when you create a relationship appears in the Indexes collection of the foreign table.
Microsoft Access Users Because the foreign key index that Microsoft Jet creates is associated with a relationship, it’s not displayed in the Indexes dialog box when you view the table in Design view in Microsoft Access.
It’s possible to create a relationship that’s based on more than one field. To create a Relation object with two fields, create two Field objects in the Fields collection of the new Relation object. Set the ForeignName property of each Field object to the name of the corresponding field in the foreign table, then append both fields to the Fields collection of the Relation object. For example, suppose you have two tables that both contain FieldA and FieldB. You can create a relationship based on both fields if you want to enforce referential integrity for both fields at the same time. You would create both fields in the Fields collection of a Relation object, and set the ForeignName property appropriately.
Note that the primary key in the primary table does not necessarily need to include both FieldA and FieldB. The primary key can be based on both fields, or on just one of the fields.
You can’t modify an existing relationship from DAO. You must delete the relationship and then re-create it. To delete a relationship, use the Delete method on the database’s Relations collection.