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")

Connecting with an ODBC connection string

You can supply a typical ODBC connection string to create a "DSN-less" connection like so:

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)