1

I can successfully connect to the Microsoft Access database; however, I am having trouble understanding how to create an Access Database object for a query. Once created, I am trying to verify it as an object in the database. I tried a few of the options out there but it does not seem to be creating any query object. Any help is appreciated.

How to create permanent MS Access Query by Python 3.5.1?

Did not work for me and I am not sure why.

sql ="""\
{CREATE AND NAME VIEW AS A SELECT STATEMENT}
"""
csr.execute(sql)
con.close()
del csr

I would expect to see the query object in the database where I can confirm its design.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
Marcus Moonilal
  • 19
  • 1

1 Answers1

0

Python's DB API 2.0 specifies that connections default to autocommit=False, implying that all operations occur in a transaction that must either be committed or rolled back.

For some database platforms, DDL statements like CREATE TABLE, CREATE VIEW, etc., will fail if performed from within a transaction, but Access ODBC does not seem to mind. However, it does require the transaction to be committed before the view (saved query in Access) is actually created.

So the solution in your case is to do 

csr.execute(sql)
con.commit()  # persist the change
con.close()
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418