Is it possible for 64-bit pyodbc to talk to 32-bit MS access database?

Question

I am using 64-bit python anaconda v4.4 which runs python v3. I have MS Access 2016 32-bit version. I would like to use pyodbc to get python to talk to Access. Is it possible to use 64-bit pyodbc to talk to a MS Access 2016 32-bit database?

I already have a number of python applications running with the 64-bit python anaconda. It will be a chore to downgrade to 32-bit python.

Mariomariology

Answer 1

I'm not a python expert, but just to clarify some possible misconceptions... The Access database file is not 32-bit or 64-bit. Both 32-bit and 64-bit version of Access use the same database file format.

You do not need the MS Office Access application to connect to or use an Access database file. You can download the Access Database Engine which includes ODBC drivers. The most recent 2010 version has both 32-bit and 64-bit versions. You just need to specify the proper driver in your connection string to use the 64-bit driver. Again, this does not speak directly to connections in Python, but perhaps you can get it to work directly using 64-bit drivers.

Volk

wonderful! This is good news to me. What I guess happens is that when I install 32-bit Access, 32-bit ODBC drivers are installed. So, perhaps what I need to do is to install the 64-bit ODBC drivers to talk to 64-bit python pyodbc. – Mariomariology

Along with the installed drivers, two separate configuration utilities are installed: ODBC Data Source (32-bit) and ODBC Data Sources (64-bit). These at least allow you to see what drivers are installed, but also facilitate some configuration. – Volk

@Mariomariology I know it is a bit too late...but if you are still working on that check my answer. – Ferdinana

Answer 2

Yes you can:

Just install

AccessDatabaseEngine_X64.exe /passive

(which contains both the x86 and x64 version of the drivers) and you will be okay. Do not forget the /passive option because if you do it won't install unless you have MS Office 2010 installed as well. You can download the file from the Microsoft Access Database Engine 2010 Redistributable site

After you install AccessDatabaseEngine_X64.exe you should run the following code on your python shell to test everything's okay:

import pyodbc
[x for x in pyodbc.drivers() if x.startswith('Microsoft')]

and you should get a printout like

['Microsoft Access Driver (*.mdb, *.accdb)',
 'Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)',
 'Microsoft Access dBASE Driver (*.dbf, *.ndx, *.mdx)', 
 'Microsoft Access Text Driver (*.txt, *.csv)']

Take care.

Ferdinana

Note that if you install the 64bit driver allongside a 32bit office you will also need to delete or rename the mso.dll registry value in the following registry key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\Common\FilesPaths If you do not do this Office will try to run the repair installer every time you try to open it. – Barogram

Never happened to me – Ferdinana

Thanks.... Apparently it doesn't happen on every pc.... I've encountered it though. For more details it is also mentioned on the following website as a possible problem that can occur: techblog.aimms.com/2014/10/27/… hope it will help others too – Barogram

In my case, it happened. I use Windows 10 with Office 2010 32bit. I needed to use Python 3.8 64bit to connect access.mdb file using pyodbc package. The MS Access Database Engine 2010 didn't work for me, but 2016 version did. I installed it using this approach – Muss

You can also use the newer Microsoft Access Database Engine 2016 Redistributable x64. You just need to replace the command flag /passive with /silent. – Sigfrid

Thanks everybody putting your priceless comments. I'd recently installed the x64 with /passive but now I'm getting this error :( pyodbc.Error: ('HY000', "[HY000] [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x3c1c Thread 0x3fa4 DBC 0x5b207a58 – Lucindalucine

Answer 3

Unfortunately, you need 32-bit Python to talk to 32-bit MS Access. However, you should be able to install a 32-bit version of Python alongside 64-bit Python. Assuming you are using Windows, during a custom install you can pick the destination path. Then use a virtualenv. For example, if you install to C:\Python36-32:

virtualenv --python=C:\Python36-32\bin\python.exe

Good luck!

Col

I don't think so. Check my answer below. – Ferdinana

Answer 4

I can confirm that using pyodbc with Python3 64bit, you can indeed access a 32bit Microsoft Access Database (*.accdb) while maintaining a 32bit MS Office install. I am not claiming that it is "correct" or that it won't cause issues, but it does seem to work for basic SELECT queries.

Here is the setup and hackery:

• I have Microsoft 365 Apps for enterprise 32bit installed. This is not really relevant, other than for the fact that it is 32bit Office.

• I don't know if it makes a difference, but I also have Microsoft ODBC Driver 18 for SQL Server x64 installed.

• Installed Microsoft Access Database Engine 2016 Redistributable x64. I wanted to use the newer Microsoft 365 Access Runtime x64, but it looks like they just provide the full 365 apps installer, which wouldn't install because 32bit Office is already installed.

  The important part here is that the installer will error out under normal conditions with a message about how you have 32bit office installed and this is a 64bit connector. Assuing you consent to the risk, you must force the installer from the command line using the /silent switch.

  accessdatabaseengine_X64.exe /silent
  

• Delete or rename the mso.dll registry value in the following registry key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\16.0\Common\FilesPaths

  Otherwise, you will get an error when you start Office Apps. Thanks @schurinkje.

Sigfrid