1

I'm trying to connect to an Access database using ODBC and if I ask for a list of DNS providers the access driver is listed, but it won't accept it being used in the query.

I've checked and I have 32bit drivers, Access and Python (and querying the available drivers shows that we can see the drivers), so I've probably done something really stupid, so any help would be appreciated.

import pypyodbc
sources = pypyodbc.dataSources()
dsns = sources.keys()
sl = []
for dsn in dsns:
  sl.append('%s [%s]' % (dsn, sources[dsn]))
print('\n'.join(sl))
  b'MS Access Database' [b'Microsoft Access Driver (*.mdb, *.accdb)']
  b'Excel Files' [b'Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)']
  b'dBASE Files' [b'Microsoft Access dBASE Driver (*.dbf, *.ndx, *.mdx)']
constr = r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=D:\Database1.accdb;'
conn = pypyodbc.connect(constr, autocommit=True)
Traceback (most recent call last):
  File "<pyshell#9>", line 1, in <module>
    conn = pypyodbc.connect(constr, autocommit=True)
  File "C:\Users\Paul\AppData\Local\Programs\Python\Python35-32\lib\site-packages\pypyodbc.py", line 2434, in __init__
    self.connect(connectString, autocommit, ansi, timeout, unicode_results, readonly)
  File "C:\Users\Paul\AppData\Local\Programs\Python\Python35-32\lib\site-packages\pypyodbc.py", line 2483, in connect
    check_success(self, ret)
  File "C:\Users\Paul\AppData\Local\Programs\Python\Python35-32\lib\site-packages\pypyodbc.py", line 988, in check_success
    ctrl_err(SQL_HANDLE_DBC, ODBC_obj.dbc_h, ret, ODBC_obj.ansi)
  File "C:\Users\Paul\AppData\Local\Programs\Python\Python35-32\lib\site-packages\pypyodbc.py", line 964, 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')
Paul Jefferson
  • 19
  • 4
  • This is an often [repeatedly asked question](https://www.google.com/#q=stackoverflow+pyodbc+ms+access+connection&start=10). Even I asked a question a while back. Your Python bit version (32/64-bit) must align to installed MS Office bit version (32/64-bit). You get the error due to incompatibility. – Parfait Sep 25 '16 at 15:05
  • I thought that at first, but I've checked and everything is 32 bit - http://imgur.com/a/BNODO – Paul Jefferson Sep 25 '16 at 15:22
  • Those N/As look suspicious as it should say 32 or 64-bit or both. Might be a registry issue. Can you use those drivers in other programs like Excel VBA using ADO? While I cannot recommend software here on SO, check out the [AccessDatabaseEngine for 2013](https://www.microsoft.com/en-us/download/details.aspx?id=39358) which comes with component parts. But check with your IT or go-to guy. Or re-install MS Office. Again just a suggestion. – Parfait Sep 25 '16 at 15:37
  • Thanks! Looks like I lost the drivers at some point, but that sorts it! – Paul Jefferson Sep 25 '16 at 17:47

0 Answers0