InterfaceError with sqlalchemy and Microsoft Access Driver in Python

Question

I want to connect to a MS Acces database that is on my host system. I am using Python 3.7 in jupyter notebook. When I connect to the engine, I get the exception InterfaceError.

My Code:

import urllib
from sqlalchemy import create_engine

connection_string = (
    r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};'
    r'DBQ=./Datenbank1.accdb;'
)
connect_str = f"access+pyodbc:///?odbc_connect={urllib.parse.quote_plus(connection_string)}"
engine = create_engine(connect_str, echo=True)
engine.connect()

InterfaceError: (pyodbc.InterfaceError) ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Der Datenquellenname wurde nicht gefunden, und es wurde kein Standardtreiber angegeben (0) (SQLDriverConnect)') (Background on this error at: http://sqlalche.me/e/13/rvf5)

The data source name was not found and no default driver was specified (0) (SQLDriverConnect)')

Can you help me to find the error? Maybe I am missing the right driver but I don't know how to install it

Check the list returned by pyodbc.drivers() to see what ODBC drivers are available to your Python app. — Gord Thompson
– Gord Thompson, Commented Dec 14, 2020 at 14:17
Ok, I get a list with two drivers ['SQL Server', 'ODBC Driver 17 for SQL Server']. How can I add new Microsoft drivers? — Phil997
– Phil997, Commented Dec 14, 2020 at 14:42
The list indicates that you are running 64-bit Python so you can download the 64-bit Access driver from here. — Gord Thompson
– Gord Thompson, Commented Dec 14, 2020 at 14:56
OK, that works. now I have the following drivers: ['SQL Server', 'ODBC Driver 17 for SQL Server', '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)'] but now I get another Error stackoverflow.com/questions/65301510/… — Phil997
– Phil997, Commented Dec 15, 2020 at 7:16

Gord Thompson · Accepted Answer · 2020-12-15 12:50:34Z

1

On Windows, one way to tell if a Python script is running as 32-bit or 64-bit is to check the output from pyodbc.drivers(). In 32-bit mode, the list will include the older "Jet" driver …

Microsoft Access Driver (*.mdb)

… (note: no mention of *.accdb) which ships with Windows and is only available as 32-bit.

In this case the list returned by pyodbc.drivers() did not include that driver so we can deduce that the script is running as 64-bit. Therefore the solution is to download the 64-bit version of the newer "ACE" ODBC driver from here.

For more details on connecting to Access via pyodbc, see the pyodbc wiki.

answered Dec 15, 2020 at 12:50

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

InterfaceError with sqlalchemy and Microsoft Access Driver in Python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related