Working with Relations

Relationships between tables are crucial to preserving data integrity in MS-Access or any other relational database system. In these examples, I show both how to create and delete a relationship. The "create" example creates a one-to-many relationship, enforces referential integrity, uses a single field, without cascades.

You can certainly manage relations in Access using the GUI (Tools ~ Relationships) but one of my "best practices" is to code any table changes in a test environment, and then apply them to the production database.

Note: Lines are wrapped to fit the Blogger window - you can view the formatted examples at Working with Relations.

Sub AddRel_Nov_26_2007()
    ' create one-to-many relationship 
    ' from PrimaryTable to ForeignTable
    Const PrimaryTable = 
        "tlkpScholarshipStatus"
    Const PrimaryField = "Status"
    '
    Const ForeignTable = "tblSCH_Applicant"
    Const ForeignField = "Status"
    '
    Dim db As DAO.Database
    Dim rel As DAO.Relation
    Dim fld As DAO.Field
    '
    Set db = CurrentDb
    Set rel = db.CreateRelation
    With rel
    .Name = PrimaryTable & ForeignTable
    .Table = PrimaryTable
    .ForeignTable = ForeignTable
    .Fields.Append rel.CreateField(PrimaryField)
    .Fields(PrimaryField).ForeignName = ForeignField
    End With
    db.Relations.Append rel
    Set fld = Nothing
    Set rel = Nothing
    Set db = Nothing
End Sub

Sub DelRel_Nov26_2007()
    ' delete the relationship
    ' from PrimaryTable to ForeignTable
    Const PrimaryTable = "tlkpScholarshipStatus"
    Const ForeignTable = "tblSCH_Applicant"
    ' relation was named 
    ' using concatenated table names
    Const RelationName = 
        "tlkpScholarshipStatustblSCH_Applicant"
    '
    Dim db As DAO.Database
    '
    Set db = CurrentDb
    db.Relations.Delete RelationName
    Set db = Nothing
End Sub

footnote:

This best practice of scripting table changes came in quite handy when working with an out-of-state customer. Instead of having the back-end database emailed for me to apply the changes, I simply coded the changes into a module in a newfront-end file & the customer ran the table-change code. This minimized downtime on their production system.