CODEWITHSUNDEEP
pythonms-accesspyodbc
LeonD
LeonD

Reputation: 13

How to save a query or macro in MS Access using pyodbc?

There’re a lot of tips online on how to use pyodbc to run a query in MS Access, and how to call queries already saved in MS Access, but is there a way to store a query/macro in MS Access, using pyodbc, win32com or other ?

It must then be possible to execute the saved request from MS Access.

(My goal is for the user to be able to execute the requests himself)

Upvotes: 1

Views: 459

Answers (2)

Parfait
Parfait

Reputation: 107737

To add, remember ms-access is a multifaceted thing. Over the years the underlying database (JET/ACE engine) and GUI application has been conflated as "Access database". But these are two different components.

  • Anything involving the GUI application (MSACCESS.exe) such as data macros, general macros, modules, forms, and reports cannot be processed in ODBC connections which runs in the non-visual background of programming languages. Additionally, MS Access, the Office application, must be installed to adjust GUI objects.

    You can still create/delete tables and queries with Access' TableDefs and QueryDefs, both part of its object library using code. Please note: interacting with Access' object library is not limited to VBA but is available to any COM-connected language including Python with win32com module; C# and VB.Net with InteropServices; PHP with COM library; Perl with Win32::OLE module; R with RDCOMClient package, and still others. Such COM programming requires MSACCESS.exe, the software, installed.

  • Anything involving the database (JET/ACE engine) which are Windows .dll files such as tables and queries can be processed in ODBC or OLEDB connections. Additionally, MS Access, the Office application, does NOT need to be installed to run ODBC connections on .mdb or .accdb files. The only requirement is having a Windows environment and ODBC driver.

    Therefore, as @GordonThompson shows, DDL (CREATE TABLE, CREATE VIEW, CREATE PROCEDURE, CREATE INDEX, DROP TABLE) and DML (SELECT, INSERT, UPDATE, DELETE) calls can work in ODBC connections to manipulate database but not GUI objects.

Upvotes: 1

Gord Thompson
Gord Thompson

Reputation: 123829

You can create a saved SELECT query via Access ODBC as follows:

crsr = cnxn.cursor()
query_name = "query1"
# emulate DROP VIEW IF EXISTS
view_names = [x.table_name for x in crsr.tables(tableType="VIEW").fetchall()]
if query_name in view_names:
    crsr.execute(f"DROP TABLE {query_name}")  # yes, DROP TABLE
crsr.execute(f"""
CREATE VIEW {query_name} AS 
SELECT * FROM Donor WHERE DonorID = 1 
"""
)
cnxn.commit()

You can perform a lot more low-level manipulation of the Access database via win32com and DAO.DBEngine.120, but user macros (as opposed to data macros) are a bit of a strange creature in Access and AFAIK you cannot do much with those via DAO.

Upvotes: 1

Related Questions