Creates a new
Relation object (Microsoft Access workspaces only). .
Syntax
expression .CreateRelation(Name, Table, ForeignTable, Attributes)
expression A variable that represents a Database object.
Parameters
Name
| Required/optional
| Data type
| Description
|
|---|
Name
| Optional
| Variant
| A Variant ( String subtype) that uniquely names the new Relation object. See the Name property for details on valid Relation names. |
Table
| Optional
| Variant
| 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
| Variant
| 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
| Variant
| A constant or combination of constants that contains information about the relationship type. See the Attributes property for details. |
Return value
Relation
Remarks
The
Relation object provides information to the Microsoft Access 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.
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