CODEWITHSUNDEEP
python-3.xms-accessodbcdriverpyodbc
Saber Alex
Saber Alex

Reputation: 1723

Driver not found even though it's listed in pyodbc.dataSources()

I tried to access MS Access .mdb database using pyodbc. I've set up the ODBC driver, The driver has been registered to my DSN and I can find in the pyodbc.dataSources()

​sources = pyodbc.dataSources()
dsns = list(sources.keys())
dsns.sort()
sl = []
for dsn in dsns:
    sl.append('%s [%s]' % (dsn, sources[dsn]))
print('\n'.join(sl))

Result in: 

Excel Files [Microsoft Excel Driver (*.xls)]
MS Access DB [Microsoft Access Driver (*.mdb, *.accdb)]
MS Access Database [Microsoft Access Driver (*.mdb)]
dBASE Files [Microsoft dBase Driver (*.dbf)]

So, I tried to access the DB by using this command:

con = pyodbc.connect(r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=H:\access-panda\data\myDB.mdb;')

But, I keep getting this error: 

Error: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')

I've tried to resolved the issue.. but no luck. Did I miss something?

Upvotes: 3

Views: 5533

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123549

The list produced by pyodbc.dataSources() is misleading because it shows the results from both the 32-bit and 64-bit "Platform". On a machine with 32-bit Office, running your code under 64-bit Python will produce the list

Excel Files [Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)]
MS Access Database [Microsoft Access Driver (*.mdb, *.accdb)]

but if we open the 64-bit ODBC Administrator we see that they are both for the 32-bit "Platform"

odbc64.png

and the associated drivers will not be available to pyodbc running under 64-bit Python.

A more reliable way to get the list of available drivers is to use

drivers = pyodbc.drivers()
print(drivers)

That should only show you the drivers that are available to your particular "Platform" (i.e., 64-bit or 32-bit Python).

Upvotes: 1

Related Questions