Last Updated 25 Feb 2022                                                                                       Difficulty level :   Advanced

Section Links: (this page)
          Using Subdatasheets
          Managing relationships using code
          Downloads


The first part of this article explored the use of the relationships window to set table relationships and to enforce referential integrity (RI).
Next, I will explain two other methods of creating relationships between tables: subdatasheets and VBA code.

2.   Using subdatasheets                                                                                                                         Return To Top

Another way of adding relationships is to use subdatasheets on the Home tab when a table is open.


Select the table to be linked and the Master/Child fields then click OK


The table will now show a + sign indicating it has a linked subdatasheet


Click the + to show the subdatasheet for one or more records.


Click the – to close the subdatasheet again.

If you have several tables that can be joined, this can be repeated to create cascading subdatasheets for all tables you have linked


However, the use of subdatasheets can be very confusing to end users.
Subdatasheets also significantly slow the loading of forms as each subdatasheet has to be loaded when the form opens.

For those reasons, many developers do not use subdatasheets.

Relationships created using subdatasheets do NOT automatically appear in the relationships window.
To display relationships created using subdatasheets, click All Relationships on the Design ribbon.


Similarly, relationships created from the relationships window do NOT automatically create subdatasheets



3.   Managing relationships using VBA code                                                                                         Return To Top

It is also possible to add, edit or delete relationships using VBA.
For example, use this code to create a relationship with referential integrity and cascade update/cascade delete between the 2 tables used above:

Function CreateRelationship()

On Error GoTo Err_Handler

    Dim db As DAO.Database
    Dim rel As DAO.Relation
    Dim fld As DAO.Field

    'Initialize
    Set db = CurrentDb()

    'Create a new relationship.
    Set rel = db.CreateRelation("tblAlbumstblAlbumsTracks")

    'Define its properties.
    With rel
        'Specify the primary table.
        .Table = "tblAlbums"
        'Specify the related table.
        .ForeignTable = "tblAlbumsTracks"
        'Specify attributes for cascading updates and deletes.
       .Attributes = dbRelationUpdateCascade + dbRelationDeleteCascade

        'Add the fields to the relationship.
        'Field name in primary table.
        Set fld = .CreateField("ID")
        'Field name in related table.
        fld.ForeignName = "ID"
        'Append the field.
        .Fields.Append fld

        'Repeat for other fields if a multi-field relation.
   End With

    'Save the newly defined relation to the Relations collection.
    db.Relations.Append rel
    'Debug.Print "Relationship created."

    'Clean up
    Set fld = Nothing
    Set rel = Nothing
    Set db = Nothing

Exit_Handler:
    Exit Function

Err_Handler:
    'error 3012 if relationship already exists
    MsgBox "Error " & Err.Number & " in CreateRelationship procedure : " & _
       Err.description, vbCritical, "Program error"
    Resume Exit_Handler

End Function

This code deletes the same relationship if it exists

Function DeleteRelationship()

On Error GoTo Err_Handler

    DBEngine(0)(0).Relations.Delete "tblAlbumstblAlbumsTracks"

Exit_Handler:
   Exit Function

Err_Handler:
    'err 3265 if relationship doesn't exist
    MsgBox "Error " & Err.Number & " in DeleteRelationship procedure : " & _
       Err.description, vbCritical, "Program error"
   Resume Exit_Handler

End Function

For more details and example code, see this article on Allen Browne's excellent website: http://allenbrowne.com/func-DAO.html#CreateRelationDAO


The final part of this article will explore how the relationship details are stored by Access using a hidden system table MSysRelationships



Downloads                                                                                                                                             Return To Top

Click to download:

      This article as a PDF file:                                   Relationships Advice

      The example database used in this article         MSysRelationships           Approx 1.5 MB (zipped)


Please use the contact form below to provide any feedback on this article including details of any errors or omissions



Colin Riddington           Mendip Data Systems                 Last Updated 25 Feb 2022