What is an easy way to list foreign keys in MDB?

Question

What is an easy way to list foreign keys in MDB?

Is there a system table that can be requested to list this information?

In particular, I need to know if there are any external constraints in the MDB.

Answer 1

Take a look at the results select * from MSysRelationships.

Answer 2

Or you can check the database object association collection:

  Public Sub PrintRelationships()
    Dim varItem As Variant
    Dim varItem2 As Variant

    For Each varItem In CurrentDb.Relations
      Debug.Print varItem.Name
      Debug.Print " " & varItem.Table
      Debug.Print " " & varItem.ForeignTable
      For Each varItem2 In varItem.Fields
        Debug.Print ": " & varItem2.Name
      Next varItem2
    Next varItem
  End Sub

There are other properties that may be of interest. In addition, the MS Relationship Copying Knowledge Base article may give you some ideas.

Answer 3

Dim rs As ADODB.Recordset
Set rs = oConn.OpenSchema(adSchemaForeignKeys)

oConn - ADODB.connection