Identifying a table's primary key fields with code

[Andrea]

I need to be able to identify a table's primary key fields
but I can't figure out how to reference them. I'm
assuming that somehow the table's "indexes" property holds
this information but I can't figure out how to use this
property. What syntax do I use to reference each indexed
field? When I do reference each field is there a name
property so that I can save the name of the field in a
string array? I can't seem to find this level of detail
in any book, website, or help file I have.

Thanks in advance!!
Andrea

 

[Douglas J. Steele]

If you've got a reference set to DAO, you can use the following code:

Sub ListIndexFields(TableName As String)
On Error GoTo Err_ListIndexFields

Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim idxCurr As Index
Dim fldCurr As DAO.Field

Set dbCurr = CurrentDb()
Set idxCurr = dbCurr.TableDefs(TableName).Indexes("PrimaryKey")
Debug.Print "The fields in the PrimaryKey for " & TableName & " are:"
For Each fldCurr In idxCurr.Fields
Debug.Print fldCurr.Name
Next fldCurr

End_ListIndexFields:
Set dbCurr = Nothing
Exit Sub

Err_ListIndexFields:
If Err.Number = 3265 Then ' 3265 is "Item not found in this collection."
Debug.Print "There is no primary key for " & TableName
Else
Debug.Print Err.Number & ": " & Err.Description
End If
Resume End_ListIndexFields

End Sub


To set a reference to DAO, with any code module open, select Tools |
References from the menu bar, scroll through the list of available
references until you find the one for Microsoft DAO 3.6 Object Library, and
select it.

 

[Andrea]

Works great!

Thanks!!!

-----Original Message-----
If you've got a reference set to DAO, you can use the following code:

Sub ListIndexFields(TableName As String)
On Error GoTo Err_ListIndexFields

Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim idxCurr As Index
Dim fldCurr As DAO.Field

Set dbCurr = CurrentDb()
Set idxCurr = dbCurr.TableDefs(TableName).Indexes ("PrimaryKey")
Debug.Print "The fields in the PrimaryKey for " & TableName & " are:"
For Each fldCurr In idxCurr.Fields
Debug.Print fldCurr.Name
Next fldCurr

End_ListIndexFields:
Set dbCurr = Nothing
Exit Sub

Err_ListIndexFields:
If Err.Number = 3265 Then ' 3265 is "Item not found in this collection."
Debug.Print "There is no primary key for " & TableName
Else
Debug.Print Err.Number & ": " & Err.Description
End If
Resume End_ListIndexFields

End Sub


To set a reference to DAO, with any code module open, select Tools |
References from the menu bar, scroll through the list of available
references until you find the one for Microsoft DAO 3.6 Object Library, and
select it.


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)






.

 

[TC]

(snip)

Set idxCurr = dbCurr.TableDefs(TableName).Indexes("PrimaryKey")

As long as the user hasn't renamed it!

I think there is an attribute for the primary key index. Maybe better to
walk the indexes, looking for that.

TC

 

[Dirk Goldgar]

message
(snip)


As long as the user hasn't renamed it!

I think there is an attribute for the primary key index. Maybe better
to walk the indexes, looking for that.

Yep -- the Primary property of the Index object. How about a compromise
solution: look first for the "PrimaryKey" index, and if you don't find
one, walk through the collection looking for an index with its Primary
property set?

 

[TC]

Yep -- the Primary property of the Index object. How about a compromise
solution: look first for the "PrimaryKey" index, and if you don't find
one, walk through the collection looking for an index with its Primary
property set?

TC never compromises!!

What if they renamed a non-pk index to PrimaryKey?



TC

 

[TC]

No debug, no surrender!


I was assuming, of course, that if an index named "PrimaryKey" were
found, one would check that index's Primary property before blithely
trusting it. Paranoid? Me?

Ok! I thought I had you, but you've got me with that double-check!



TC

 

[Dirk Goldgar]

TC never compromises!!

No debug, no surrender!

What if they renamed a non-pk index to PrimaryKey?

I was assuming, of course, that if an index named "PrimaryKey" were
found, one would check that index's Primary property before blithely
trusting it. Paranoid? Me?

 

[Dirk Goldgar]

Ok! I thought I had you, but you've got me with that double-check!

<g> Note that I'm not offering any opinions on whether it would be
faster to do it this way than just to loop through the collection in the
first place. Probably that would depend on the number and sequence of
indexes (and I'd be surprised if the primary key were not usually the
first or second one). Finding out the break-even point would call for
benchmarking I'm too lazy to do at the moment.