How to get connected to *.mdb with pypyodbc py2.7 win64

[carlos25]

I'm working on a script that make consults on mdb files with the module pypyodbc, but when it's time to connect with the file it give me an exception.

Python 2.7.9 (default, Dec 10 2014, 12:28:03) [MSC v.1500 64 bit (AMD64)] on win32

This is the code:

def make_consult(fpath, table, column_name, searched_val):
#    Try No. 1
    con = pypyodbc.win_connect_mdb(fpath)
#    Try No. 2
#    connection_string = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=%s" %fpath
#    con = pypyodbc.connect(connection_string)
    cur = con.cursor()
    sql = ("""SELECT * FROM """ + table + """ WHERE """ + column_name + """ LIKE '%""" + searched_val + """'%;""")
    rsql = cur.execute(sql)
    rsql = rsql.fetchall()
    cur.close()
    con.close()
    return rsql
 

In both tries I get the same exception:

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "C:\Python27\64\lib\site-packages\spyderlib\widgets\externalshell\sitecustomize.py", line 682, in runfile
    execfile(filename, namespace)
  File "C:\Python27\64\lib\site-packages\spyderlib\widgets\externalshell\sitecustomize.py", line 71, in execfile
    exec(compile(scripttext, filename, 'exec'), glob, loc)
  File "C:/AAA/Extractor de Datos.py", line 46, in <module>
    a = make_consult(p, table_name(p), 'ClaveInst', 'RbRomanza1')
  File "C:/AAA/Extractor de Datos.py", line 35, in make_consult
    con = pypyodbc.connect(connection_string)
  File "build\bdist.win-amd64\egg\pypyodbc.py", line 2434, in __init__
  File "build\bdist.win-amd64\egg\pypyodbc.py", line 2483, in connect
  File "build\bdist.win-amd64\egg\pypyodbc.py", line 988, in check_success
  File "build\bdist.win-amd64\egg\pypyodbc.py", line 964, in ctrl_err
pypyodbc.Error: (u'HY000', u"[HY000] [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x10dc Thread 0x5d0 DBC 0x4338008                                                              Jet'.")
 

[carlos25]

After a while of looking for how can fix this i think i got it...

On Windows 8.1 64-bits, Python 64-bits, MS Office 32-bits

1. Install AccessDatabaseEngine (because MS Office 32 bits)
https://www.microsoft.com/en-us/down....aspx?id=13255

2. Verify this: Control Panel/Administrative Tools ->
right click on ODBC Data Sources (32-bits) in Target:
%windir%\syswow64\odbcad32.exe
right click on ODBC Data Sources (64-bits) in Target:
%windir%\system32\odbcad32.exe

3. Uninstall Python 64-bits and install Python 32-bits and pypyodbc module

Done...
By the way, in the code above line 8:
(the second '% must be %')

    sql = ("""SELECT * FROM """ + table + """ WHERE """ + column_name + """ LIKE '%""" + searched_val + """'%;""")
 

Replace by this

    sql = ("""SELECT * FROM %s WHERE %s LIKE %s;""" %(table, column_name, "'%" + searched_val + "%'"))
 

Hope this help somebody else...