Search code examples
pythonms-accessodbcpyodbc

ODBC cannot open MS Access database whose password contains '{'

I am trying to connect to password protected Access Databases with pyodbc. The passwords are created based on the filename and may contain special characters. I've made sure the connection string is correct before calling the .connect() method.

import pyodbc
conn_str = r"DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\Users\user\PW issue\test.accdb;PWD=abc{123"
conn = pyodbc.connect(conn_str)

I'm getting the following error (shortened version - it only repeats itself from the '"[HY000]' part 2 more times:

Error: ('HY000', "[HY000] [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0x5f80 Thread 0x5a54 DBC 0x96f3be58
Jet'. (63) (SQLDriverConnect)")

I've tried to make sense of many sources such as tens of similar posts here, and also other places, for example: https://www.connectionstrings.com/formating-rules-for-connection-strings/

From what I can tell the issue could be how the driver interprets the connection string, not how I am structuring it in python. The two characters that I'm sure cause an issue on their own are "{" and "%". I mainly tried to focus on solving the issue with a file that only had "{" in it. The connection string works properly for any password that doesn't contain these 2.

Creating different passwords is not an option.

An example password would be: "abc{123" What I've tried already within the connection string:

  1. "...;{PWD=abc{123}"
  2. "...;'PWD=abc{123'"
  3. "...;PWD=abc{{123"
  4. "...;PWD=abc'{123"
  5. "...;PWD={abc'{123}"
  6. "...;PWD={abc{{123}" I've probably tried some other combinations too. As one source said to escape "{"s with doubling it, while the other source said the whole password has to be escaped with {}. Then again others said to enclose the password in single quotes, and another said to escape the special character with a single quote. So out of desperation I tried their combinations.
Solution

  • It seems that you have encountered a deficiency in the MS Access ODBC driver. When using other ODBC drivers like "ODBC Driver 17 for SQL Server", an open curly bracket in a password is not a problem unless it is the first character of the password. So, for logging in to SQL Server

    UID=john;PWD=abc{123;

    works just fine. If John's password was {abc then he would have to use

    UID=john;PWD={{abc};

    Furthermore, the MS Access OLEDB provider can open a database whose password is abc{123

    connStr = _
    "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=C:\Users\Public\zzz_test.accdb;" & _
    "Jet OLEDB:Database Password=abc{123;"

    so this is an issue with the Access ODBC driver.