CreateRelation Method

Applies To Database object.

Description

Creates a new Relation object (Microsoft Jet workspaces only).

Syntax

Set relation = database.CreateRelation(name, table, foreigntable, attributes)

The CreateRelation method syntax uses these parts.

Part	Description

relation	An object variable that represents the Relation object you want to create.
database	An object variable that represents the Database object for which you want to create the new Relation object.
name	Optional. A Variant (String subtype) that uniquely names the new Relation object. See the Name property for details on valid Relation names.
table	Optional. A Variant (String subtype) that names the primary table in the relation. If the table doesn't exist before you append the Relation object, a run-time error occurs.
foreigntable	Optional. A Variant (String subtype) that names the foreign table in the relation. If the table doesn't exist before you append the Relation object, a run-time error occurs.
attributes	Optional. A constant or combination of constants that contains information about the relationship type. See the Attributes property for details.

Remarks The Relation object provides information to the Microsoft Jet database engine about the relationship between fields in two TableDef or QueryDef objects. You can implement referential integrity by using the Attributes property.

If you omit one or more of the optional parts when you use the CreateRelation method, you can use an appropriate assignment statement to set or reset the corresponding property before you append the new object to a collection. After you append the object, you can't alter any of its property settings. See the individual property topics for more details.

Before you can use the Append method on a Relation object, you must append the appropriate Field objects to define the primary and foreign key relationship tables.

If name refers to an object that is already a member of the collection or if the Field object names provided in the subordinate Fields collection are invalid, a run-time error occurs when you use the Append method.

You can't establish or maintain a relationship between a replicated table and a local table.

To remove a Relation object from the Relations collection, use the Delete method on the collection.

See Also Append method, Attributes property, Delete method, Name property, TableDef object.

Example

This example uses the CreateRelation method to create a Relation between the Employees TableDef and a new TableDef called Departments. This example also demonstrates how creating a new Relation will also create any necessary Indexes in the foreign table (the DepartmentsEmployees Index in the Employees table).

Sub CreateRelationX()

    Dim dbsNorthwind As Database
    Dim tdfEmployees As TableDef
    Dim tdfNew As TableDef
    Dim idxNew As Index
    Dim relNew As Relation
    Dim idxLoop As Index

    Set dbsNorthwind = OpenDatabase("Northwind.mdb")

    With dbsNorthwind
        ' Add new field to Employees table.
        Set tdfEmployees = .TableDefs!Employees
        tdfEmployees.Fields.Append _
            tdfEmployees.CreateField("DeptID", dbInteger, 2)

        ' Create new Departments table.
        Set tdfNew = .CreateTableDef("Departments")

        With tdfNew
            ' Create and append Field objects to Fields
            ' collection of the new TableDef object.
            .Fields.Append .CreateField("DeptID", dbInteger, 2)
            .Fields.Append .CreateField("DeptName", dbText, 20)

            ' Create Index object for Departments table.
            Set idxNew = .CreateIndex("DeptIDIndex")
            ' Create and append Field object to Fields
            ' collection of the new Index object.
            idxNew.Fields.Append idxNew.CreateField("DeptID")
            ' The index in the primary table must be Unique in
            ' order to be part of a Relation.
            idxNew.Unique = True
            .Indexes.Append idxNew
        End With

        .TableDefs.Append tdfNew

        ' Create EmployeesDepartments Relation object, using
        ' the names of the two tables in the relation.
        Set relNew = .CreateRelation("EmployeesDepartments", _
            tdfNew.Name, tdfEmployees.Name, _
            dbRelationUpdateCascade)

        ' Create Field object for the Fields collection of the
        ' new Relation object. Set the Name and ForeignName
        ' properties based on the fields to be used for the
        ' relation.
        relNew.Fields.Append relNew.CreateField("DeptID")
        relNew.Fields!DeptID.ForeignName = "DeptID"
        .Relations.Append relNew

        ' Print report.
        Debug.Print "Properties of " & relNew.Name & _
            " Relation"
        Debug.Print "    Table = " & relNew.Table
        Debug.Print "    ForeignTable = " & _
            relNew.ForeignTable
        Debug.Print "Fields of " & relNew.Name & " Relation"

        With relNew.Fields!DeptID
            Debug.Print "    " & .Name
            Debug.Print "        Name = " & .Name
            Debug.Print "        ForeignName = " & .ForeignName
        End With

        Debug.Print "Indexes in " & tdfEmployees.Name & _
            " TableDef"
        For Each idxLoop In tdfEmployees.Indexes
            Debug.Print "    " & idxLoop.Name & _
                ", Foreign = " & idxLoop.Foreign
        Next idxLoop

        ' Delete new objects because this is a demonstration.
        .Relations.Delete relNew.Name
        .TableDefs.Delete tdfNew.Name
        tdfEmployees.Fields.Delete "DeptID"
        .Close
    End With

End Sub

Example (Microsoft Access)

The following example creates a new Relation object that defines a relationship between a Categories table and a Products table. The Categories table is the primary table in the relationship, and the Products table is the foreign table. The CategoryID field is the primary key in the Categories table, and a foreign key in the Products table.

To test this example in the Northwind sample database, click Relationships on the Tools menu, and delete the relationship between the Categories table and the Products table. Close the Relationships window, saving the current configuration at the prompt. Run the following procedure. Then view the Relationships window again to see the new relationship.

Sub NewRelation()
    Dim dbs As Database, rel As Relation, fld As Field
    
    ' Return reference to current database.
    Set dbs = CurrentDb
    ' Create new Relation object and specify foreign table.
    Set rel = dbs.CreateRelation("CategoryProducts", "Categories", _
        "Products")
    ' Set attributes to enforce referential integrity.
    rel.Attributes = dbRelationUpdateCascade And dbRelationDeleteCascade
    ' Create field in Relation object.
    Set fld = rel.CreateField("CategoryID")
    ' Specify field name in foreign table.
    fld.ForeignName = "CategoryID"
    ' Append Field object to Fields collection of Relation object.
    rel.Fields.Append fld
    ' Append Relation object to Relations collection.
    dbs.Relations.Append rel
    dbs.Relations.Refresh
    Set dbs = Nothing
End Sub