Python connecting to Access Database using pypyodbc: No default driver specified

Question

I am trying to get data from a very simple Access file into a python code. I followed the directions in this video: https://www.youtube.com/watch?v=zw9P2wSnoIo

My code is as follows:

import pypyodbc

con=pypyodbc.connect('DRIVER={Microsoft Access Driver(*.mdb)};UID=admin;UserCommitSync=Yes;Threads=3;SafeTransactions=0;PageTimeout=5;MaxScanRows=8;MaxBufferSize=2048;{FIL=MS Access};DriverId=25;{DefaultDir=C:/Users/climate1/Documents/Test};DBQ=C:/Users/climate1/Documents/Test/dogs1.mdb;')
cursor=con.cursor()
cursor.execute("SELECT * FROM doggos")

for row in cursor,fetchall():
     print(row)

but it doesn't seem to be able to find the correct driver and I am receiving this error:

File "doggos.py", line 5, in con=pypyodbc.connect('DRIVER={Microsoft Access Driver (*.mdb)};UID=admin;UserCommitSync=Yes;Threads=3;SafeTransactions=0;PageTimeout=5;MaxScanRows=8;MaxBufferSize=2048;{FIL=MS Access};DriverId=25;{DefaultDir=C:/Users/climate1/Documents/Test};DBQ=C:/Users/climate1/Documents/Test/dogs1.mdb;') File "C:\Users\climate1\Documents\SaraCode\lib\site-packages\pypyodbc.py", line 2454, in init self.connect(connectString, autocommit, ansi, timeout, unicode_results, readonly) File "C:\Users\climate1\Documents\SaraCode\lib\site-packages\pypyodbc.py", line 2507, in connect check_success(self, ret) File "C:\Users\climate1\Documents\SaraCode\lib\site-packages\pypyodbc.py", line 1009, in check_success ctrl_err(SQL_HANDLE_DBC, ODBC_obj.dbc_h, ret, ODBC_obj.ansi) File "C:\Users\climate1\Documents\SaraCode\lib\site-packages\pypyodbc.py", line 985, in ctrl_err raise Error(state,err_text) pypyodbc.Error: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified')

I am not new to python but I am fairly new to both windows and Access. I am using python version 3.6.3 and downloaded pypyodbc using pip install.

I have tried going back and fourth between 32 and 64 bit and have also played with some basic syntax stuff (forward slash vs back slash, etc.)

I tried changing the syntax to what you have above and I am still getting the same error. When I check my drivers, one of them is "Microsoft Access Driver (*mdb)" which is the one I'm calling so I'm not sure why it can't find it. — SGray2
I just checked and my computer is a 64 bit operator, and that drive does not exist in my ODBD admin for 64 bit. How can I get this to work on a 64 bit system? — SGray2

Gord Thompson Gord Thompson · Accepted Answer · 2018-04-04T00:47:57

ODBC is fussy about driver names and your driver name is missing a space.

DRIVER={Microsoft Access Driver(*.mdb)}

is incorrect. It needs to be

DRIVER={Microsoft Access Driver (*.mdb)}

assuming that you are using 32-bit Python.

Python connecting to Access Database using pypyodbc: No default driver specified

1 Answers