Creating relationships between tables in MS Access using VBA code

Initially we have to create a form along with controls over it to create table relationship, to view table relationships, to view table names and their corresponding fields. Initially we have three different tables with no relationship between them.

Creating relationships between tables in MS Access using VBA code Fig-1.1

Fig-1.1

As shown above to create a relationship we require tables so initially we have three different tables and also there is no relationship between those tables.

Creating relationships between tables in MS Access using VBA code Fig-1.2

Fig-1.2

When we click on Create Relationship corresponding response will be creation of relationships among tables enumerated in above figure.

Creating relationships between tables in MS Access using VBA code Fig-1.3

Fig-1.3

Newly created relationships between tables could be confirmed in relationship window under Database Tools option.

Creating relationships between tables in MS Access using VBA code Fig-1.3

Fig-1.3

In order to view relationships among tables on main form we have a form control named View Relationships. If we click on it relationships will be displayed inside a textbox aligned subsequent to it.

Creating relationships between tables in MS Access using VBA code Fig-1.4

Fig-1.4

Using control named View Columns we can enumerate table names along with their fields. Details will be reflected into textbox on its right side.

Creating relationships between tables in MS Access using VBA code Fig-1.5

Fig-1.5

In our next step we create a module that encompasses main functionality of our objective.

VBA Code given for the above procedure here: -

Create a Module with the following code:-

Public 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()
Set newRelation = db.CreateRelation(relationUniqueName, primaryTableName, foreignTableName)
Set relatingField = newRelation.CreateField(primaryFieldName)
relatingField.ForeignName = foreignFieldName
newRelation.Fields.Append relatingField
db.Relations.Append newRelation
Set db = Nothing
CreateRelation = True
Exit Function
ErrHandler:
Debug.Print Err.Description + " (" + relationUniqueName + ")"
CreateRelation = False
End Function

Above code highlights the importance of VBA programming in which we are declaring procedure within module named mdlCreateRelationship and calling that generic code some where else where we wants to utilise in an effective manner.In our next step we are Building events for control named Create Relationship in property sheet its name is cmdCreate. Now when we click this form control subsequently its corresponding VBA code will be invoked.To establish its reference to its corresponding event right click on that control.

Private Sub cmdCreate_Click()
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
Call CreateRelation("Table1", "EMP_ID", "Table2", "EMP_ID")
Call CreateRelation("Table1", "EMP_ID", "Table3", "EMP_ID")
Call CreateRelation("Table2", "ClientID", "Table3", "ClientID")
totalRelations = db.Relations.Count
Set db = Nothing
MsgBox Trim(Str(totalRelations)) + " Relationships created successfully!"
End Sub

Mentioned below code is called when user clicks on View Relationships . Its name in property sheet is cmdAllRelationships.

Private Sub cmdAllRelationships_Click()
Dim db As DAO.Database
Dim totalRelations As Integer
Dim relationText As String
Dim i As Integer
Dim DisplayAllRelations As String
Set db = CurrentDb()
totalRelations = db.Relations.Count
DisplayAllRelations = "Total Relations: " + Str(totalRelations) + vbCrLf
For i = 0 To totalRelations - 1
relationText = db.Relations(i).Table
relationText = relationText + "." + db.Relations(i).Fields(0).Name
relationText = relationText + " <-> " + db.Relations(i).ForeignTable
relationText = relationText + "." + db.Relations(i).Fields(0).ForeignName
relationText = relationText + " (" + db.Relations(i).Name + ")"
DisplayAllRelations = DisplayAllRelations + relationText + vbCrLf
Next i
Set db = Nothing
DisplayAllRelations = DisplayAllRelations + vbCrLf
Me.txtContent = Me.txtContent & DisplayAllRelations
End Sub

Mentioned below code is called when user clicks on View Columns . Its name in property sheet is cmdViewColumns.

Private Sub cmdViewColumns_Click()
Dim db As DAO.Database
Dim totalTables As Integer
Dim i As Integer, j As Integer
Dim fieldsList As String
Set db = CurrentDb()
totalTables = db.TableDefs.Count
Me.txtContent = Me.txtContent & "Total Tables: " + Str(totalTables) + vbCrLf
For i = 0 To totalTables - 1
Me.txtContent = Me.txtContent + db.TableDefs(i).Name + ":" + vbNewLine
fieldsList = ""
For j = 0 To db.TableDefs(i).Fields.Count - 1
fieldsList = fieldsList & db.TableDefs(i).Fields(j).Name & "," + vbLf
Next j
Me.txtContent = Me.txtContent & vbTab + fieldsList + vbCrLf
Next i
Set db = Nothing
End Sub

Mentioned below code is called when user clicks on Clear Text . Its name in property sheet is cmdClear. This code is used to clear Textbox aligned on right side besides other controls.

Private Sub cmdClear_Click()
Me.txtContent = ""
End Sub


DISCLAIMER

It is advised that the information provided in the article should not be used for any kind formal or production programming purposes as content of the article may not be complete or well tested. Access Guru will not be responsible for any kind of damage (monetary, time, personal or any other type) which may take place because of the usage of the content in the article.