Create relationships between MS Access tables using VBA

Create relation between MS Access tables using VBA

Microsoft Access has ability to maintain relationships between different tables. Access database makes it possible to correlate data in many ways and ensure the consistency. In this article we are using database create relation method to create relation between different tables. The Relation object provides information to the Microsoft Access database engine about the relationship between fields in two tables. We can implement referential integrity by using the Attributes property also. The object of create relation method will return the relation value that we are specify.

For implementation of create relation method firstly we have to create two tables with common foreign key. In coding part we are implement create relation method with this foreign key to create relation. Then create a form with button. On button event procedure we have to implement the create object method. After coding we have to test the method. For this we have to click on button. The pop message will show that new relation is created as shown in Fig 1.1.

Now to check whether the relation is created or not, we have to open main table as mention in implementation part. Open table in date sheet view. It will show relations with one table with another. The field of main table contains the field of second table like as one to many relation as shown in fig 1.2.

If we click again on Form button it will return error Object already created as shown in Fig 1.3. It means object created only one time for relation.

VBA code

Option Compare Database
Private Sub Command0_Click()
Dim dbs As DAO.Database
Dim objFld As DAO.Field
Dim objRel As DAO.Relation
Set dbs = CurrentDb()
Set objRel = dbs.CreateRelation("objectrelationtable2")
objRel.Table = "MyMainTable"
objRel.ForeignTable = "MyRelatedTable"
objRel.Attributes = dbRelationUpdateCascade + dbRelationDeleteCascade
Set objFld = objRel.CreateField("club_id")
objFld.ForeignName = "ord_id"
objRel.Fields.Append objFld
dbs.Relations.Append objRel
MsgBox ("Relation Created")
Set objFld = Nothing
Set objRel = Nothing
Set dbs = Nothing
End Sub