Jasinski Technical Wiki


Home Page
All Pages

Quick Search
Advanced Search »

Contributor Links

Create a new Page
File Management
Your Profile

Other Wiki Sections



CreateRelation Script - Microsoft Access

Modified on Fri, Sep 14, 2012, 3:07 PM by Administrator Categorized as Microsoft Office


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

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

ScrewTurn Wiki version Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2023, Patrick Jasinski.