64bit v 32bit ODBC

Although Python is available in 64- and 32-bit versions, there are not many times when it makes a massive difference. One time it will is when you use ODBC. As we are talking about drivers here, if your app (or in our case Python build) is 32bit you will need to use a 32bit ODBC driver and if you app is 64bit you will need a 64bit driver.

Under a 64bit version of Windows, you will have two versions of the ODBC Administrator tool available. Naturally the 32bit version is used to configure the 32bit ODBC drivers and the 64bit version (the one available from the Control Panel) is for 64bit drivers. Confusingly both tools show 32bit and 64bit user DSN even through they can only configure one, see Microsoft article KB942976 for details. To get to the 32bit version run

%systemdrive%\Windows\SysWoW64\Odbcad32.exe

Still you probably use the ADODB COM interface and these are not 64-/32-bit dependent right? Unfortunately not. This still relies on the the same mechanism as ODBC, so you will need the correct drivers installed.

This caught me out when I tried to open an Access 2007 database and couldn’t understand the error message. Office 2007 is still a 32bit application and hence only installs 32bit ODBC drivers. It was not until 2010 that you had the option of a 64bit version. So what should have been a simple few lines to connect just produced an error.

# Worked under
import win32com.client
dbcom = win32com.client.Dispatch('ADODB.Connection')
connstr = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=C:\\PathToDB\\dbfile.accdb;Uid=Admin;Pwd=;"
db = dbcom.Open(connstr)

It wasn’t until I dropped back to a 32bit build of Python and the same lines worked was I finally sure the problem was with the ODBC driver. Running this with a 64bit build produced the following error

Exception occurred. Provider cannot be found. It may not be properly installed

The correct fix is to install the correct drivers, although this might not be as easy as it should be. When I tried installing the 64bit Microsoft Access Database Engine 2010 Redistributable it refused to install because I the 32bit Office installed.

Another work around would be to use the COM interface to the Access application itself, although this will only work on computers where MS Access is installed.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s