0

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.)

SGray2
  • 19
  • 1
  • 4
  • 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 Apr 04 '18 at 13:36
  • 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 Apr 04 '18 at 14:03

1 Answers1

0

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.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • Thanks for pointing that out! Unfortunately, I added the space and I am still get the same error. – SGray2 Apr 04 '18 at 13:38
  • @SGray2 ... per your comment above, in addition to space did you also put a period for mdb extension which your comment leaves out? You need to type exactly the name of ODBC driver. Additionally, what is your [bit architecture of Python](https://stackoverflow.com/q/1842544/1422451)? – Parfait Apr 04 '18 at 13:46
  • @Parfait it's 64 bit. And I do have the period and space in my driver name in the code. – SGray2 Apr 04 '18 at 14:40
  • @SGray2 - Do you have MS Office (or MS Access) installed? If so, what version? – Gord Thompson Apr 04 '18 at 18:59
  • @Parfait I have version 2016 MSO (16.0.9126.2166) 32 bit. I'm guessing I can't get a 64 bit Microsoft Access Driver without 64 bit access? Also, when I am saving my access files to feed into python, the video recommended saving them as 2003-2002 .mbd files instead of .accdb files, so this is what I've been doing. – SGray2 Apr 04 '18 at 19:11
  • Bit types should ideally align. Can you change your Office install to 64-bit? And you should be able to use either Access types with Python if you use the latest driver. See my own [Python-Access question](https://stackoverflow.com/a/25952758/1422451) asked years ago which @GordThompson kindly answered then too! – Parfait Apr 04 '18 at 19:43