CODEWITHSUNDEEP
pythonms-accesspyodbc
Michael
Michael

Reputation: 197

How to connect MS Access to Python using pyodbc

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

Upvotes: 15

Views: 48022

Answers (3)

Tony B
Tony B

Reputation: 41

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.

Upvotes: 1

Purnomo Setyawendha
Purnomo Setyawendha

Reputation: 98

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

Microsoft Access Database Engine 2016 Redistributable

Upvotes: 1

Gord Thompson
Gord Thompson

Reputation: 123829

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)

Upvotes: 19

Related Questions