Unable to run MS Access macros using Python and pyodbc

Question

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?

Gord Thompson · Accepted Answer · 2022-08-11 14:35:02Z

2

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 …

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()

answered Aug 11, 2022 at 14:35

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

wvano97 Over a year ago

I'm trying to this out but im geting this error:

(-2147352567, 'Exception occurred.', (0, None, 'You canceled the previous operation.', 'vbaac10.chm', 5738, -2146826287), None)

Do you know what would caues this

Gord Thompson Over a year ago

Does the macro call StopAllMacros or try to shut down Access when it finishes?

Collectives™ on Stack Overflow

Unable to run MS Access macros using Python and pyodbc

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related