Migrating from Access

dpmcore can import data from Microsoft Access .accdb / .mdb files into any SQLAlchemy-supported database (SQLite, PostgreSQL, SQL Server).

Prerequisites

You need one of the following backends to read Access files:

Backend	Platform	Install
mdb-tools	Linux	apt install mdbtools (Debian/Ubuntu)
pyodbc + Access ODBC driver	Windows / macOS	pip install pyodbc + Microsoft Access driver

The service tries mdb-tools first, then falls back to pyodbc.

You also need pandas:

pip install dpmcore[migration]

Using the CLI

The simplest way to run a migration:

dpmcore migrate --source /path/to/dpm.accdb --database sqlite:///dpm.db

This will:

1. Read all user tables from the Access file (system tables are skipped).

2. Create the ORM schema in the target database.

3. Load the data using INSERT (preserving ORM column types).

4. Display a summary table with row counts per table.

Example output:

           Migration Results
┌──────────────────────┬───────┐
│ Table                │  Rows │
├──────────────────────┼───────┤
│ Release              │     8 │
│ Framework            │     3 │
│ Module               │    42 │
│ Table                │   312 │
│ ...                  │   ... │
└──────────────────────┴───────┘

Total: 58 tables, 145230 rows (backend: mdbtools)

Using the Python API

Via DpmConnection

from dpmcore import connect

with connect("postgresql://user:pass@host/dpm_db") as db:
    result = db.services.migration.migrate_from_access(
        "/path/to/dpm.accdb"
    )
    print(f"Tables: {result.tables_migrated}")
    print(f"Rows:   {result.total_rows}")
    print(f"Backend: {result.backend_used}")

    for name, count in result.table_details.items():
        print(f"  {name}: {count} rows")

    if result.warnings:
        for w in result.warnings:
            print(f"  WARNING: {w}")

Standalone (without DpmConnection)

from sqlalchemy import create_engine
from dpmcore.loaders.migration import MigrationService

engine = create_engine("sqlite:///dpm.db")
service = MigrationService(engine)
result = service.migrate_from_access("/path/to/dpm.accdb")

MigrationResult

The MigrationResult dataclass contains:

Field	Type	Description
tables_migrated	int	Number of tables loaded
total_rows	int	Total row count across all tables
table_details	Dict[str, int]	Table name to row count mapping
warnings	List[str]	Any non-fatal issues encountered
backend_used	str	"mdbtools" or "pyodbc"

How it works

1. Extract — Tables are read from the Access file using mdb-tools (mdb-tables + mdb-export) or pyodbc. Access system tables (MSys*, ~*) are automatically filtered out.

2. Create schema — Base.metadata.create_all(engine) creates all ORM tables if they don’t exist yet.

3. Load data — Each table’s DataFrame is written with df.to_sql(..., if_exists="append"). The append mode preserves ORM-created column types and constraints.

Type handling

• mdb-tools backend: CSV data is read as strings, then numeric columns are auto-detected via pd.to_numeric.

• pyodbc backend: Column types from the Access schema metadata (cursor.description) are used to enforce types. Text columns that contain numeric-looking values (e.g. postal codes "01234") are preserved as strings.

Error handling

• If neither mdb-tools nor pyodbc can read the file, a MigrationError is raised.

• If individual tables fail to load, a warning is recorded in result.warnings but the migration continues.