pythonms-accessodbcarcmappypyodbc

Modify existing MS Access Query via ODBC

I have a python script that update and generates exports in ArcMap. The ArcMap document pulls data to be mapped from MS Access. Currently, I have to go into to MS Access and update the criteria for a date from/to column (in design view) and save it before running the script to generate exports. I would like to be able to simply update the date range in the criteria box, save the query, and close the connection.

I have tried to using SQL Update strings, but I get errors for too few arguments and I don't want it to create or change the any tables. Only the query in my database.

db = pypyodbc.connect(
r"Driver={Microsoft Access Driver (*.mdb)};"+
    r"Dbq=P:\path\path\path.mdb;")

sql = """SELECT DISTINCT"""

db.cursor().execute(sql).commit()
Solution

  • To create a saved query in Access via ODBC we can use a CREATE VIEW statement, e.g.,

    crsr.execute("CREATE VIEW CAD_Extract_Mapping AS SELECT ...")

    Unfortunately, Access SQL does not support ALTER VIEW, and DROP VIEW also doesn't work. However, the workaround is to use DROP TABLE view_name, e.g.,

    crsr.execute("DROP TABLE CAD_Extract_Mapping")
crsr.execute("CREATE VIEW CAD_Extract_Mapping AS SELECT ...")

    Note that many DDL commands will fail to execute from within a transaction, so you may need to use

    cnxn.autocommit = True

    before trying the above.