Connection problems to Access DB

[haiduc]

Hi everyone,

I'm having difficulty connecting to an MS Access database using pyodbc and hope someone on the forum might have a quick fix for this.

import pyodbc

strPathFileDb = 'C:\\full\\path\\to\\database.accdb'

strConnectString = 'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=' + strPathFileDb + ';'

oDbConnection = pyodbc.connect( strConnectString )

Running the above program produces the following error:

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

OS: Windows 7 Home Premium 64-bit

Access version: Access 2013 32-bit

Python version: Python 3.3.2 32-bit

pyodbc version: 3.0.7 32-bit

The exact code works without a hitch on a Win7 64 bit system running 64-bit versions of Access, Python, and pyodbc.

I have already tried using 64-bit versions of Python/pyodbc and changing around connection strings to no avail.

I suspect the problem is related to the underlying ODBC driver... Maybe 64-bit Windows ODBC drivers clash with the 32-bit version of Access installed?

Are there two ODBC drivers (64-bit and 32-bit) available in Win7 64-bit and, if yes, could it be that the wrong driver gets called by pyodbc?

Thanks for any input!

[icsny...@gmail.com]

I dealt with the 32/64 bit issue for a while as well. If you need to use 32bit libraries, try replacing your connection string with something like this instead and let me know if that works. Also, raw strings are cool, and they save you from having to type escape characters.

pyodbc.connect(r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\Users\yourname\Documents\yourdb.accdb;Trusted_Connection=yes')

[haiduc]

Thanks for the suggestion. Unfortunately the behavior is still the same with 'Trusted_Connection=yes'.

I really suspect the bitness of the driver is the culprit.

Can I set the driver using the 32-bit ODBC admin in C:\Windows\SysWOW64 somehow?

I saw the option of hardwiring DSNs to local file paths there, but I don't like the idea of having to manually fiddle with the admin tool each and every time I want to open a database file...

Any further input is much appreciated.

[icsny...@gmail.com]

Yeah, I had to do that at work the other week for an old system on Server 2003. Take, the syswow64 version of odbcad32.exe and move it to administrative tools, and name the other version of the admin tool something like backup-odbcad. Then try to set up the connection again.