There are a lot of tips online on how to use pyodbc to run a query in MS Access 2007, but all those queries are coded in the Python script itself. I would like to use pyodbc to call the queries already saved in MS Access. How can I do that?
Add a comment
|
1 Answer
If the saved query in Access is a simple SELECT query with no parameters then the Access ODBC driver exposes it as a View so all you need to do is use the query name just like it was a table:
import pyodbc
connStr = (
r"Driver={Microsoft Access Driver (*.mdb, *.accdb)};"
r"DBQ=C:\Users\Public\Database1.accdb;"
)
cnxn = pyodbc.connect(connStr)
sql = """\
SELECT * FROM mySavedSelectQueryInAccess
"""
crsr = cnxn.execute(sql)
for row in crsr:
print(row)
crsr.close()
cnxn.close()
If the query is some other type of query (e.g., SELECT with parameters, INSERT, UPDATE, ...) then the Access ODBC driver exposes them as Stored Procedures so you need to use the ODBC {CALL ...} syntax, as in
import pyodbc
connStr = (
r"Driver={Microsoft Access Driver (*.mdb, *.accdb)};"
r"DBQ=C:\Users\Public\Database1.accdb;"
)
cnxn = pyodbc.connect(connStr)
sql = """\
{CALL mySavedUpdateQueryInAccess}
"""
crsr = cnxn.execute(sql)
cnxn.commit()
crsr.close()
cnxn.close()
3 Comments
PKey
One line missing from the code is
import pyodbc. Up-voting - cause it worked for me ;-)
Gord Thompson
@Plirkee - I have added the
import statements. Thanks.
svinec
Confirmed, works for me, too. Versions I use: MS Office Pro 2019 x64; MS Access Database Engine 2016; Python 3.8.0 x64; pyodbc 4.0.34; Windows 7 Ultimate x64;