CreateRelation Method

Applies To

Database Object.

Description

Creates a new Relation object.

Syntax

Set variable = database.CreateRelation([name[, table[, foreigntable[,
úattributes]]]])

The CreateRelation method syntax uses these parts.

Part	Description

variable	A variable declared as an object data type Relation.
database	The variable name of the Database object you want to use to create the new Relation object.
name	A String variable that uniquely names the new Relation object. See the Name property for details on valid Relation names.
table	A String variable that names the primary table in the relation. If the table doesn't exist before you append the Relation object, a trappable error occurs.
foreigntable	A String variable that names the foreign table in the relation. If the table doesn't exist before you append the Relation object, a trappable error occurs.
attributes	A Long variable that contains information about the relationship type. See the Attributes property for details.

Remarks

The Relation object relays information to the Microsoft Jet database engine about the relationship between fields in two TableDef or QueryDef objects. The Jet database engine can provide referential integrity through the Attributes property.

If you omit one or more of the optional parts when you use CreateRelation, 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, none of its property settings can be altered. 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 trappable error occurs when you use the Append method.

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

See Also

Append Method, Attributes Property, Delete Method, Name Property, TableDef Object.

Example

This example creates a new Relation object. In this case, the Customers table is related to a ValidStates table. Once established, the relationship requires that an entry exist in the ValidStates table corresponding to the Customers.CustState field before it can be added to the Customers table. Any changes to the State field that violate this relationship trigger a trappable error.

By setting the dbRelationUpdateCascade attribute, the Microsoft Jet database engine changes the Primary table Customers.CustState if a corresponding value changes in the ValidStates table. For example, if the ValidState entry TR was changed to TX, all Customers records having TR as a CustState code would be changed to TX.

Dim relTableOne As Relation
Dim dbsNorthwind As Database
Dim fldCustState As Field
Set dbsNorthwind = DBEngine.Workspaces(0).OpenDatabase("Northwind.mdb")
' Create new Relation object.
Set relTableOne = dbsNorthwind.CreateRelation("TableRelationOne")
relTableOne.Table = "Customers"    'Name the Primary Table.
relTableOne.ForeignTable = "ValidStates"    'Name the Foreign Table.
relTableOne.Attributes = dbRelationUpdateCascade
Set fldCustState = relTableOne.CreateField("CustState")
fldCustState.ForeignName = "StateCode"
relTableOne.Fields.Append fldCustState
' Save Relation definition by appending it to Relations collection.
dbsNorthwind.Relations.Append relTableOne

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 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 Database variable pointing to current database.
    Set dbs = CurrentDb

    ' Create new Relation object and specify foreign table.
    Set rel = dbs.CreateRelation("CategoryID", "Categories", "Products")
    ' Set attributes to enforce referential integrity.
    rel.Attributes = dbRelationUpdateCascade + dbRelationDeleteCascade
    ' Create field in relation.
    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
End Sub