Getting primary key field name

  • Thread starter Thread starter John
  • Start date Start date
J

John

Hi

Is it possible to programmatically get the name of the primary key field of
a recordset? Primary key is composed of a single field.

Thanks

Regards
 
Assuming the your table is named "tblAccessTable":

Debug.Print
CurrentDb.TableDefs("tblAccessTable").Indexes("PrimaryKey").Fields(0).Name
 
Jellifish said:
Assuming the your table is named "tblAccessTable":

Debug.Print
CurrentDb.TableDefs("tblAccessTable").Indexes("PrimaryKey").Fields(0).Name
Click to expand...


There's no guarantee that the name of the primary index will be
"PrimaryKey", though it will be if you create it in the usual fashion, by
clicking the "key" icon in the table design toolbar. Here's a more generic
function, though, that doesn't rely on the name of the index (and that
returns multiple field names if the primary key is a compound index):

'----- start of code -----
Function PrimaryKeyFields(TableName As String) As String

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim ix As DAO.Index
Dim fld As DAO.Field
Dim strFields As String

Set db = CurrentDb
Set tdf = db.TableDefs(TableName)
For Each ix In tdf.Indexes
If ix.Primary Then
For Each fld In ix.Fields
strFields = strFields & "," & fld.Name
Next fld
Exit For
End If
Next ix

If Len(strFields) > 0 Then strFields = Mid$(strFields, 2)

PrimaryKeyField = strFields

End Function

'----- end of code -----

If there is no primary key, a zero-length string is returned.
 
Back
Top