Python: Reading an Access database that contains ODBC linked tables

Question

I'm trying to read an Access query in pandas, but run into the following error. Could anyone kindly assist?

My code seems to work if I convert the ODBC tables to local tables in Access (but I rather not do this). Also, I already tried turning off pooling and it didn't help.

import pyodbc
import pandas as pd

pyodbc.pooling = False

connStr = (
    "Driver={Microsoft Access Driver (*.mdb, *.accdb)};"
    r"Dbq=C:/users/myname/Documents/database.accdb;"
    )
conn = pyodbc.connect(connStr)

df = pd.read_sql("SELECT * FROM query",conn)

Here's the error:

Execution failed on sql 'SELECT * FROM query': ('HY000', "[HY000] [Microsoft][ODBC Microsoft Access Driver] ODBC--connection to 'SQL Server' failed. (-2001) (SQLExecDirectW)")

Can we assume that you are not prompted for login credentials when you try to open the tables in Access itself? — Gord Thompson
– Gord Thompson, Commented Sep 11, 2019 at 18:14
Also, what is the ODBC driver name and version that the linked tables are using? — Gord Thompson
– Gord Thompson, Commented Sep 11, 2019 at 18:56
For the ODBC tables, I'm using trusted connection, driver = SQL Server, and APP=Microsoft Office 2013 (if this is what you mean by version). — MapleSyrup
– MapleSyrup, Commented Sep 11, 2019 at 19:29
I am unable to reproduce your issue using the old "SQL Server" ODBC driver (SQLSRV32.DLL version 6.01.7601.17514). Can you post a download link for a sample database that includes the query you are trying to run and the tables+links on which it depends? (Local tables can be empty, and table links will have a .Connect property that can be used to reconstruct the SQL Server backend.) — Gord Thompson
– Gord Thompson, Commented Sep 11, 2019 at 20:42

Gord Thompson · Accepted Answer · 2019-09-11 17:32:10Z

1

As documented here:

pyodbc can work with Access databases that contain ODBC Linked Tables if we disable connection pooling before connecting to the Access database

import pyodbc
pyodbc.pooling = False
cnxn = pyodbc.connect(r"DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ= ... ")

answered Sep 11, 2019 at 17:32

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Parfait Over a year ago

But OP has that line in posted (unedited) code pyodbc.pooling = False.

Collectives™ on Stack Overflow

Python: Reading an Access database that contains ODBC linked tables

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related