The basic procedure for creating a Relation is as follows:
1. Create the Relation object using the Database's CreateRelation method.
2. Set the Relation object's attributes as appropriate.
3. Create the fields that participate in the relationship, using the Relation object's CreateField method.
4. Set the Field object's attributes as appropriate.
5. Append each field to the Relation's Fields collection.
6. Append the Relation object to the Database's Relations collection.
The following code creates a relationship whose name is specified by the strRelName argument, specifies its attributes, and adds the tables and fields that make up the relationship.
Note that you can name a relationship any way you like, but when you create a relationship using the Relationships window, Access names the relationship according to the names of the tables involved. For example, if you were to create a relationship between tblInvoice and tblInvItem, Access would name it tblInvoicetblInvItem.
Public Sub CreateRelation(strRelName As String, _ strSrcTable As String, strSrcField As String, _ strDestTable As String, strDestField As String)
Dim dbs As Database Dim fld As DAO.Field Dim rel As DAO.Relation Dim varRel As Variant
Set dbs = CurrentDb On Error Resume Next
'Check if the relationship already exists. 'If so, delete it.
If IsObject(dbs.Relations(strRelName)) Then dbs.Relations.Delete strRelName End If
'Create the relation object
Set rel = dbs.CreateRelation(strRelName, _
strSrcTable, _ strDestTable)
The Relation object now exists in memory, but as with the TableDef and Index objects, it won't be a permanent part of the database until you append it to the Database's Relations collection.
The following code segment defines the relationship's attributes. Notice that I have used three Relation attribute enum values: dbRelationLeft, dbRelationUpdateCascade, and dbRelationDeleteCascade. These, of course, define a LEFT JOIN relationship with referential integrity set to Cascade Update and Cascade Delete.
When you specify the Attribute property, use the sum of the enum values you want to include. This is generally accomplished using the logical Or operator, rather than the unary plus (+).
'Set this relationship to:
' LEFT JOIN
' Referential integrity = Cascade Update
and Cascade Delete
rel.Attributes = dbRelationLeft Or _
Once the Relation object has been created and its attributes specified, you then add all the fields that collectively form the relationship. Lastly, you add the new relationship to the Database's Relations collection to make it permanent, and refresh it.
'Append the field(s) involved in the relationship Set fld = rel.CreateField(strSrcField) fld.ForeignName = strDestField
'Append the field to the relation's Fields collection rel.Fields.Append fld
'Append the relation to the Database's Relations collection dbs.Relations.Append rel 'Refresh the Relations collection dbs.Relations.Refresh
Set rel = Nothing Set fld = Nothing Set dbs = Nothing End Sub
When you create your own relationships in code, they will not automatically appear in the Relationships window. To display the Relationships window, display the Database window, and then select Relationships from the Tools menu.
To display the new relationships you've created in code, you either add the related tables to the Relationships window, or click Show All from the Relationships menu.
Was this article helpful?