Relation Object

Description

A Relation object represents a relationship between fields in tables or queries (Microsoft Jet databases only).

Remarks

You can use the Relation object to create new relationships and examine existing relationships in your database.

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 a linked table).
Establish unenforced relationships between any type of table or query — native or linked.
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 Microsoft Jet database engine can perform cascading update and cascading 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.
Use the Name property of all Field objects in the Fields collection of a Relation object to set or return the names of the fields in the primary key of the referenced table, or the ForeignName property settings of the Field objects to set or return the names of the fields in the foreign key of the referencing table.

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

For example, the Northwind database contains a relationship between an Orders table and a Customers table. The CustomerID field of the Customers table is the primary key, and the CustomerID field of the Orders table is the foreign key. For Microsoft Jet to accept a new record in the Orders table, it searches the Customers table for a match on the CustomerID field of the Orders table. If Microsoft Jet doesn't find a match, it doesn't accept the new record, and a trappable error occurs.

When you enforce referential integrity, a unique index must already exist for the key field of the referenced table. The Microsoft 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. To refer to a Relation object in a collection by its ordinal number or by its Name property setting, use any of the following syntax forms:

Relations(0)

Relations("name")

Relations![name]

Properties

Attributes property, ForeignTable property, Name property, PartialReplica property, Table property.

Methods

CreateField method.

See Also

CreateRelation method, Foreign property.

Example

This example shows how an existing Relation object can control data entry. The procedure attempts to add a record with a deliberately incorrect CategoryID; this triggers the error-handling routine.

Sub RelationX()

    Dim dbsNorthwind As Database
    Dim rstProducts As Recordset
    Dim prpLoop As Property
    Dim fldLoop As Field
    Dim errLoop As Error

    Set dbsNorthwind = OpenDatabase("Northwind.mdb")
    Set rstProducts = dbsNorthwind.OpenRecordset("Products")

    ' Print a report showing all the different parts of
    ' the relation and where each part is stored.
    With dbsNorthwind.Relations!CategoriesProducts
        Debug.Print "Properties of " & .Name & " Relation"
        Debug.Print "    Table = " & .Table
        Debug.Print "    ForeignTable = " & .ForeignTable
        Debug.Print "Fields of " & .Name & " Relation"
        With .Fields!CategoryID
            Debug.Print "    " & .Name
            Debug.Print "        Name = " & .Name
            Debug.Print "        ForeignName = " & .ForeignName
        End With
    End With

    ' Attempt to add a record that violates the relation.
    With rstProducts
        .AddNew
        !ProductName = "Trygve's Lutefisk"
        !CategoryID = 10
        On Error GoTo Err_Relation
        .Update
        On Error GoTo 0
        .Close
    End With

    dbsNorthwind.Close
    
    Exit Sub
    
Err_Relation:

    ' Notify user of any errors that result from
    ' the invalid data.
    If DBEngine.Errors.Count > 0 Then
        For Each errLoop In DBEngine.Errors
            MsgBox "Error number: " & errLoop.Number & _
                vbCr & errLoop.Description
        Next errLoop
    End If
    
    Resume Next

End Sub

Example (Microsoft Access)

The following example removes the existing relationship between an Employees table and an Orders table and then re-creates the relationship by creating a new Relation object.

Sub NewRelation()
    Dim dbs As Database
    Dim fld As Field, rel As Relation
    
    ' Return reference to current database.
    Set dbs = CurrentDb
    ' Find existing EmployeesOrders relation.
    For Each rel In dbs.Relations
        If rel.TABLE = "Employees" And rel.ForeignTable = "Orders" Then
            ' Prompt user before deleting relation.
            If MsgBox(rel.Name & " already exists. " & vbCrLf _
                & "This relation will be deleted and re-created.", vbOK) = vbOK Then
                dbs.Relations.Delete rel.Name
            ' If user chooses Cancel, exit procedure.
            Else
                Exit Sub
            End If
        End If
    Next rel
    ' Create new relationship and set its properties.
    Set rel = dbs.CreateRelation("EmployeesOrders", "Employees", "Orders")
    ' Set Relation object attributes to enforce referential integrity.
    rel.Attributes = dbRelationDeleteCascade + dbRelationUpdateCascade
    ' Create field in Fields collection of Relation object.
    Set fld = rel.CreateField("EmployeeID")
    ' Provide name of foreign key field.
    fld.ForeignName = "EmployeeID"
    ' Append field to Relation object and Relation object to database.
    rel.Fields.Append fld
    dbs.Relations.Append rel
    MsgBox "Relation '" & rel.Name & "' created."
    Set dbs = Nothing
End Sub