Getting Connected

Connecting with an ODBC DSN

As with other SQLAlchemy dialects that use ODBC, connecting via a DSN is the preferred method. Create the DSN in Windows' ODBC Administrator (tip: for best results, enable ExtendedAnsiSQL), then use one of the following:

Ordinary unprotected Access database

from sqlalchemy import create_engine
engine = create_engine("access+pyodbc://@your_dsn")

Encrypted Database (database-level password)

If when opening the database in Access you are prompted for a "database password" then the database has been encrypted (for .accdb) or protected with a database password (for .mdb). In that case use admin as the username in the connection URL:

from sqlalchemy import create_engine
engine = create_engine("access+pyodbc://admin:the_password@your_dsn")

Database protected by User-Level Security (ULS)

If when opening the database in Access you are prompted for a "Name" and a "Password" then the database has been configured for ULS. Use the corresponding username and password in the connection URL:

from sqlalchemy import create_engine
engine = create_engine("access+pyodbc://your_username:your_password@your_dsn")

Note: ULS is only available for the older .mdb file format and has been deprecated for many years. Therefore, ULS-protected databases are not officially supported by this dialect.

Connecting with an ODBC connection string

You can supply a typical ODBC connection string to create a "DSN-less" connection.

Ordinary unprotected Access database

import urllib
from sqlalchemy import create_engine
connection_string = (
    r"DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};"
    r"DBQ=C:\Users\Public\test\sqlalchemy-access\sqlalchemy_test.accdb;"
    r"ExtendedAnsiSQL=1;"
)
connection_uri = f"access+pyodbc:///?odbc_connect={urllib.parse.quote_plus(connection_string)}"
engine = create_engine(connection_uri)

Encrypted Database (database-level password)

Reflection of primary and foreign keys requires a second separate "ACE DAO" connection to the database, so we need to supply the password in two places.

driver = "{Microsoft Access Driver (*.mdb, *.accdb)}"
db_path = r"C:\Users\Public\test\sqlalchemy-access\gord_test.accdb"
pwd = "tiger"
connection_string = (
    f"DRIVER={driver};"
    f"DBQ={db_path};"
    f"PWD={pwd};"
    f"ExtendedAnsiSQL=1;"
)
connection_uri = (
    f"access+pyodbc://admin:{pwd}@/"
    f"?odbc_connect={urllib.parse.quote_plus(connection_string)}"
)
engine = sa.create_engine(connection_uri)