This article explains very basic concept to determine table relationships on SQL server and present this information on MS Access in a effective manner.Here we are using an automation feature in access VBA to accomplish this intended task.Usually database diagrams are used on SQL Servers for this purpose but When we are using Microsoft Access as a front end for business specific application we would rather like to avoid headache of using Servers.So here this tutorial comes in a handy way.In order to implement this article SQL Server must needs to be in active mode.
As Shown in figure Fig 1.2 we will create a form named frm_Relationships.form will contains few controls like search option, a sub form named SubForm_Relationships.Sub form will be further bounded to a table named tbl_Relationships .A pass through query named PassThroughQryWithResultsReturn will also be used to pass query to SQL Server. Result will be inserted into table that is bounded to sub form. Table design has been shown in figure Fig 1.2.
Now in next step user will associates VBA coding with On Click event of form button. This VBA code will further perform search over SQL server regarding number of different relationship that has been associated with search entity (Table). In order to do this right click on button and click on build event option this process will takes used to VB Editor to perform associated logic. Same has been shown in figure Fig 1.3
In last step when user enter's table name to search it over SQL server and hits button Relationships ,all relatioship displayed in subform as shown in figure Fig 1.4
VBA Code associated with On Click event of button named Relationships:-
Private Sub Btn_Relationships_Click() DoCmd.Hourglass True Dim db As DAO.Database Dim totalRelations As Integer Dim rstTBLs As Recordset Dim sSQL As String Dim rstGetLinkInfo As Recordset Dim strCS As String CurrentDb.Execute "DELETE * FROM tbl_Relationships" ExecPassThroughQueryWithResults Return "SELECT tp.name 'ParentTable', cp.name 'PrimaryField' , tr.name 'RefrencedTable', cr.name 'SecondaryField' FROM sys.foreign_keys fk INNER JOIN sys.tables tp ON fk.parent_object_id = tp.object_id INNER JOIN sys.tables tr ON fk.referenced_object_id = tr.object_id INNER JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id INNER JOIN sys.columns cp ON fkc.parent_column_id = cp.column_id AND fkc.parent_object_id = cp.object_id INNER JOIN sys.columns cr ON fkc.referenced_column_id = cr.column_id AND fkc.referenced_object_id = cr.object_id WHERE tr.name = '" & Me.txtTableName & "' or tp.name = '" & Me.txtTableName & "' ORDER BY tp.name, cp.column_id", rstGetLinkInfo While Not rstGetLinkInfo.EOF If not IsNull(Me.txtTableName) Then CurrentDb.Execute "INSERT INTO tbl_Relationships(TableName, FieldName,ForiegnTable,ForiegnField) VALUES ('" & rstGetLinkInfo!RefrencedTable & "','" & rstGetLinkInfo!PrimaryField & "','" & rstGetLinkInfo!ParentTable & "','" & rstGetLinkInfo!SecondaryField & "')" ElseIf not (rstGetLinkInfo!RefrencedTable = Me.txtTableName) Then CurrentDb.Execute "INSERT INTO tbl_Relationships(TableName, FieldName,ForiegnTable,ForiegnField) VALUES ('" & rstGetLinkInfo!RefrencedTable & "','" & rstGetLinkInfo!PrimaryField & "','" & rstGetLinkInfo!ParentTable & "','" & rstGetLinkInfo!SecondaryField & "')" End If rstGetLinkInfo.MoveNext Wend Me.SubForm_Relationships.Requery DoCmd.Hourglass False End Sub Public Function ExecPassThroughQueryWithResults Return(Strsql As String, ByRef rs As Recordset) As Variant On Error GoTo ErrHndlr CurrentDb.QueryDefs("PassThroughQry WithResultsReturn").SQL = Strsql Set rs = CurrentDb.QueryDefs("PassThroughQry WithResultsReturn").OpenRecordset ExecPassThroughQueryWithResults Return = rs Exit Function ErrHndlr: ExecPassThroughQueryWithResults Return = False End Function
VBA Code associated with On Load event form:-
VBA code shown below will be executed whenever main from is loaded in order to clear the previous result.
Private Sub Form_Load() CurrentDb.Execute "DELETE * FROM tbl_Relationships" Me.SubForm_Relationships.Requery End Sub
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. ERP Makers 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.