Mendip Data Systems

Database applications for businesses and schools

LogoTransparent

 

 

Colin Riddington      Mendip Data Systems        27/03/2019

* Required

To provide feedback on this article, please enter your name, e-mail address and company (optional) below, add your comment and press the submit button. Thanks

Difficulty level :   Moderate

 

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

Click any image to view a larger version

A bit more Relationships advice - Part 2 (of 3)

MSysRel23

2.    Using subdatasheets

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

MSysRel24 MSysRel25 MSysRel26 MSysRel27

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

MSysRel28

3.    Managing relationships using code

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

This code deletes the same relationship if it exists

1 2 Return To Top Page 2 of 3 Return to Access Articles 3

Click to download this article as a PDF file:                              Relationships Advice

 

Click to download the sample database used in this article        MSysRelationships                 Approx 1.5 MB (zipped)

 

 

 

I would be grateful for any feedback on this article including details of any errors or omissions