I have just installed 64-bit microsoft office on a new computer and also installed 64bit python 3.6.
I'm trying to connect to an access database with my python script, however I keep getting the following error which suggests I have the 32bit ODBC driver instead of the 64bit driver?
Any ideas on how to connect without having to install 32bit MS Office and 32bit Python?
This is the error message I get:
cnxn = pyodbc.connect(connstr)
pyodbc.Error: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')
Here's my code:
import pyodbc
connStr = (
r"DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};"
r"DBQ=C:\Documents\Test.accdb;"
)
cnxn = pyodbc.connect(connStr)
cursor = cnxn.cursor()
cursor.execute("SELECT * FROM Test")
I personally have not dealt with 64-bit MS Office apps or drivers, however the Access Database Engine 2016 Redistributable contains a 64-bit version including ODBC drivers (I think).
For that matter, even the Access Database Engine 2010 Redistributable also has a 64-bit version.
You can also use ODBC Data Source Administrator to create and manage DSN (Data Source Name). There are separate administrator programs for 32-bit and 64-bit programs. On my Windows 10 machine, the shortcuts are under 'Windows Administrative Tools' and are labeled 'ODBC Data Source (32-bit)' and 'ODBC Data Source (64-bit)'.
By defining a DNS, you can specify which driver to use for opening the database. But you must also be able to refer to the DNS in your connection parameters, regrettably I am not familiar with how to do that in your python program. I at least hope this gives a hint on were to start.
