Relations Collection (DAO)

A Relations collection contains stored Relation objects of a Database object (Microsoft Access database engine databases only).

Remarks

You can use the Relation object to create new relationships and examine existing relationships in your database. To add a Relation object to the Relations collection, first create it with the CreateRelation method, and then append it to the Relations collection with the Append method. This will save the Relation object when you close the Database object. To remove a Relation object from the collection, use the Delete 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]

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 

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