Retrieving primary key names from Access 2003

[aleksander3]

Does anyone know how Access 2003 stores the names of the primary keys of a database? I'm using an ODBC connection, but the current driver I have for Access (4.00.6304.00) doesn't support the function SQLPrimaryKeys(). I looked in the system tables, but I can't find the names. I've searched this board but can't seem to find someone who has had a similar problem.

I want to know the names because I want to be able to build queries to drop an existing primary key from a given table.

[pclement]

I don't know whether you are using DAO or ADO but one method is to use ADOX (Microsoft ADO Ext 2.x for DDL and Security):

Dim catADOX As New ADOX.Catalog

catADOX.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                     "Data Source=C:\Test Files\db1 XP.mdb;"

For Each keyADOX In catADOX.Tables("Table1").Keys
    If keyADOX.type = adKeyPrimary Then
        Debug.Print keyADOX.Name
        Exit For
    End If
Next keyADOX

catADOX.ActiveConnection.Close

Set catADOX = Nothing
Set keyADOX = Nothing

OpenSchema would be another option.

Personally I can't recommend using ODBC with a Jet database since it's not as stable as Jet OLEDB and is lacking in features.