Primary Key

K

Kim

Does anyone know of a way to dynamically identify the
primary key in a table? I have written a function that
creates an update query on 2 tables that will always have
different fields, I want to link on the primary key, but I
haven't had luck identifying it code.

Thanks,

Kim
 
D

Douglas J. Steele

Don't forget that a primary key can be made up of more than one field.

The following shows you one way to determine which index is the primary key,
and what fields are in that index:

Sub FindPrimaryKey(TableName As String)
On Error GoTo Err_FindPrimaryKey

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

Set dbCurr = CurrentDb()
Set tdfCurr = dbCurr.TableDefs(TableName)
For Each idxCurr In tdfCurr.Indexes
If idxCurr.Primary = True Then
Debug.Print idxCurr.Name & " is the Primary Key for " &
TableName
Debug.Print "It contains the following fields:"
For Each fldCurr In idxCurr.Fields
Debug.Print " " & fldCurr.Name
Next fldCurr
End If
Next idxCurr

End_FindPrimaryKey:
Exit Sub

Err_FindPrimaryKey:
Select Case Err.Number
Case 3265 ' Item not found in this collection
Debug.Print TableName & " is not a valid table."
Case Else
MsgBox Err.Number & ": " & Err.Description
End Select
Resume End_FindPrimaryKey

End Sub


(Note that if you're using Access 2000 or 2002, you'll need to add a
reference to DAO for this code to work)
 
Top