Python to Access connection Database Error?

Question

I am looking to connect python to an Access database with the following code:

connStr = (
    r"DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};"
    r"DBQ=O:\Architecture\DAART\Data Analytics Team\DAART.accdb;"
   )
cnxn = pyodbc.connect(connStr)
cursor = cnxn.cursor()

df = pd.read_sql("select * from APMS SV-8 Report", cnxn)

For the last line of code, I am receiving the following error message:

DatabaseError: Execution failed on sql 'select * from APMS SV-8 Report': ('42000', '[42000] [Microsoft][ODBC Microsoft Access Driver] Syntax error in FROM clause. (-3506) (SQLExecDirectW)')

I think problem is that your table name has spaces. write your query like this "select * from 'APMS SV-8 Report'" (single quote around your table name) — Sercan
@sTekin Nope, you need to bracket the name. While in ANSI SQL single quotes around the table name are valid, Access is not fully compliant with it out of the box (and AFAIK it even does not support ANSI quotes in ANSI compatibility mode). — Erik A
@ErikA thank you, if someday I use Access, this will save my time :) — Sercan

Erik A Erik A · Accepted Answer · 2020-06-16T13:21:41

Access SQL requires you bracket table names if they contain spaces, keywords or special characters:

select * from [APMS SV-8 Report]

Python to Access connection Database Error?

1 Answers