MS-Access / Getting Started

---

Creating Relations

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 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