Python Access Database Query Giving "Data Source Name Not Found"

Question

Whenever I attempt to query a file using Python script, I receive the following error

pyodbc.InterfaceError: ('IM002', u'[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')

Image of Error Message

My code is as followings:

conn = pyodbc.connect ("DRIVER={ODBCDataFile [Microsoft Access Driver 
(*.mdb, *.accdb)]};DBQ=C:\Users\jmtr\Documents\IRST_old.accdb;")

cur = conn.cursor()
cur.execute("SELECT Name, CAI, Email, SSPLocation, BUNUM from Tbl_SSP")

My Access database is "Microsoft Access 2016 32-bit". I am also using "32-bit" python 2.7.13 and 32-bit PYODBC. And, I have 32-bit drivers set-up in the ODBC Data Source Administrator:

Image of ODBC 32-bit

I don't understand why I am still receiving this error message?

Parfait · Accepted Answer · 2018-03-05 19:17:27Z

5

Connection string is incorrect. There is no ODBCDataFile keyword with brackets [...]. Simply remove them and assign DRIVER to installed ODBC driver as your screenshot shows:

conn = pyodbc.connect("DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};" + \
                      "DBQ=C:\\Users\\jmtr\\Documents\\IRST_old.accdb;")

answered Mar 5, 2018 at 19:17

Parfait

108k19 gold badges103 silver badges138 bronze badges

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

1 Comment

JJMeadows3 Over a year ago

Thank you! I made the requested change and the query is working now! I appreciate the help.

Giacomo Catenazzi · Accepted Answer · 2018-03-05 19:18:38Z

0

I think you misspelled ODBCDataFile instead of ODBCFile.

But in python the character \ is the escape character in the strings. You need to append the prefix r"DRIVER..." to force raw strings, so without missinterpreting the escape character.

answered Mar 5, 2018 at 19:18

Giacomo Catenazzi

9,6832 gold badges31 silver badges37 bronze badges

Comments

Masoud.Ebrahimi · Accepted Answer · 2021-03-17 17:46:54Z

First download and install Microsoft Access Database Engine 2010 Redistributable if you haven’t.

Then you should install pyodbc module.

Now you can connect to access database :

ConFileName=(r'c:\mydb\myaccess.mdb')
conn = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=' + ConFileName + ';')
cursor = conn.cursor()

To select from any table in the database please use this simple code :

ConFileName=(r'c:\mydb\myaccess.mdb')
conn = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=' + ConFileName + ';')
cursor = conn.cursor()
cursor.execute('select * from table1')
for row in cursor.fetchall():
    Table1Array.append((row[0],row[1],row[2])
print(str(len(Table1Array))+" records in Table1 loaded successfully.")

You can follow this link to get more information about working with MS Access by Pyton :

https://elvand.com/python-and-ms-access/

Collectives™ on Stack Overflow

Python Access Database Query Giving "Data Source Name Not Found"

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related