Extracting Foreign Keys from MS Access

Question

I'm trying to get all foreign keys from a table in MS Access. When trying to use cursor.foreignKeys("Table"), I get the error:

InterfaceError: ('IM001', '[IM001] [Microsoft][ODBC Driver Manager] Driver does not support this function (0) (SQLForeignKeys)')

Here's a similar problem but with primary keys: pyodbc - read primary keys from MS Access (MDB) database

Answer 1

Although the Access ODBC driver does not support the SQLForeignKeys function you can still get that information via Jet/ACE DAO:

import win32com.client  # needs `pip install pywin32`


def get_access_foreign_keys(db_path, table_name):
    db_engine = win32com.client.Dispatch("DAO.DBEngine.120")
    db = db_engine.OpenDatabase(db_path)
    fk_list = []
    for rel in db.Relations:
        if rel.ForeignTable == table_name:
            fk_dict = {
                "constrained_columns": [],
                "referred_table": rel.Table,
                "referred_columns": [],
                "name": rel.Name,
            }
            for fld in rel.Fields:
                fk_dict["constrained_columns"].append(fld.ForeignName)
                fk_dict["referred_columns"].append(fld.Name)
            fk_list.append(fk_dict)
    return fk_list


if __name__ == "__main__":
    print(get_access_foreign_keys(r"C:\Users\Public\Database1.accdb", "child"))
    """
    [{'constrained_columns': ['parent_id'], 
      'referred_table': 'parent', 
      'referred_columns': ['id'], 
      'name': 'parentchild'}]
    """