Connection to Linked Access Database Using pyodbc

Question

I am trying to get data from a Microsoft Access Database. The issue is the number of constraints I'm under:

I have to use 64 bit Python
The access database is made up of linked tables to a different database
The other database requires a 32 bit Oracle driver

Therefore, I have the Access Database stored locally and am trying to connect to that using PyODBC.

I've tried looking around and messing with the connection string but this problem seems pretty unique.

This is currently a modified version of what I have:

import pyodbc

dbPATH = r'C:\path\to\database.accdb'
UID = 'username'
PWD = 'username'

driver = r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};'
credentials = r'DBQ=%s;UID=%s;PWD=%s'%(dbPATH, UID, PWD)
conn_str = driver + credentials

connection = pyodbc.connect(conn_str)
cursor = connection.cursor()
cursor.execute("select * from [table_name];")

for row in cursor.fetchone():
    print(row)

This is the error I typically get:

pyodbc.Error: ('HY000', "[HY000] [Microsoft][ODBC Microsoft Access Driver] ODBC--connection to '{Oracle in OraClient11g_home1_32bit}' failed. (-2001) (SQLExecDirectW)")

Which is surprising since this is the driver and connection that the access database uses to connect to the other data source.

I have tried setting

pyodbc.pooling = False

but that did not change anything.

Does [this tip](https://github.com/mkleehammer/pyodbc/wiki/Tips-and-Tricks-by-Database-Platform#databases-containing-odbc-linked-tables) help? — Gord Thompson, Apr 10 '19 at 18:13
That was one of the things I looked at, but even when I turn off pooled connections, the error message doesn't change — A. Hudson, Apr 10 '19 at 18:28
"this is the driver and connection that the access database uses to connect to the other data source" - Yes, but if your machine has 32-bit Office installed then MSACCESS.EXE can use the 32-bit ODBC driver for Oracle because it is running in the Windows 32-bit subsystem, whereas your 64-bit Python app cannot "see" the 32-bit Oracle driver. — Gord Thompson, Apr 11 '19 at 19:48
Right, so in theory setting ``` pyodbc.pooling = False ``` Should circumnavigate this issue right? — A. Hudson, Apr 11 '19 at 22:10

Gord Thompson · Accepted Answer · 2019-04-12T00:30:50.017

You have two constraints that are mutually exclusive:

I have to use 64 bit Python

... and ...

The other database requires a 32 bit Oracle driver

64-bit processes cannot use 32-bit ODBC drivers; they are simply not compatible. If the rest of your stack (Oracle ODBC driver, Microsoft Office/Access application(s)) is 32-bit then you will need to use a 32-bit version of Python if you want to work with the linked tables as you've described.

Additional Note: Your statement that "The other database requires a 32 bit Oracle driver" is dubious. Client-server databases like Oracle, SQL Server, etc., don't particularly care if they receive requests from a 32-bit client or a 64-bit client. There may be differences in the details on the client side, but that's for the ODBC Driver (and/or the ODBC Driver Manager) on the client to figure out.

Connection to Linked Access Database Using pyodbc

1 Answers1