Relation Object

Description

A Relation object represents a relationship between fields in tables or queries.

Remarks

You can use the Relation object to create new relationships and examine existing relationships in your database. By setting the Attributes property, you can also use the Microsoft Jet database engine to enforce referential integrity.

Any changes you make to the database that violate the relationships established for the database result in a trappable error. If you request cascade update or cascade delete operations, the Jet database engine also modifies the primary or foreign key tables to enforce the relationships you establish.

An example of a typical relationship can be found between the NameAddress table and the ValidStateCodes table. The State field of the NameAddress table is the primary key, and the State field of the ValidStateCodes table is the foreign key. For a new record to be accepted in the NameAddress table, the Jet database engine searches the ValidStateCodes table for a match on the State field of the NameAddress table. If no match is found, the Jet database engine doesn't accept the new record, and a trappable error occurs.

Using a Relation object and its properties, you can:

Specify an enforced relationship between fields in base tables (but not a relationship that involves a query or an attached table).
Establish unenforced relationships between any type of table or query— native or attached.
Use the Name property to refer to the relationship between the fields in the referenced primary table and the referencing foreign table.
Use the Attributes property to determine whether the relationship between fields in the table is one-to-one or one-to-many and how to enforce referential integrity.
Use the Attributes property to determine whether the Jet database engine can perform cascade update and cascade delete operations on primary and foreign tables.
Use the Attributes property to determine whether the relationship between fields in the table is left join or right join.

The Name property settings of all Field objects in the Fields collection of a Relation object represent the names of the fields in the primary key of the referenced table. The ForeignName property settings of the Field objects represent the names of the fields in the foreign key of the referencing table.
When you enforce referential integrity, a unique index must already exist for the key field of the referenced table. The Jet database engine automatically creates an index with the Foreign property set to act as the foreign key in the referencing table.

To create a new Relation object, use the CreateRelation method.

Properties

Attributes Property, ForeignTable Property, Name Property, Table Property.

Methods

CreateField Method.

See Also

CreateRelation Method; Foreign Property; Appendix, "Data Access Object Hierarchy."

Example

This example creates a one-to-many relationship between single fields in two tables. Because referential integrity is enforced for the relationship, an index is created for the primary table.

Function EnumerateRelation () As Integer
    Dim dbsExample As Database
    Dim tdfReferenced As TableDef, tdfReferencing As TableDef
    Dim fldPrimeKey As Field, idxUnique As Index, relEnforced As _ 
        Relation
    Dim I As Integer
    ' Get database.
    Set dbsExample = _ 
        DBEngine.Workspaces(0).OpenDatabase("Northwind.mdb")
    ' Create referenced table with primary key.
    Set tdfReferenced = dbsExample.CreateTableDef("Referenced")
    Set fldPrimeKey = tdfReferenced.CreateField("PrimaryKey", dbLong)  
    tdfReferenced.Fields.Append fldPrimeKey
    ' Create unique index for enforced referential integrity.
    Set idxUnique = tdfReferenced.CreateIndex("UniqueIndex") 
    idxUnique.Primary = True ' No Null values allowed.

    Set fldPrimeKey = tdfReferenced.CreateField("PrimaryKey") 
    idxUnique.Fields.Append fldPrimeKey
    tdfReferenced.Indexes.Append idxUnique
    dbsExample.TableDefs.Append tdfReferenced

    ' Create referencing table with foreign key.
    Set tdfReferencing = dbsExample.CreateTableDef("Referencing") 
    Set fldPrimeKey = tdfReferencing.CreateField("ForeignKey", dbLong) 
    tdfReferencing.Fields.Append fldPrimeKey
    dbsExample.TableDefs.Append tdfReferencing
    ' Create one-to-many relationship and enforce referential integrity.
    Set relEnforced = dbsExample.CreateRelation("EnforcedOneToMany") 
    relEnforced.Table = "Referenced"
    relEnforced.ForeignTable = "Referencing"
    ' Don't set either dbRelationUnique or dbRelationDontEnforce.
    relEnforced.Attributes = 0
    Set fldPrimeKey = relEnforced.CreateField("PrimaryKey")
    fldPrimeKey.ForeignName = "ForeignKey"
    relEnforced.Fields.Append fldPrimeKey
    dbsExample.Relations.Append relEnforced
    ' Enumerate relation and its fields.
    Debug.Print "Relation: "; relEnforced.Name
    Debug.Print "  Primary Table: "; relEnforced.Table
    Debug.Print "  Foreign Table: "; relEnforced.ForeignTable
    Debug.Print "  Attributes: "; relEnforced.Attributes
    Debug.Print "Fields in Relation: Primary, Foreign";
    For I = 0 To relEnforced.Fields.Count - 1
        Set fldPrimeKey = relEnforced.Fields(I)
        Debug.Print "  "; fldPrimeKey.Name;
        Debug.Print ", "; fldPrimeKey.ForeignName
    Next I
    Debug.Print
    dbsExample.Relations.Delete "EnforcedOneToMany"
    dbsExample.TableDefs.Delete "Referenced"
    dbsExample.TableDefs.Delete "Referencing"
    EnumerateRelation = True
End Function

Example (Microsoft Access)

The following example creates a new Relation object representing the relationship between an Employees table and an Orders table.

To test the following example in Microsoft Access, open the Northwind sample database and choose Relationships from the Tools menu. Delete the relationship between the Employees table and the Orders table, and close the Relationships window. Then, run the following function from a standard module, and view the Relationships window again to see the new relationship.

Sub NewRelation()
    Dim dbs As Database
    Dim fld As Field, rel As Relation
    
    ' Return Database variable that points to current database.
    Set dbs = CurrentDb

    ' Create new relationship and set its properties.
    Set rel = dbs.CreateRelation("EmployeesRelation", "Employees", _ 
        "Orders")
    ' Set Relation object attributes to enforce referential integrity.
    rel.Attributes = dbRelationDeleteCascade + dbRelationUpdateCascade
    ' Create field in Fields collection of Relation.
    Set fld = rel.CreateField("EmployeeID")
    ' Provide name of foreign key field.
    fld.ForeignName = "EmployeeID"
    ' Append field to Relation and Relation to database.
    rel.Fields.Append fld
    dbs.Relations.Append rel
End Sub