Unable to run MS Access macros using Python and pyodbc

user_7927642 0
Viewed 46

I am trying to run a msaccess macro in python and having trouble with running the query. I am using pyodbc to connect to the mdb.

conn_str = (
   r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};'
   fr'DBQ={mdb_path};'
)
conn = pyodbc.connect(conn_str)
cursor = conn.cursor()

The above code connects to the .mdb and appears to be successful in connecting, but I am unsure of how to run the macro now.

cursor.execute(f'exec {macro}')

I've tried the above and get the error - ('42S02', "[42S02] [Microsoft][ODBC Microsoft Access Driver] The Microsoft Access database engine cannot find the input table or query 'macro_run_vb'. Make sure it exists and that its name is spelled correctly. (-1305) (SQLExecDirectW)")

macro_run_vb is the name of the macro so I know that its not a problem of it not finding the macro. Has anyone had a similar issue with running macros in python?

ms-access pyodbc python
1 Answers

It is not possible to run an Access macro using an ODBC connection. To do that you would need to use COM Automation. For example, to run the Macro named DropOrmTables …

enter image description here

you would do something like this:

import win32com.client  # needs `pip install pywin32`

db_path = r"C:\Users\Public\test\gord_test.accdb"

obj_access = win32com.client.gencache.EnsureDispatch("Access.Application")
obj_access.OpenCurrentDatabase(db_path)
obj_access.DoCmd.RunMacro("DropOrmTables")
obj_access.Quit()
user_21443900
Related