python - connecting to a database with pyodbc - not working

Question

I am trying to connect to an Access 2007 database with the following connection string.

conn = pyodbc.connect("Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=<C:\Users\JSeinfeld\Desktop\Backup databases\Database_6.7.accdb>;")

I get this error:

Traceback (most recent call last):
File "", line 1, in conn = pyodbc.connect("Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=;")

Error: ('HY000', "[HY000] [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x167c Thread 0x1568 DBC 0x1c67a5c

Jet'. (63) (SQLDriverConnectW); [HY000] [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x167c Thread 0x1568 DBC 0x1c67a5c

Jet'. (63); [HY000] [Microsoft][ODBC Microsoft Access Driver] Not a valid file name. (-1044); [HY000] [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x167c Thread 0x1568 DBC 0x1c67a5c

Jet'. (63); [HY000] [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x167c Thread 0x1568 DBC 0x1c67a5c

Jet'. (63); [HY000] [Microsoft][ODBC Microsoft Access Driver] Not a valid file name. (-1044)")

There is another question like this on SO, but there was a 32/64bit compatibility issue which I do not have. There doesn't seem to be a good answer for this question, but hopefully someone can help me connect to my database because I don't know of any other way to query or write data to Access 2007 with python.

Thanks

Answer 1

Had the same issue - it was a simple case of escaping the backslash used in the location of the access db

>>> conn = pyodbc.connect("Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\\access\\site_be.accdb;")

worked as whereas

>>> conn = pyodbc.connect("Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\access\site_be.accdb;")

was giving me the error Jet'. (63); [HY000] [Microsoft][ODBC Microsoft Access Driver] Not a valid file name. (-1044)")

Hope that helped..

Answer 2

Try taking the brackets out of the file name.

conn = pyodbc.connect("Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\Users\JSeinfeld\Desktop\Backup databases\Database_6.7.accdb")

Answer 3

Is there an *.ldb file in your folder?

I had very similar error messages, but mine also included "file already in use." In my case, I finally realized that there was a lock on the database (an .ldb file next to the .mdb). I have a service running that uses the database all the time -- I guess it's time to switch to a multi-user database engine.

As an experiment, I made a copy of the database, and pyodbc connected to the copy just fine. This obviously isn't a long-term solution, but at least now I know what the problem was.

Answer 4

Fabiolus got it right.

The backslash character means that the next character has special meaning in a string. So deciphering the double backslash... The first backslash tells python to prepare for a special string character, the second backslash is the special string character (which in this case is a backslash)

\n = newline
\t = tab
\\ = backslash