0

Im trying to connect to my microsoft access datbase stored locally on my PC using pythons pyodbc but I keep getting a long error. Heres my code.

conn = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\Users\James\Documents\Database1.accdb;')
cursor = conn.cursor()
cursor.execute('select * my_table')

Heres the error , or part of:

Error: ('HY000', "[HY000] [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x2cd0 Thread 0x268c DBC 0x7f37e34                                                              Jet'. (63) (SQLDriverConnect); [HY000] [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x2cd0 Thread 0x268c DBC 0x7f37e34                                                              Jet'. (63); [HY000] [Microsoft][ODBC Microsoft Access Driver] The database you are trying to open requires a newer version of Microsoft Access. (-1073); [HY000] [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x2cd0 Thread 0x268c DBC 0x7f37e34                                                              Jet'. (63); [HY000] [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x2cd0 Thread 0x268c DBC 0x7f37e34                                                              Jet'. (63); [HY000] [Microsoft][ODBC Microsoft Access Driver] The database you are trying to open requires a newer version of Microsoft Access. (-1073)")

using pyodbc.drivers() , returns

enter image description here

so my understanding is that the driver Driver={Microsoft Access Driver (*.mdb, *.accdb exists on my machine

Ive downloaded and installed Microsofts Access database engine but that didnt help.. I also changed my connection string to

DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};UID=admin;UserCommitSync=Yes;Threads=3;SafeTransactions=0;PageTimeout=5;MaxScanRows=8;MaxBufferSize=2048;{FIL=MS Access};DriverId=25;DefaultDir=C:\Users\James\Documents;DBQ=C:\Users\James\Documents\Database1.accdb

Error msg stayed the same

Any suggestions??

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
James Cook
  • 334
  • 4
  • 16
  • If I create a blank database in a location where I'm allowed to access files and run your code (after fixing the SQL to read `select * from my_table`) it runs without issue. So, my bet is there's a problem with the installation of the ODBC driver, or with the rights of the user account running the script (no rights to the file, or some other access issue - no pun intended). Do you have Access installed? Can you access the database from there? – Grismar Nov 09 '20 at 01:29
  • @Grismar thanks for picking up that SQL typo!! I can open the db and make changes to the tables without issue. I can save edit filename etc.. When I ran the install for MS access DB engine would that have updated / corrected a bad ODBC driver? Or will I need to install separately.. – James Cook Nov 09 '20 at 01:36
  • 1
    Not sure what your exact situation is, but it's likely not a programming error but an installation / environment error. I have a standard install of 64-bit office and connect to it from 64-bit Python. You may want to check if you are perhaps trying to use a 32-bit driver from 64-bit Python or the other way around, but given the error message, that doesn't seem likely either. A recommendation would be to try the same code on another machine / user account and see if you can identify differences if it works there. – Grismar Nov 09 '20 at 01:39
  • Thanks very much, your right.. Im using a 32bit version of python and 64 bit version of MS office.. Kindly post as an answer and I will accept.. – James Cook Nov 09 '20 at 01:46

1 Answers1

2

Not sure what your exact situation is, but it's likely not a programming error but an installation / environment error. I have a standard install of 64-bit office and connect to it from 64-bit Python. And that works with your provided code.

You may want to check if you are perhaps trying to use a 32-bit driver from 64-bit Python or the other way around, but given the error message, that didn't seem too likely.

If that doesn't resolve it, a recommendation would be to try the same code on another machine / user account and see if you can identify differences if it does work there.

Grismar
  • 27,561
  • 4
  • 31
  • 54
  • Posted this as an answer after initial comment - I suppose the error message not being very clear about this is commonly a part of the problem. – Grismar Nov 09 '20 at 01:49