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 sqlalchemy as sa
connection_string = (
    r"DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};"
    r"DBQ=C:\Users\Public\test\sqlalchemy-access\sqlalchemy_test.accdb;"
    r"ExtendedAnsiSQL=1;"
)
connection_url = sa.engine.URL.create(
    "access+pyodbc",
    query={"odbc_connect": connection_string}
)
engine = sa.create_engine(connection_url)

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.

import sqlalchemy as sa
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_url = sa.engine.URL.create(
    "access+pyodbc",
    username="admin",
    password=pwd,
    query={"odbc_connect": connection_string}
)
engine = sa.create_engine(connection_url)