Click here to Skip to main content
14,635,966 members
Articles » Enterprise Systems » Office Development » Microsoft Access
Posted 11 Jun 2009

Tagged as


12 bookmarked

VBA code for creating MS Access Relations

Rate this:
4.00 (2 votes)
Please Sign up or sign in to vote.
4.00 (2 votes)
11 Jun 2009CPOL
VBA code for easily creating MS Access Relations.


The following article describes how to create relationships in MS Access using a VBA macro.


I always had a difficult time creating and managing Relationships in MS Access databases using the Relationships window. Especially when you have a lot of tables and Relations between them. My search in Google and CodeProject didn't help me, and finally, I developed this VB macro to automate Relationships.

Using the code

The code is very straightforward.

Download the zip file ( - 1.19 KB). Unzip and add it to your MS Access database as a module.

The main function is CreateAllRelations(), which calls the function CreateRelation(). As shown in the example below, you need to call the function CreateRelation() with the following parameters -- base table's name, field name in the base table, foreign table name, and field name in the foreign table. Repeat this for each of the Relations you want to create. The main function first deletes all the Relations and then creates them all again.

Typing this for each Relation may be tedious. But once you have this, you can remove and create the Relationships any number of times by just running the macro function named CreateAllRelations().

Public Function CreateAllRelations()

    Dim db As DAO.Database
    Dim totalRelations As Integer
    Set db = CurrentDb()
    totalRelations = db.Relations.Count
    If totalRelations > 0 Then
        For i = totalRelations - 1 To 0 Step -1
            db.Relations.Delete (db.Relations(i).Name)
        Next i
        Debug.Print Trim(Str(totalRelations)) + " Relationships deleted!"
    End If
    Debug.Print "Creating Relations..."
    'Employee Master to Employee CheckIn
    Debug.Print CreateRelation("Employee", "Code", _
                               "CheckIn", "Code")
    ''Orders to Order Details
    Debug.Print CreateRelation("Orders", "No", _
                               "OrderDetails", "No")
    totalRelations = db.Relations.Count
    Set db = Nothing
    Debug.Print Trim(Str(totalRelations)) + " Relationships created!"
    Debug.Print "Completed!"
End Function

Private Function CreateRelation(primaryTableName As String, _
                                primaryFieldName As String, _
                                foreignTableName As String, _
                                foreignFieldName 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 = primaryTableName + "_" + primaryFieldName + _
                         "__" + foreignTableName + "_" + foreignFieldName
    Set db = CurrentDb()
    'Arguments for CreateRelation(): any unique name, 
    'primary table, related table, attributes.
    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




This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Technical Lead
India India
.net developer

Comments and Discussions

QuestionLinked tables Pin
Igor Krupitsky26-Jun-20 6:11
mvaIgor Krupitsky26-Jun-20 6:11 
You need to add this line when creating relationship between linked tables:

newRelation.Properties("Attributes").Value = 2

Otherwise you will get this error:

cannot create a relationship on linked odbc tables
QuestionInvalid Argument Pin
Member 1257020010-Jun-16 1:58
MemberMember 1257020010-Jun-16 1:58 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.