MS-Access - how to find the name of the composite primary key and associated fields

Question

Hey all

I have a bunch of Access databases, each of which has several tables. Each table has a composite primary key, with varying fields comprising that key.

How can I, for a given table, get the name of the primary key and the names of the fields which are used in it? I.e., what's the SQL to do that? (I need to use raw SQL rather than Visual Basic)

BTW I know that I can open the tables in the Access GUI and see the primary keys there but I need to automate the process so that I can modify the primary keys.

thanks! max

Answer 1

Have a look at the ADOX Object Model. Specifically the Key Object:

With the properties and collections of a Key object, you can:

• Identify the key with the Name property.

• Determine whether the key is primary, foreign, or unique with the Type property.

• Access the database columns of the key with the Columns collection.

• Specify the name of the related table with the RelatedTable property.

• Determine the action performed on deletion or update of a primary key with the DeleteRule and UpdateRule properties.

Also, How to list the primary key columns in an Access table

Update: I think the question originally said programmatically: If you want to use TSQL then you need to query the Hidden System Tables

Answer 2

Here is an Access VBA function that uses ADOX to get the primary Key columns.

Private Function getPrimaryKeyFields(ByRef strFieldNames() As String) As Integer
On Error GoTo HandleErr

Dim intReturn As Integer

'just get the primary key field here.
Dim idx As ADOX.Index
Dim Table As ADOX.Table
Dim col As ADOX.Column
Dim cat As New ADOX.Catalog

Set cat.ActiveConnection = CurrentProject.Connection

Set Table = cat.Tables(mTableName)
Set idx = Table.Indexes("PrimaryKey")

ReDim strFieldNames(idx.Columns.Count)
Dim intCount As Integer
intCount = 0
For Each col In idx.Columns
    strFieldNames(intCount) = col.Name
    intCount = intCount + 1
Next
intReturn = intCount
Set idx = Nothing
Set Table = Nothing
Set col = Nothing
Set cat = Nothing



ExitHere:
getPrimaryKeyFields = intReturn
    Exit Function

HandleErr:
    Select Case Err.Number
        Case Else

            'put some error handling here.  
            Resume ExitHere
    End Select
' End Error handling block.
End Function

Pass in a string array and it is filled in with the field names. Number of fields is returned by the function. Hope this helps.

Seth