1

How can I access my Microsoft Access 2010 database (accdb) with pyodbc? Before, I used an mdb Database, which worked fine with the connection string being: 

ODBC_CONN_STR = 'DRIVER={Microsoft Access Driver (*.mdb)};DBQ=%s;' % ACCESS_DATABASE_FILE

Now I use:

import pyodbc
ACCESS_DATABASE_FILE = "PSA_TEST.accdb"
ODBC_CONN_STR = 'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=%s;' % ACCESS_DATABASE_FILE
conn = pyodbc.connect(ODBC_CONN_STR)

The error I get is: pyodbc.Error: ('HY000', '[HY000] [Microsoft][ODBC-Treiber für Microsoft Access] Kein zulässiger Dateiname. (-1044) (SQLDriverConnect)')

Which translates to "the filename is not acceptable". I found a related question, but the answer does not work for me (Connecting to MS Access 2007 (.accdb) database using pyodbc). I use 32 bit python according to the output of:

python -c 'import struct; print struct.calcsize("P") * 8'

and MS Access 32 bit.

[EDIT]

  • Just in case, I check with os.path.isfile(ACCESS_DATABASE_FILE) that the file actually exists
  • the file can be opened with Access
  • opening the previous mdb file with the new connection string gives the same error message, which afaik is not the expected behavior
Community
  • 1
  • 1
phobic
  • 914
  • 10
  • 24
  • 1
    Is the access database in your current working directory? If not you will need to give the path to the file as well as the filename. – ChrisProsser Sep 14 '13 at 21:07
  • Yes it is. The same code works properly with an mdb file in the same directory, and the slightly different connection string. – phobic Sep 15 '13 at 07:53
  • Actually, when I specify a wrong filename, I get the exact same error message. I checked if this is due to the underscore not being interpreted correctly, but changing the filename to test.accdb does not change the result. – phobic Sep 15 '13 at 07:58

1 Answers1

8

Ok, sorry to answer my own question, but by playing around, I learned that you need to specify the absolute path name if you use the second connection string:

ACCESS_DATABASE_FILE = 'C:\\path\\to\\PSA_TEST.accdb'
ODBC_CONN_STR = 'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=%s;' % ACCESS_DATABASE_FILE

Then it even works with the accdb file, as well as with the mdb file as expected.

phobic
  • 914
  • 10
  • 24
  • 2
    Thanks for taking the time to post your solution. There is nothing wrong with answering your own question. – Gord Thompson Sep 15 '13 at 13:49
  • for relative path we use dbPath = os.path.join(Path(__file__).parent.parent, "datasets\CDR\CDR_Data.mdb") connection_string = 'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=%s' %dbPath+';' – Nikhil Jan 08 '21 at 14:53