CreateRelation Script - Microsoft Access

Overview¶

When working with Microsoft Access databases in multiple environments (e.g., development versus production), if you ever need to add foreign keys to a database (called "Relations" in Microsoft Access), it would be better to do it via a script rather than manually. This article outlines a means to write such a script.

Sample Usage¶

CreateRelation "ProposalProduct", "ProposalID", "Proposals", "ProposalID"
    CreateRelation "ProposalProduct", "ProductID", "Product", "ProductID"
    . . .

Reusable Code¶

Public Function CreateRelation(foreignTableName As String, foreignFieldName As String, _
primaryTableName As String, primaryFieldName As String) As Boolean

On Error GoTo ErrHandler

    Dim db As DAO.Database
    Dim newRelation As DAO.Relation
    Dim relatingField As DAO.Field
    Dim relationUniqueName As String
    
    relationUniqueName = "FK_" & primaryTableName & "_" & primaryFieldName & _
                         "__" & foreignTableName & "_" & foreignFieldName
    
    Set db = CurrentDb()
    
    Set newRelation = db.CreateRelation(relationUniqueName, primaryTableName, foreignTableName)
    
    'The field from the primary table.
    Set relatingField = newRelation.CreateField(primaryFieldName)
    
    'Matching field from the related table.
    relatingField.ForeignName = foreignFieldName
    
    'Add the field to the relation's Fields collection.
    newRelation.Fields.Append relatingField
    
    'Add the relation to the database.
    db.Relations.Append newRelation
    
    Set db = Nothing
    
    CreateRelation = True
        
Exit Function

ErrHandler:
    Debug.Print Err.Description + " (" + relationUniqueName + ")"
    CreateRelation = False
End Function