How to connect MS Access to Python using pyodbc

python ms-access pyodbc
Michael picture Michael · Feb 25, 2015 · Viewed 21.2k times · Source

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

Answer

Gord Thompson picture Gord Thompson · Feb 25, 2015

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)

Related questions

How to see the real SQL query in Python cursor.execute using pyodbc and MS-Access

I use the following code in Python (with pyodbc for a MS-Access base). cursor.execute("select a from tbl where b=? and c=?", (x, y)) It's Ok but, for maintenance purposes, I need to know the complete and exact SQL …

Read More
Connecting to ODBC using pyODBC

I've read all the faq pages from the python odbc library as well as other examples and managed to connect to the DSN, using the following code: cnxn = pyodbc.connect("DSN=DSNNAME") cursor = cnxn.cursor() cursor.tables() rows = cursor.fetchall() …

Read More
UnicodeDecodeError: 'utf-16-le'

I'm trying to pull a list of tables from an access database. The script goes through and displays about a third of the tables and gets the following error: Traceback (most recent call last): File "C:/GageInfo/sourcecode for GageTrakNotify/__…

Read More