How to connect MS Access to Python using pyodbc

Question

I'm having trouble connecting a database in access with pyodbc. I've seen other example codes that appear near identical to mine that work:

import pyodbc 
cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=localhost;DATABASE=PYODBC.accdb;UID=me;PWD=pass')
cursor = cnxn.cursor()

cursor.execute("SELECT Forename FROM Student")
row = cursor.fetchone()
if row:
    print(row)

My machine is running on windows 7 home premium 64-bit. I have Microsoft office 2010; 32-bit I'm running python 3.3; 32-bit

I have no idea whats wrong with it, I don't even get an error message, the shell opens, but nothing happens. Any help is greatly appreciated

Bibliomancy

Why is the ODBC Driver SQL Server but you specify an Access database? And is the database named PYODBC.accdb? Note: SQL Server is a client server database while Access is a file server database. They use different drivers and connection parameters. – Unprofitable

Please edit your question with more details. Does your machine have Microsoft Office (or perhaps just Microsoft Access) installed? If so, please indicate the version (2013?, 2010?, ...?) and whether it is the 32-bit or the 64-bit version. Also let us know whether you are running 32-bit or 64-bit Python. – Whap

@GordThompson I have added more details to the question, sorry I should have put it originally. – Bibliomancy

@Unprofitable Yeah, the database is called PYODBC, as for the driver do you know what I would put instead? – Bibliomancy

Answer 1

Since you are using the 32-bit versions of both Microsoft Office and Python you should be good to go once you have the right connection string. It should look like this:

connStr = (
    r"DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};"
    r"DBQ=C:\full\path\to\your\PYODBC.accdb;"
    )
cnxn = pyodbc.connect(connStr)

Whap

Answer 2

I am using Win10 and Office 365, my problem resolved with installing Microsoft Access Database Engine 2016 Redistributable

Microsoft Access Database Engine 2016 Redistributable

Newcomen

Answer 3

TLDR1 - PYODBC connect doesn't need both *.mdb and *.accdb. For me having both was correlated with an error

TLDR2 - connecting to 32bit Driver needs 32bit Python

TLDR3 - sometime PYPI doesn't have the compiled code as a WHL for 'pip install somemodule', so either get a C++ compiler or find the version of Python that has a WHL, and use that Version of Python

Including *.accdb returned an error. Once I eliminated *.accdb there was nolonger an error Also had an error when 'DBQ=' was not included.

Either of the following seems to resolve the error;

    import pyodbc
    cnxn = pyodbc.connect(r"DRIVER={Microsoft Access Driver (*.mdb)};" + \
                          r"DBQ=C:\full\path\to\your\PYODBC.accdb;"
                         )

or;

    cnxn = pyodbc.connect("DRIVER={Microsoft Access Driver (*.mdb)};" + \
                          "DBQ=C:\\full\\path\\to\\your\\PYODBC.accdb;"
                         )

Also for 'pip install pyodbc' I had an error error: Microsoft Visual C++ 14.0 or greater is required. Get it with "Microsoft C++ Build Tools"

This was resolved by having the right version of PY request a version of PYODBC from PYPI that was already compiled as a WHL. I installed PY3.8 because PYPI (https://pypi.org/project/pyodbc/#files) did not have the precompiled PYODBC WHL available for PY 3.9. So then pip install pyodbc just collected the WHL from PYPI without needing a compiler. All good.

Another response by DavidSheldon on Microsoft Visual C++ 14.0 is required (Unable to find vcvarsall.bat) indicated that setuptools upgrade would resolve the problem, though I cannot find the foundation for this.

Also since my OS only has 32 Bit MS Office I had to use 32 bit Python.

All on Win10.

Ine

For me it only works if "*.accdb" is included in the connection string. This is probably to do with the way the MS access driver is listed in the registry. Use C:\Windows\System32\odbcad32.exe to check that. – Rhomb