Access VBA - table relationships

Hi

What VBA code would I use to find the table relationships in Access?
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Jim Dettman (Microsoft MVP/ EE MVE)Connect With a Mentor President Online Computer Svcs, WNY IncCommented:
You'd want to access the relations object, which belongs to the database object.  Sample DAO code from the on-line help below.

Jim.

Sub RelationX()

      Dim dbsNorthwind As Database
      Dim rstProducts As Recordset
      Dim prpLoop As Property
      Dim fldLoop As Field
      Dim errLoop As Error

      Set dbsNorthwind = OpenDatabase("Northwind.mdb")
      Set rstProducts = dbsNorthwind.OpenRecordset("Products")

      ' Print a report showing all the different parts of
      ' the relation and where each part is stored.
      With dbsNorthwind.Relations!CategoriesProducts
            Debug.Print "Properties of " & .Name & " Relation"

Debug.Print "    Table = " & .Table
            Debug.Print "    ForeignTable = " & .ForeignTable
            Debug.Print "Fields of " & .Name & " Relation"
            With .Fields!CategoryID
                  Debug.Print "    " & .Name
                  Debug.Print "        Name = " & .Name
                  Debug.Print "        ForeignName = " & .ForeignName
            End With
      End With

      ' Attempt to add a record that violates the relation.
      With rstProducts
            .AddNew
            !ProductName = "Trygve's Lutefisk"

!CategoryID = 10
            On Error GoTo Err_Relation
            .Update
            On Error GoTo 0
            .Close
      End With

      dbsNorthwind.Close
      
      Exit Sub
      
Err_Relation:

      ' Notify user of any errors that result from
      ' the invalid data.
      If DBEngine.Errors.Count > 0 Then
            For Each errLoop In DBEngine.Errors
                  MsgBox "Error number: " & errLoop.Number & _
                        vbCr & errLoop.Description
            Next errLoop
      End If
      
      Resume Next

End Sub
0
 
PatHartmanConnect With a Mentor Commented:
You could create a query that reads the MSysRelationships table.

SELECT MSysRelationships.*
FROM MSysRelationships;
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thanks very much
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.