Programmatically determine Prime and Foreign Keys in MS-Access 1

[TomCarnahan]

I am trying to use VBA to examine tables in an MS-Access to find Prime and Foreign keys. I know how to do it with SQL-Server, but not in MS-Access.

Would anyone know how to do this in VBA?

Thanks,

--- Tom

 

[TomCarnahan]

I figured out how to get the Prime Key by iterating the table indexes and testing the "Primary" property, then getting the index field/s.

Now I am trying to determine how to get foreign keys. Has anyone seen information on retrieving foreign keys within a table using VBA?

Thanks again!

--- Tom

 

[Golom]

If you're using ADO then

'ADOX
Sub GetForeignKeysADO()

    Dim cat                         As New ADOX.Catalog
    Dim tbl                         As ADOX.Table
    Dim clm                         As ADOX.Column
    Dim fk                          As New ADOX.Key

    ' Open the catalog
    cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                           "Data Source=.\myDataBase.mdb;"

    ' Get the table 
    Set tbl = cat.Tables("Products")

    For Each fk In tbl.Keys
        If Len(fk.RelatedTable) > 0 Then
            Debug.Print fk.Name, tbl.Name, fk.RelatedTable
            For Each clm In fk.Columns
                Debug.Print "    Field: " & clm.Name
            Next
        End If
    Next

    Set cat = Nothing

End Sub

and if you're using DAO

Sub GetForeignKeyDAO()

    Dim db                          As DAO.Database
    Dim rel                         As DAO.Relation
    Dim fld                         As DAO.Field

    ' Open the database
    Set db = DBEngine.OpenDatabase(".\myDataBase.mdb")

    For Each rel In db.Relations
        Debug.Print rel.Name, rel.Table, rel.ForeignTable
        For Each fld In rel.Fields
            Debug.Print "    Field: " & fld.Name
        Next
    Next

End Sub

 

[TomCarnahan]

Works great ... Thanks!

--- Tom