VBA to read foreign keys in table (1 Viewer)

[bodders24]

I have written some VBA to create a basic data model in Excel from an Access database. It’s nearly there, but I have found an issue with finding the Foreign keys in tables.

Here is the relationship:

And here are the indexes in table tblSubHeading:

When I use the Relation object the VBA successfully comes up with the ForeignTable and Foreign field names. However, when I loop through the indexes for the ForeignTable and check the Foreign property none are selected. Here's the code:

For Each idx In tdf.Indexes
    If idx.Foreign = True Then
        For Each fld In idx.Fields
            astrFK(intFKCount) = fld.ForeignName
            intFKCount = intFKCount + 1
        Next
    End If
Next

It finds nothing. Can anyone explain why this should be.

Thanks for your help

Bodders

 

[cheekybuddha]

You need to query:
DAO.Database.Relations collection
and iterate the
DAO.Relation objects

 

[bodders24]

You need to query:
DAO.Database.Relations collection
and iterate the
DAO.Relation objects

David

Thanks for your reply. I had already done this:

For Each rln In gdbs.Relations
    blnSelect = True
    If InStr(1, rln.Table, "Hol") > 0 Then
        blnSelect = False
    End If
    If Mid(rln.Table, 2, 3) = "Sys" Then
        blnSelect = False
    End If
    If (blnSelect = True) And (dctTable.Exists(rln.Table) = False) Then
        dctTable.Add rln.Table, intCount
    End If
    If (blnSelect = True) And (dctTable.Exists(rln.ForeignTable) = False) Then
        dctTable.Add rln.ForeignTable, intCount
    End If
Next

My question is why this code works, but interrogating the tableDef Indexes does not.

Nick

 

[arnelgp]

you can also check MSysRelationship table for all PK/FK keys for related tables.

 

[sonic8]

My question is why this code works, but interrogating the tableDef Indexes does not.

Because an index and a foreign key are different things.
The DAO.Field object is used in multiple different contexts. Some of its properties are only valid in a particular context.
In regard to your question, the documentation of the DAO.Field(2).ForeignName property says:

Only a Field2 object that belongs to the Fields collection of a Relation object can support the ForeignName property.

 

[MajP]

Public Function isPK(tblDef As DAO.TableDef, strField As String) As Boolean
  Dim idx As DAO.Index
  Dim fld As DAO.Field
  For Each idx In tblDef.Indexes
    If idx.Primary Then
      For Each fld In idx.Fields
        If strField = fld.Name Then
          isPK = True
          Exit Function
        End If
      Next fld
    End If
  Next idx
End Function

Public Function isIndex(tblDef As DAO.TableDef, strField As String) As Boolean
  Dim idx As DAO.Index
  Dim fld As DAO.Field
  For Each idx In tblDef.Indexes
     For Each fld In idx.Fields
        If strField = fld.Name Then
          isIndex = True
          Exit Function
         End If
      Next fld
  Next idx
End Function

Public Function isFK(tblDef As DAO.TableDef, strField As String) As Boolean
  Dim idx As DAO.Index
  Dim fld As DAO.Field
  For Each idx In tblDef.Indexes
    If idx.Foreign Then
      For Each fld In idx.Fields
        If strField = fld.Name Then
          isFK = True
          Exit Function
        End If
      Next fld
    End If
  Next idx
End Function

 

[Pat Hartman]

It finds nothing. Can anyone explain why this should be.

Because the Indexes created automagically by Access when you define a relationship are hidden so their name starts with a tilda "~".

 

[bodders24]

Thanks for your responses. The use of the MSysRelationships table solved the problem.