Determine Primary Key through VBA

[mark_aok]

Hi all,

I have a situation where I need to determine a specific table's
primary key, and then output it.

I have tried the Database Object, and the Record Object, but I've had
no luck.

Ideally there would be something like,

Record(0).index , or .type, or something that would determine the
primary key.

Anyone have any ideas?

Mark

[Allen Browne]

Loop through the Indexes collection to find the one that has the Primary
property set. Note that the primary key consist of more than one field.

The example below shows how to loop the indexes, and assigns upper case P
for the first field in the primary key, or the lower case p for subsequent
fields. It's part of this routine:
http://allenbrowne.com/AppRelReportCode.html

Note that if you were trying to identify the AutoNumber field, you will need
to check the Attributes of the Field in the TableDef. There's an example in
the DescribeFields( ) function on the same page (above.)

Private Function DescribeIndexFi eld(tdf As DAO.TableDef, strField As String)
As String
'Purpose: Indicate if the field is part of a primary key or unique
index.
'Return: String containing "P" if primary key, "U" if uniuqe index,
"I" if non-unique index.
' Lower case letters if secondary field in index. Can have
multiple indexes.
'Arguments: tdf = the TableDef the field belongs to.
' strField = name of the field to search the Indexes for.
Dim ind As DAO.Index 'Each index of this table.
Dim fld As DAO.Field 'Each field of the index
Dim iCount As Integer
Dim strReturn As String 'Return string

For Each ind In tdf.Indexes
iCount = 0
For Each fld In ind.Fields
If fld.Name = strField Then
If ind.Primary Then
strReturn = strReturn & IIf(iCount = 0, "P", "p")
ElseIf ind.Unique Then
strReturn = strReturn & IIf(iCount = 0, "U", "u")
Else
strReturn = strReturn & IIf(iCount = 0, "I", "i")
End If
End If
iCount = iCount + 1
Next
Next

DescribeIndexFi eld = strReturn
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"mark_aok" <ma******@hotma il.comwrote in message
news:11******** *************@z 24g2000prh.goog legroups.com...

Hi all,

I have a situation where I need to determine a specific table's
primary key, and then output it.

I have tried the Database Object, and the Record Object, but I've had
no luck.

Ideally there would be something like,

Record(0).index , or .type, or something that would determine the
primary key.

Anyone have any ideas?

Mark