Reading MS Access with Python 3.7

Question

I have read a hundred pages online as to how to connect to legacy MS Access databases to Python, but everything seems to be dated. I am using Python 3.7 64-bit, with SQLAlchemy and PyODBC, but SQLAlchemy doesn't support MS Access anymore, and I can't get any code to work. Has anyone got a modern version of Python to connect to Access lately? Here's my code that doesn't work.

import pyodbc
connStr = (
    r"DRIVER={Microsoft Access Driver (*.accdb)}; DBQ=E:\db\Records.accdb; Uid=Admin; Pwd=;"
    )
conn = pyodbc.connect(connStr)
cursor = conn.cursor()
cursor.execute('select * from Students')
for row in cursor.fetchall():
    print(row)

The Error:

Traceback (most recent call last): File "G:/Projects/AccessTest/accessdb.py", line 7, in conn = pyodbc.connect(connStr) pyodbc.InterfaceError: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')

Look here. There is no Access ODBC driver named Microsoft Access Driver (*.accdb). Check the list returned by pyodbc.drivers() to see if Microsoft Access Driver (*.mdb, *.accdb) is available to your Python app, and if not then install it. — Gord Thompson
Damn, I just have the MS SQL Drivers in there, which is what I see when I go to my ODBC GUI in Windows for the 64-Bit drivers, I have to go to the ODBCAD32.exe to see the 32-Bit drivers, which is all I have MS Access in. Is there a way to switch PyODBC to use the other drivers? — krypterro

krypterro krypterro · Accepted Answer · 2019-01-24T19:35:57

Added both 32-bit drivers from MS, and made a short-cut to the (as the existing one just goes to the 64 or 32, whatever your OS is). Then I was able to setup an ODBC source, which PyODBC connected to fine. In a related note, I had the same problem with XAMPP on Windows Server 64-bit, and installing the extra MS Driver fixed that as well.

This is where the 32-bit Driver gets installed to with the MS Installer. Not the first place one would look obviously.

C:\Windows\SysWOW64\odbcad32.exe

Reading MS Access with Python 3.7

1 Answers