Forum Discussion
MS Access ODBC driver doesn't support SQLPrimaryKeys function
I'm writing a python script using pyodbc to read an MS Access database. I was hoping to read the primary key of each table, but I'm getting the error IM001 "Driver does not support this function (0) (SQLPrimaryKeys)". Is there another way I can check the primary keys with python?
1 Reply
IM001 means the driver behind your connection does not implement SQLPrimaryKeys; it does not mean the table has no primary key. Retrying the same metadata call will not establish the key definition.
For a local Access database on Windows, an alternative is Access’s documented DAO object model through Automation. From a Python COM client, obtain Access.Application, use its DBEngine.OpenDatabase method to open the database read-only, then enumerate the database’s TableDefs. For each table, enumerate Indexes and select the index whose Primary property is true. Read that index’s Fields collection to obtain every primary-key column, including composite keys.
Do not substitute Unique = true: a table can have unique indexes that are not its primary key. If no index has Primary set, report no declared primary key.
This route requires an installed Access Automation component; close the database and quit the application when finished.