CODEWITHSUNDEEP
ms-accessprimary-key
Max Williams
Max Williams

Reputation: 32933

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

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

Upvotes: 1

Views: 3936

Answers (3)

Aranxo
Aranxo

Reputation: 1193

Well, I am late, but there is a solution without the need of using of ADOX, just with the regular ADODB object model.

This is the function to get the name of the primary index. If there isn't one you will get back the empty string:

Public Function GetPrimaryIndex(ByVal Table As String) As String
    Dim RS As ADODB.Recordset

    Set RS = CurrentProject.Connection.OpenSchema(adSchemaIndexes, Array(Empty, Empty, Empty, Empty, Table))

    With RS
        Do Until .EOF
            If !PRIMARY_KEY Then GetPrimaryIndex = !INDEX_NAME: Exit Do
            .MoveNext
        Loop
        .Close
    End With
    Set RS = Nothing
End Function

And here is the function to get the names of the used columns in an index with additional collation (Collation 1 would be ASC, but we normally don't need it), like you would use it in a CREATE INDEX statement:

Public Function GetIndexFields(ByVal Table As String, ByVal IndexName As String) As String
    Dim RS As ADODB.Recordset
    Dim Columns As String

    Set RS = CurrentProject.Connection.OpenSchema(adSchemaIndexes, Array(Empty, Empty, Empty, Empty, Table))

    With RS
        Do Until .EOF
            If !INDEX_NAME = IndexName Then
                Columns = Columns & !COLUMN_NAME
                If !COLLATION = 2 Then Columns = Columns & " DESC"
                Columns = Columns & ", "
            End If
            .MoveNext
        Loop
        .Close
    End With
    Set RS = Nothing

    GetIndexFields = IIf(Len(Columns), Left(Columns, Len(Columns) - 2), "")
End Function

I know, the OP asked for a raw-SQL-only-solution. But - to be honest - I don't know where to find indexes in the hidden system ("MySys") tables nor if that is even possible.

But as reminder: Maybe the OP didn't know that you can use any VBA function in an Access query, not only VBA built in functions, but also your own in local modules programmed functions and even in other Access DBs programmed functions, if you link to that Access DB as a library. So feel free to use this functions in your query.

Upvotes: 2

Seth Spearman
Seth Spearman

Reputation: 6780

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

Upvotes: 1

Mitch Wheat
Mitch Wheat

Reputation: 300559

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

Upvotes: 0

Related Questions