The basic procedure for creating a relation is as follows:
- Create the Relation object using the Database's CreateRelation method.
- Set the Relation object's attributes as appropriate.
- Create the fields that participate in the relationship, using the Relation object's CreateField method.
- Set the Field object's attributes as appropriate.
- Append each field to the Relation's Fields collection.
- 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 DAO.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 On Error Goto 0 '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. It uses 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 accomplished using the logical Or operator, rather than the unary plus (+) operator.
'Set this relationship to: ' LEFT JOIN ' Referential integrity = Cascade Update and Cascade Delete rel.Attributes = dbRelationLeft Or _ dbRelationUpdateCascade Or _ dbRelationDeleteCascade
Once the Relation object has been created and its attributes specified, you then add all the fields that collectively form the relationship. Finally, 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 'The Field object represents the left side of the relationship, 'where the right side of the relationship is set with the 'ForeignName property. 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, click the Relationships button from the Database Tools tab in the Access Ribbon.
To display the new relationships you've created in code, either add the related tables to the Relationships window, or click Show All from the Relationships group.
Putting It All Together
When writing your own procedures to create DAO objects, you should include sufficient error handling code, and perhaps even wrap the whole lot in a transaction, so if any part of it fails, you don't have orphaned objects that you will have to delete manually.
You can use the following procedure to manage all the code you just created, to test the creation of invoice tables, indexes, and relationships:
Public Sub CreateInvoiceSchema() CreateInvoiceTable CreatePKIndexes "tblInvoice", "InvoiceNo" CreateInvItemTable CreatePKIndexes "tblInvItem", "InvItemID" CreateRelation "Relation1", "tblInvoice", "InvoiceNo", "tblInvItem", "InvoiceNo" End Sub
In this tutorial:
- Using DAO to Access Data
- Data Access Objects
- New Features in DAO
- Referring to DAO Objects
- The DBEngine Object
- Using Transactions
- The Errors Collection
- The Databases Collection
- The CurrentDb() Function
- Opening an External Database
- Closing and Destroying Database Object References
- DAO Property Types
- Setting and Retrieving Built-In Object Properties
- Setting and Retrieving SummaryInfo Properties
- Creating Schema Objects with DAO
- Creating Indexes
- Creating Relations
- Creating Multi-Value Lookup Fields
- Database Encryption with DAO
- Setting Encryption Options
- Managing Access (JET) Security with DAO
- Creating Security Objects
- Creating and Deleting Groups
- Managing Passwords
- Data Access with DAO
- Modifying a QueryDef
- Filtering and Ordering Recordsets
- Navigating Recordsets
- BOF, EOF
- Navigating Recordsets with Multi-Value Lookup Fields
- Bookmarks and Recordset Clones
- Finding Records
- Working with Recordsets
- Using Arrays with Recordsets
- Working with Attachment Fields
- Append Only Fields