CODEWITHSUNDEEP
pythonms-accesspyodbc
JJMeadows3
JJMeadows3

Reputation: 91

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

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?

Upvotes: 5

Views: 6136

Answers (3)

Masoud.Ebrahimi
Masoud.Ebrahimi

Reputation: 309

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/

Upvotes: 0

Giacomo Catenazzi
Giacomo Catenazzi

Reputation: 9523

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.

Upvotes: 0

Parfait
Parfait

Reputation: 107587

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;")

Upvotes: 5

Related Questions