# PyAccessKit — full documentation for language models
Generated by scripts/sync_docs.py. Start with the agent guide.


<!-- file: src/pyaccesskit/AGENT_GUIDE.md -->

# PyAccessKit — guide for AI coding agents

You are writing Python that builds or changes a Microsoft Access application with PyAccessKit. This guide
is the authoritative, compact reference for that job. Print it any time with `pyaccesskit guide`. Follow the
rules below exactly; they encode Access behaviour that is not obvious and that you cannot observe directly.

## 1. Workflow

1. **Check the machine** once: `pyaccesskit doctor --json`. Continue only if `"usable": true`.
   `"auto_engine"` says what `engine="auto"` uses (`"dao"` or `"access"`). Forms, modules and text
   import/export need full Microsoft Access (`engines[].engine == "access"` available).
2. **Understand an existing database** before changing it: `pyaccesskit inspect DB --json` (read-only, runs
   no startup code). Its `tables[].spec` entries are valid `TableSpec` JSON.
3. **Write the program** in three parts (see the complete example in section 9):
   *specs as data* → *one build function inside one `with` block* → *verification after reopening*.
4. **Run it**, read errors (section 7), fix, rerun. Building with `AccessDatabase.create(...)` is atomic,
   so a failed run leaves nothing behind and can simply be repeated.
5. **Verify**: `to_spec() == spec.normalized()` for tables, `fetch()` for queries, `check_opens()` for every
   form (this also compiles VBA).

Validate specs without Access: `pyaccesskit schema table` (JSON Schema), or construct the spec in Python —
invalid specs raise `SpecError` listing every problem.

## 2. Rules (MUST / NEVER)

Sessions and processes
- MUST open databases only with `with AccessDatabase.create(path, ...) as db:` or
  `with AccessDatabase.open(path, ...) as db:`. Everything is cleaned up on exit, also on exceptions.
- NEVER use `win32com.client.Dispatch`, `GetObject`, `DispatchEx` or `pythoncom` for Access. NEVER kill
  `MSACCESS.EXE` processes. PyAccessKit owns and closes its own Access processes; if a crash left one
  behind, run `pyaccesskit cleanup`.
- NEVER call `Quit`, `Close`, `CloseCurrentDatabase` on `db.raw.*` objects. Avoid `db.raw` unless the API
  truly lacks a feature.
- Use a session only on the thread that opened it. Do not open the same file in two sessions at once.
- `create()` refuses to replace an existing file unless `overwrite=True`.

Schema
- `Column.number(...)` is a Long Integer. Foreign keys that point to an `autonumber` use `Column.number`.
- Defaults: Python values (`default=0`, `default=True`, `default="pcs"`, `default=date(2026, 1, 1)`).
  Access expressions MUST be wrapped: `default=Expr("Now()")`, `default=Expr("Date()")`.
- Text length is 1–255 (`Column.text`); use `Column.long_text` for more. Decimal needs
  `precision` 1–28 and `scale <= precision`.
- At most one AutoNumber per table; name it `<Table>ID` by convention. Primary key index is named
  `PrimaryKey`; `unique=True`/`indexed=True` create an index named after the column.
- Names: ≤ 64 chars; no `.`, `!`, `` ` ``, `[`, `]`; no leading space. Names are case-insensitive:
  `Name` and `name` clash. AVOID reserved words and names like `Name`, `Date`, `Time`, `Value`, `Order`,
  `Level`, `Section`, `Note`, `Description`, `Type`, `Year`, `Month`, `User`, `Text` — use
  `CustomerName`, `OrderDate`, … PyAccessKit warns (`AccessNameWarning`); treat that warning as an error.
- Create tables before relationships, relationships before data, data before forms that need it.

SQL
- Access SQL (Jet/ACE), not ANSI/T-SQL: `TOP n` (no `LIMIT`), `IIf()` (no `CASE`), `&` concatenates strings,
  dates are `#2026-01-31#`, booleans `True`/`False`, `LIKE` wildcards are `*` and `?`, several joins need
  parentheses: `FROM (A INNER JOIN B ON ...) INNER JOIN C ON ...`.
- NEVER build SQL with f-strings or `+`. Pass values as parameters: SQL `[name]`, Python
  `db.execute(sql, {"name": value})`. Saved queries may declare `PARAMETERS [pX] Long;`.
- SQL that Python runs (`db.execute`, `db.fetch_all`, `Query.fetch`) goes through DAO outside the Access UI:
  use only engine functions (`IIf`, `IsNull`, `Sum`, `Format`, `Left`, `DateAdd`, `Year`…). `Nz()` and your
  own VBA functions are NOT available there (DAO error 3085). They do work in form control sources.

Forms and VBA
- Forms need full Access. Build them with `with db.forms.create(name, record_source=..., ...) as form:`;
  the form is saved when the block ends. Reuse a name only with `replace=True`.
- Buttons MUST have a name (`form.button("cmdSave", caption=..., on_click=Vba(...))`). Controls with events
  need plain VBA identifier names.
- `Vba("...")` holds a procedure **body** only — never `Sub`/`End Sub`. Put helpers and module-level
  variables in `form.module_code("...")` or in a standard module (`db.modules.create`).
- VBA is case-insensitive: a constant `PLATFORM` and a function `Platform` clash and the module will not
  compile (reported later as Access error 7960 or an `AccessDialogError`). Keep all names in a module
  unique ignoring case. `Option Compare Database` / `Option Explicit` are added for you.
- VBA text must be representable in the Windows ANSI code page (no emoji); otherwise `SpecError`.
- `default_view=FormView.CONTINUOUS` uses a tabular layout: labels go in the form header, so do not combine
  it with `header=False` unless every control has `label=False`.

## 3. Program skeleton

```python
from pathlib import Path

from pyaccesskit import AccessDatabase, Column, TableSpec

CUSTOMERS = TableSpec(
    name="Customers",
    columns=[
        Column.autonumber("CustomerID", primary_key=True),
        Column.text("CustomerName", length=120, required=True),
    ],
)


def build(db: AccessDatabase) -> None:
    db.tables.create(CUSTOMERS)


def verify(path: Path) -> None:
    with AccessDatabase.open(path, readonly=True) as db:
        assert db.tables["Customers"].to_spec() == CUSTOMERS.normalized()


def main(path: Path) -> None:
    with AccessDatabase.create(path, overwrite=True) as db:
        build(db)
    verify(path)
```

To change an existing database, open it writable and apply the change, then verify:

```python
from pyaccesskit import AccessDatabase, Column

with AccessDatabase.open("app.accdb") as db:
    if "Phone" not in db.tables["Customers"].fields:
        db.tables["Customers"].add_column(Column.text("Phone", length=30))
```

Open changes are not atomic across steps: back up the file first (copy it) when a change is risky.

## 4. API cheat sheet

```text
AccessDatabase.create(path, *, overwrite=False, atomic=True, engine="auto", options=None)
AccessDatabase.open(path, *, readonly=False, exclusive=None, password=None, engine="auto", options=None)
db.path  db.transport  db.format_version  db.readonly  db.is_open  db.access_pid
db.execute(sql, params=None) -> int                      # action SQL, returns affected rows
db.fetch_all(sql, params=None, *, limit=None) -> list[dict]
db.properties["AppTitle"] = "..." ; db.properties["StartUpForm"] = "frmMain" ; .get(name, default)

db.tables: names() | [name] | get(name) | in | len | iter | specs()
    create(TableSpec) | create(name, *, columns, indexes=(), primary_key=None, description=None,
                               validation_rule=None, validation_text=None, properties=None)
    drop(name, *, drop_relationships=False)
Table: name, fields, indexes, primary_key, description (settable), is_linked, properties, record_count(),
    to_spec(), add_column(col), drop_column(name), rename_column(old, new), create_index(IndexSpec),
    drop_index(name), rename(new), drop()
Field: name, data_type, size, required, spec, properties, rename(new), drop()

Column.text(name, *, length=255, allow_zero_length=False, unicode_compression=True, input_mask=None, **common)
Column.long_text(name, *, rich_text=False, append_only=False, **common)
Column.number(name, *, size=NumberSize.LONG_INTEGER, decimal_places=None, input_mask=None, **common)
Column.decimal(name, *, precision=18, scale=0, **common)      Column.currency(name, **common)
Column.autonumber(name, *, replication_id=False, primary_key=..., description=..., caption=...)
Column.date_time(name, **common)  Column.yes_no(name, **common)  Column.hyperlink(name, **common)
Column.ole_object(name, *, required=False, description=..., caption=...)
    common = required, default, validation_rule, validation_text, description, caption, format,
             primary_key, unique, indexed, properties={...}
IndexSpec.on(name, *columns, unique=False, ignore_nulls=False, required=False)   # ("Col", "desc") pairs
IndexSpec.primary_key(*columns)
TableSpec(name, columns, indexes=(), primary_key=None|"Col"|["A","B"], description, validation_rule, ...)

db.relationships.create("Primary.Col", "Foreign.Col", *, name=None, enforce_integrity=True,
    cascade_update=False, cascade_delete=False, one_to_one=False, join=JoinType.INNER)
db.relationships.create(RelationshipSpec.between("A.ID", "B.AID", cascade_delete=True))
    composite: create(("A", ["K1", "K2"]), ("B", ["K1", "K2"]))

db.queries.create(name, sql, *, description=None, replace=False) | create(QuerySpec, replace=False)
db.queries.create_pass_through(name, sql, *, connect="ODBC;...", returns_records=True, timeout=60)
Query: name, sql (settable), kind, parameters, description, execute(params) -> int,
    fetch(params=None, *, limit=None) -> list[dict], rename(new), drop(), to_spec()

db.forms.create(name, *, replace=False, **FormSpec options) -> FormBuilder (use as context manager)
    FormSpec options: record_source, caption, default_view (FormView.SINGLE|CONTINUOUS|DATASHEET|SPLIT),
    layout (LayoutKind.AUTO|STACKED|TABULAR|NONE), header, width, allow_additions, allow_edits,
    allow_deletions, data_entry, navigation_buttons, record_selectors, dividing_lines, scroll_bars,
    auto_center, pop_up, modal, option_explicit, properties={...}
FormBuilder: textbox(field=None, *, label=None|False|"text", control_source=None, format=None, enabled=True,
        locked=False, after_update=None, name=None, section=Section.DETAIL, at=(left, top), width, height,
        visible=True, properties={})
    checkbox(field, *, label, enabled, locked, after_update, ...)
    combobox(field, *, row_source, row_source_type=RowSourceType.TABLE_QUERY|VALUE_LIST, bound_column=1,
        column_count=1, column_widths=[cm(0), cm(4)], limit_to_list=True, label, ...)
    label(caption, ...)      button(name, *, caption, on_click=None, section, at, width, height)
    on_load(Vba) on_current(Vba) module_code(str) to_spec() save() discard()
db.forms.build(FormSpec, replace=False) ; db.forms[name]: controls(), check_opens(), export_text(),
    rename(new), drop()

db.modules.create(name, code, *, kind=ModuleKind.STANDARD|CLASS, replace=False) ; Module: code (settable),
    kind, rename(new), drop()
db.objects: names(kind) export_text(kind, name) import_text(kind, name, text, *, replace=False)
    save_text(kind, name, path) load_text(kind, name, path, *, replace=False) delete(kind, name)
    rename(kind, old, new)        kind = "form" | "report" | "macro" | "module" | "query"

Units: cm(2), mm(5), inch(1), pt(12), twips(1440); Length supports + - * / and comparisons.
Enums: FormView single|continuous|datasheet|split; LayoutKind auto|stacked|tabular|none;
    NumberSize byte|integer|long_integer|single|double|replication_id; Section detail|header|footer;
    RowSourceType table_query|value_list; ModuleKind standard|class; JoinType inner|left|right;
    QueryKind select|crosstab|delete|update|append|make_table|ddl|pass_through|union|...
SessionOptions(visible=False, macro_security=MacroSecurity.DISABLE, dialog_policy=DialogPolicy.FAIL,
    call_timeout=600.0, quit_timeout=30.0, access_progid="Access.Application")
```

All names above are importable from `pyaccesskit` (form control specs from `pyaccesskit.forms`).

## 5. Values in and out

| Access type | Python value written | Python value read |
|---|---|---|
| Short/Long Text, Hyperlink | `str` | `str` |
| Number (Byte/Integer/Long) | `int` | `int` |
| Number (Single/Double) | `float` | `float` |
| Decimal, Currency | `Decimal` (or `int`) | `Decimal` |
| Date/Time | naive `datetime` or `date` (wall-clock) | naive `datetime` |
| Yes/No | `bool` | `bool` |
| OLE Object | `bytes` | `bytes` |
| Replication ID (GUID) | `str` | `str` in DAO's form `"{guid {…}}"` |
| Null | `None` | `None` |

A `time` reads back as a `datetime` on 1899-12-30 (Access's day zero). Aggregates can come back as `float`
(e.g. `Sum` over integers through `IIf`). `bytes` parameters work in `db.execute`/`db.fetch_all`; a saved
query that receives bytes must declare the parameter: `PARAMETERS [payload] LongBinary;`.

## 6. Access limits

| Limit | Value |
|---|---|
| Object and column names | 64 characters |
| Columns per table | 255 |
| Indexes per table (including relationship indexes) | 32 |
| Columns per index | 10 |
| Short Text length | 255 |
| Form width / section height | 22 inches (`inch(22)`) |
| Database size | 2 GB |
| SQL statement | ~64,000 characters |

## 7. Errors and what to do

All exceptions derive from `pyaccesskit.PyAccessKitError`; `str(exc)` says what failed and why;
`exc.details.number` holds the Access/DAO error number when there is one.

| Exception | Typical cause | Fix |
|---|---|---|
| `SpecError` | invalid spec, name, option or default | read `exc.problems`, correct the spec |
| `ObjectExistsError` | name already used (tables and queries share one namespace) | pick another name, or `replace=True` where offered |
| `ObjectNotFoundError` | wrong table/column/query/form name | check `names()`; lookups are case-insensitive |
| `RelationshipError` | incompatible key types, no unique index on the primary side | primary side needs a PK/unique index; FK type `Column.number` for AutoNumber keys |
| `IntegrityViolationError` | duplicate key (3022), missing related row (3201), rows still related (3200) | fix the data or insert parents first |
| `SqlSyntaxError` | invalid Access SQL (`exc.sql` holds it) | apply the SQL rules in section 2 |
| `MissingParameterError` | a `[name]` in SQL without a value (often a misspelled column) | pass the parameter or fix the column name |
| `ComError` 3085 "Undefined function" | `Nz`/VBA function in SQL run from Python | use `IIf(IsNull(x), 0, x)` |
| `ComError` 7960 / `AccessDialogError` naming VBA | VBA does not compile | fix the module (duplicate names, missing `End Sub`, typos) |
| `AccessDialogError` | Access showed a modal dialog; `exc.dialogs` has title/text | usually VBA or a broken expression; read the text |
| `CapabilityError` | design feature with `engine="dao"` or `readonly=True` | use `engine="auto"`/`"access"`, open writable |
| `AccessRuntimeOnlyError` | only the Access Runtime is installed | forms/modules impossible; schema and data still work |
| `DatabaseLockedError` | file open elsewhere (exclusive) | close it in Access, or open `readonly=True` |
| `DatabaseExistsError` | target exists | `overwrite=True`, or another path |
| `AccessTimeoutError` | one call exceeded `call_timeout`; the owned Access was ended | split the work, raise `SessionOptions(call_timeout=...)` |
| `EngineUnavailableError` | no usable engine | run `pyaccesskit doctor` |

Warnings: `AccessNameWarning` (troublesome name) — rename; `AccessDialogWarning` (with
`dialog_policy="warn"`).

## 8. Not supported yet (do not attempt through PyAccessKit)

Reports, subforms, tab controls, list boxes, option groups, attachment / calculated / multi-value / lookup
columns, creating linked tables, macros (other than raw text import), ribbons, VBA references, compiling on
demand, encrypted database creation, changing a column's type or order (create a new table and copy rows
with `INSERT INTO ... SELECT` instead). For anything essential, `db.raw.access` / `db.raw.dao` expose the
underlying objects; save your own raw design changes.

## 9. Complete example

A small inventory application: three related tables, two queries (one with a parameter), a VBA module using
conditional compilation, a single form with lookups and events, a continuous form, startup settings, and
verification. It is `examples/04_inventory_app.py` in the source tree and is run by the test-suite.

<!-- example:start -->
```python
"""A complete small Access application, written the way the agent guide recommends.

Run:  python examples/04_inventory_app.py [path/to/inventory.accdb]

Structure: (1) the schema as immutable specs, (2) one build function, (3) verification that reopens the
file and checks what was built. Everything happens in one atomic ``create()``: if any step fails, no file
is left behind and the Access process PyAccessKit started is closed.
"""

from __future__ import annotations

import sys
from decimal import Decimal
from pathlib import Path

from pyaccesskit import (
    AccessDatabase,
    Column,
    Expr,
    FormView,
    NumberSize,
    RelationshipSpec,
    RowSourceType,
    TableSpec,
    Vba,
    cm,
)

# --- 1. Schema as data ---------------------------------------------------------------------------
TABLES = [
    TableSpec(
        name="Categories",
        columns=[
            Column.autonumber("CategoryID", primary_key=True),
            Column.text("CategoryName", length=60, required=True, unique=True),
        ],
    ),
    TableSpec(
        name="Products",
        description="Everything we stock",
        columns=[
            Column.autonumber("ProductID", primary_key=True),
            Column.text("ProductName", length=100, required=True, indexed=True),
            Column.number("CategoryID", required=True),
            Column.text("Unit", length=10, required=True, default="pcs"),
            Column.currency("UnitCost", default=0, validation_rule=">=0"),
            Column.number("ReorderLevel", size=NumberSize.INTEGER, default=5),
            Column.yes_no("Discontinued", default=False),
        ],
    ),
    TableSpec(
        name="StockMoves",
        columns=[
            Column.autonumber("MoveID", primary_key=True),
            Column.number("ProductID", required=True),
            Column.date_time("MovedAt", default=Expr("Now()"), format="General Date"),
            Column.number(
                "Quantity",
                required=True,
                validation_rule="<>0",
                validation_text="Use a positive number for receipts, negative for issues",
            ),
            Column.text("Reference", length=50),
        ],
    ),
]
RELATIONSHIPS = [
    RelationshipSpec.between("Categories.CategoryID", "Products.CategoryID"),
    RelationshipSpec.between("Products.ProductID", "StockMoves.ProductID", cascade_delete=True),
]
QUERIES = {
    "qryStockLevels": (
        # Only database-engine functions here: Nz() and VBA functions exist inside Access, not in DAO.
        "SELECT p.ProductID, p.ProductName, p.ReorderLevel,\n"
        "IIf(IsNull(Sum(m.Quantity)), 0, Sum(m.Quantity)) AS OnHand\n"
        "FROM Products AS p LEFT JOIN StockMoves AS m ON p.ProductID = m.ProductID\n"
        "GROUP BY p.ProductID, p.ProductName, p.ReorderLevel;"
    ),
    "qryLowStock": (
        "PARAMETERS [pMargin] Long;\n"
        "SELECT ProductName, OnHand, ReorderLevel FROM qryStockLevels\n"
        "WHERE OnHand <= ReorderLevel + [pMargin] ORDER BY ProductName;"
    ),
}
INVENTORY_MODULE = """\
Private lastRefresh As Date

#If Win64 Then
Private Const PLATFORM_NAME As String = "64-bit Office"
#Else
Private Const PLATFORM_NAME As String = "32-bit Office"
#End If

Public Function OnHand(ByVal productID As Long) As Long
    OnHand = Nz(DSum("Quantity", "StockMoves", "ProductID=" & productID), 0)
    lastRefresh = Now()
End Function

Public Function Platform() As String
    Platform = PLATFORM_NAME
End Function
"""


# --- 2. Build ------------------------------------------------------------------------------------
def build(db: AccessDatabase) -> None:
    for table in TABLES:
        db.tables.create(table)
    for relationship in RELATIONSHIPS:
        db.relationships.create(relationship)
    for name, sql in QUERIES.items():
        db.queries.create(name, sql)

    for category in ("Stationery", "Hardware"):
        db.execute("INSERT INTO Categories (CategoryName) VALUES ([name])", {"name": category})
    products = [("Notebook", 1, Decimal("1.20")), ("Stapler", 2, Decimal("6.50"))]
    for name, category, cost in products:
        db.execute(
            "INSERT INTO Products (ProductName, CategoryID, UnitCost) VALUES ([n], [c], [cost])",
            {"n": name, "c": category, "cost": cost},
        )
    db.execute("INSERT INTO StockMoves (ProductID, Quantity, Reference) VALUES (1, 40, 'PO-1')")
    db.execute("INSERT INTO StockMoves (ProductID, Quantity, Reference) VALUES (2, 3, 'PO-2')")

    db.modules.create("modInventory", INVENTORY_MODULE)

    with db.forms.create(
        "frmProducts", record_source="Products", caption="Products", width=cm(16)
    ) as form:
        form.textbox("ProductName", label="Product", width=cm(8))
        form.combobox(
            "CategoryID",
            label="Category",
            row_source="SELECT CategoryID, CategoryName FROM Categories ORDER BY CategoryName",
            column_count=2,
            column_widths=[cm(0), cm(5)],
        )
        form.combobox(
            "Unit", row_source='"pcs";"box";"kg"', row_source_type=RowSourceType.VALUE_LIST
        )
        form.textbox("UnitCost", label="Unit cost", format="Currency")
        form.textbox(
            name="txtOnHand",
            label="On hand",
            control_source="=OnHand([ProductID])",
            locked=True,
            enabled=False,
        )
        form.checkbox("Discontinued")
        form.button("cmdClose", caption="Close", on_click=Vba("DoCmd.Close acForm, Me.Name"))
        form.on_current(Vba("Me.txtOnHand.Requery"))

    with db.forms.create(
        "frmStockMoves",
        record_source="SELECT * FROM StockMoves ORDER BY MovedAt DESC",
        caption="Stock moves",
        default_view=FormView.CONTINUOUS,
    ) as form:
        form.textbox("MovedAt", width=cm(4))
        form.textbox("ProductID", width=cm(2))
        form.textbox("Quantity", width=cm(2))
        form.textbox("Reference", width=cm(4))

    db.properties["AppTitle"] = "Inventory"
    db.properties["StartUpForm"] = "frmProducts"


# --- 3. Verify -----------------------------------------------------------------------------------
def verify(path: Path) -> None:
    with AccessDatabase.open(path) as db:
        for table in TABLES:  # what Access stored is exactly what was specified
            assert db.tables[table.name].to_spec() == table.normalized(), table.name
        low = db.queries["qryLowStock"].fetch({"pMargin": 0})
        assert [row["ProductName"] for row in low] == ["Stapler"], low
        for name in db.forms.names():
            db.forms[name].check_opens()  # compiles the form's module and opens it hidden
        print(f"verified {path}: {db.tables.names()}, forms {db.forms.names()}, low stock {low}")


def main(target: Path) -> None:
    with AccessDatabase.create(target, overwrite=True) as db:
        build(db)
    verify(target)


if __name__ == "__main__":
    main(Path(sys.argv[1]) if len(sys.argv) > 1 else Path.cwd() / "inventory.accdb")
```
<!-- example:end -->


<!-- file: docs/index.md -->

# PyAccessKit

PyAccessKit is a typed, Pythonic toolkit for creating and modifying Microsoft Access databases (`.accdb`)
and, progressively, whole Access applications. You work with databases, tables, columns, indexes,
relationships, queries, forms, controls and modules. Raw COM objects, DAO type codes and `MSACCESS.EXE`
processes stay out of your code.

```python
from pyaccesskit import AccessDatabase, Column

with AccessDatabase.create("crm.accdb") as db:
    db.tables.create(
        "Customers",
        columns=[
            Column.autonumber("CustomerID", primary_key=True),
            Column.text("CustomerName", length=200, required=True),
        ],
    )
```

## Why

Automating Access from Python usually means `win32com.client.Dispatch("Access.Application")`, then calls
like `CreateField("x", 10, 255)` and `CreateControl(frm, 109, 0, ...)`. After that you are catching
`com_error` tuples, and finding that `MSACCESS.EXE` is still running after the script has crashed. Worse,
`Dispatch` attaches to an Access window the user already has open, so the script's `Quit()` can close the
user's work.

PyAccessKit replaces that with:

- **Specs**: immutable, validated, serializable descriptions (`TableSpec`, `Column.text(...)`,
  `RelationshipSpec`, `FormSpec`…). Most mistakes are caught before Access is involved.
- **Handles and collections**: `db.tables["Customers"].fields["Email"]`, case-insensitive like Access.
  Handles are name-based and never hold COM objects.
- **An isolated, typed COM boundary**: late binding, exact-argument `IDispatch` calls, and COM errors
  translated into specific exceptions that carry the Access or DAO error number.
- **Process ownership**: every Access process is started fresh, tracked by identity, placed in a
  kill-on-close job object, and closed even when your code raises.

## Built for AI coding agents

Specs are plain, validated data with a JSON Schema, and errors explain themselves. Builds are atomic and
every Access process is cleaned up, so an agent can write, run and fix code in a loop without leaving
damage behind. `pyaccesskit guide` prints a compact, version-matched guide that tells an agent how to write
Access applications with the library. See [Building with AI agents](agents/index.md).

## Design principles

1. **The COM world is quarantined.** Only a few internal packages import `pywin32`. Everything else,
   including every spec, is pure Python, importable on any OS, and strictly type-checked.
2. **Validate before COM.** Names, limits, types, relationships and form layouts are checked in Python
   first.
3. **Atomic where Access allows it.** Databases are built in a temporary file, tables are created
   all-or-nothing, and forms are built under a temporary name and swapped in.
4. **One owner.** A session owns the engine and the Access process. You never need to call `Quit()`.
5. **One model for code and files.** The same spec models drive today's imperative API and tomorrow's
   declarative project format.

## Roadmap

| Version | Planned |
|---|---|
| **0.1** | Lifecycle and safety, tables, relationships, queries and data, small form API, VBA modules, text import/export, CLI (`doctor`, `inspect`, `cleanup`) |
| 0.2 | Reports, calculated/attachment/lookup columns, linked tables, database settings spec, VBA references and compile checks, `Application.Run`, `db.detach(show=True)`, public fake engine for tests |
| 0.3 | Project format (YAML/SQL/VBA files), JSON Schema, `pyaccesskit export` and `pyaccesskit build` |
| 0.4 | `pyaccesskit plan` / `apply`: desired-state schema changes, replace-if-changed forms and modules |
| 0.5+ | Native form/report specs as the main authoring path, seed data, ribbons, navigation pane, themes, data macros |

The project format will be built on the same specs you use today. `Table.to_spec()` already returns a
canonical, round-trippable `TableSpec`.


<!-- file: docs/getting-started.md -->

# Getting started

## Install

```console
pip install pyaccesskit
pyaccesskit doctor
```

`doctor` tells you which engines work on your machine and why (see [Engines](concepts/engines.md)). If it
reports that PyAccessKit is ready, everything below works.

## Create a database

```python
from datetime import date
from decimal import Decimal

from pyaccesskit import AccessDatabase, Column, Expr

with AccessDatabase.create("crm.accdb") as db:
    db.tables.create(
        "Customers",
        columns=[
            Column.autonumber("CustomerID", primary_key=True),
            Column.text("CustomerName", length=120, required=True),
            Column.text("Email", length=255, unique=True),
            Column.yes_no("IsActive", default=True),
            Column.date_time("CreatedAt", default=Expr("Now()")),
        ],
        description="People and companies we sell to",
    )
    db.tables.create(
        "Orders",
        columns=[
            Column.autonumber("OrderID", primary_key=True),
            Column.number("CustomerID", required=True),
            Column.date_time("OrderDate", default=Expr("Date()")),
            Column.currency("Amount", default=0, validation_rule=">=0"),
        ],
    )
    db.relationships.create("Customers.CustomerID", "Orders.CustomerID", cascade_delete=True)
    db.queries.create(
        "qryCustomerTotals",
        "SELECT c.CustomerName, Sum(o.Amount) AS Total "
        "FROM Customers AS c LEFT JOIN Orders AS o ON c.CustomerID = o.CustomerID "
        "GROUP BY c.CustomerName;",
    )

    db.execute(
        "INSERT INTO Customers (CustomerName, Email) VALUES ([name], [email])",
        {"name": "Ada Lovelace", "email": "ada@example.com"},
    )
    db.execute(
        "INSERT INTO Orders (CustomerID, OrderDate, Amount) VALUES (1, [day], [amount])",
        {"day": date(2026, 1, 31), "amount": Decimal("149.95")},
    )
    print(db.queries["qryCustomerTotals"].fetch())
```

A few things to notice:

- **The `with` block matters.** The file appears at `crm.accdb` only when the block ends without an
  exception (atomic creation). Any Access process PyAccessKit started is closed either way.
- **Defaults are Python values**, rendered as correct Access literals: `True`, `0`, or a quoted string.
  Expressions are wrapped in `Expr(...)`.
- **Parameters are bound by name.** `[name]` in the SQL is an Access parameter, and values are never
  pasted into the SQL text.
- **`Column.number` is a Long Integer by default**, like the Access table designer. Pass
  `size=NumberSize.INTEGER` for Access's 16-bit *Integer*.

## Open an existing database

```python
with AccessDatabase.open("crm.accdb", readonly=True) as db:
    customers = db.tables["customers"]  # names are case-insensitive, as in Access
    print(customers.fields["Email"].size)
    print(customers.to_spec().model_dump_json(indent=2))
    for query in db.queries:
        print(query.name, query.kind, query.sql)
```

Read-only sessions open the file in shared mode, so it can stay open in Access. They never run the
database's startup code.

## Add a form and some VBA

Forms and modules need Microsoft Access (the full product, not the Runtime). The session switches to
Access automatically the first time you use them:

```python
from pyaccesskit import AccessDatabase, Vba, cm

with AccessDatabase.open("crm.accdb") as db:
    db.modules.create(
        "modFormatting",
        'Public Function Money(ByVal v As Currency) As String\n    Money = Format$(v, "Currency")\nEnd Function\n',
    )
    with db.forms.create("frmCustomers", record_source="Customers", caption="Customers") as form:
        form.textbox("CustomerName", label="Customer name", width=cm(8))
        form.textbox("Email", width=cm(8))
        form.checkbox("IsActive")
        form.button("cmdClose", caption="Close", on_click=Vba("DoCmd.Close acForm, Me.Name"))
    db.forms["frmCustomers"].check_opens()  # opens it hidden in Form view, then closes it
```

## The escape hatch

Anything PyAccessKit doesn't cover yet is reachable through `db.raw`:

```python
with AccessDatabase.open("crm.accdb") as db:
    dao_database = db.raw.dao  # the DAO Database
    access = db.raw.access  # Access.Application (switches to a design session)
    print(access.Version)
```

Raw objects belong to the session. They stop working when it closes or switches engines, and you must not
call `Quit()` or `Close()` on them. Save your own raw design changes: PyAccessKit quits Access without
saving.

## Next steps

- [Engines](concepts/engines.md): what happens under the hood, and bitness.
- [Lifecycle & safety](concepts/lifecycle.md): what is cleaned up when, and the session options.
- The guides for [tables](guides/tables.md), [relationships](guides/relationships.md),
  [queries](guides/queries.md), [forms](guides/forms.md), [modules](guides/modules.md) and
  [text I/O](guides/text-io.md).
- The runnable scripts in the `examples/` folder of the source tree.


<!-- file: docs/concepts/engines.md -->

# Engines

PyAccessKit uses DAO for all schema and data work, and Microsoft Access (`Access.Application`) for design
work: forms, modules, and `SaveAsText`/`LoadFromText`. DAO can be reached in three ways, and the choice
matters for bitness, licensing and safety.

| Transport (`db.transport`) | How | Good for | Caveats |
|---|---|---|---|
| `dao-inproc` | `DAO.DBEngine.120` loaded into Python | Fastest. No `MSACCESS.EXE`. Works with only the Access Runtime installed. | Python and Office must have the **same bitness** |
| `access-hosted` | A hidden Access instance owned by PyAccessKit hosts DAO; the database is opened with `DBEngine.OpenDatabase` | Any bitness combination. The database is not opened in the Access UI, so startup code never runs. | Starts one `MSACCESS.EXE` per session |
| `access-design` | The database is Access's *current database* (`OpenCurrentDatabase`) | Forms, modules, text import/export | Needs full Access, not the Runtime. Startup code is governed by `macro_security`. |

## Choosing an engine

```python
AccessDatabase.open("app.accdb")  # engine="auto" (default)
AccessDatabase.open("app.accdb", engine="dao")  # in-process DAO only; never starts Access
AccessDatabase.open("app.accdb", engine="access")  # always through Microsoft Access
```

- **`auto`** uses in-process DAO when this Python can load it, and Microsoft Access otherwise. The first
  design feature (a form, a module, text I/O, `db.raw.access`) switches the session to a design session.
- **`dao`** never starts Access. Design features raise `CapabilityError`. If DAO cannot be loaded, the
  session raises `DaoNotAvailableError` with an explanation such as *"DAO is installed for 32-bit
  programs only, but this Python is 64-bit"*.
- **`access`** always goes through an owned Access instance, using Access-hosted DAO for schema work until a
  design feature needs a design session.

## The switch to a design session

When a session switches engines, PyAccessKit releases the in-process DAO connection, starts (or reuses) its
own Access instance, and opens the database as the current database. Two consequences:

- **Handles keep working.** `db.tables["Customers"]` holds only a name, so it is valid after the switch.
- **Raw objects are revoked.** A `db.raw.dao` obtained before the switch raises `SessionClosedError`.
  Get a fresh one afterwards.

Read-only sessions cannot switch: opening a database for design needs exclusive, writable access.

## Capabilities

| Capability | In-process DAO | Access-hosted DAO | Design session |
|---|---|---|---|
| Create / open / close | ✅ | ✅ | ✅ |
| Tables, fields, indexes, field properties | ✅ | ✅ | ✅ |
| Decimal columns | ✅ (ADO + ACE OLEDB) | switches to design | ✅ (`CurrentProject.Connection`) |
| Relationships, saved and pass-through queries, execute/fetch | ✅ | ✅ | ✅ |
| List forms, reports, macros and modules | ✅ | ✅ | ✅ |
| Create or modify forms and modules; text import/export | ❌ | switches to design | ✅ |
| Runs the database's startup code | never | never | per `macro_security` (off by default) |

## Bitness

| Python | Office / Access Runtime | In-process DAO | Access transports |
|---|---|---|---|
| 64-bit | 64-bit | ✅ | ✅ |
| 32-bit | 32-bit | ✅ | ✅ |
| 64-bit | 32-bit | ❌ | ✅ |
| 32-bit | 64-bit | ❌ | ✅ |

Microsoft 365 is often installed as 32-bit, while Python installers default to 64-bit. PyAccessKit works in
that combination through Access. For in-process speed, add a matching Python, for example
`uv python install cpython-3.12-windows-x86`.

## Same result from every engine

DAO's `CreateDatabase` and Access's `NewCurrentDatabase` write slightly different database properties.
When DAO creates a database, PyAccessKit adds the properties Access itself writes (tabbed documents, and so
on). A database looks the same in Access whichever engine created it. To disable this, set
`SessionOptions(apply_native_defaults=False)`.


<!-- file: docs/concepts/lifecycle.md -->

# Lifecycle & safety

## Sessions

`AccessDatabase.create()` and `AccessDatabase.open()` start a **session**. The session owns everything
that has to be cleaned up: the DAO connection, the Access process if one was started, and the temporary
file of an atomic create. Use the database as a context manager:

```python
with AccessDatabase.open("app.accdb") as db:
    ...
```

`db.close()` does the same explicitly and is idempotent. If you forget both, a finalizer cleans up when the
object is garbage-collected and emits a `ResourceWarning`.

## Atomic creation

```python
with AccessDatabase.create("app.accdb", overwrite=True) as db:
    build_everything(db)
```

The database is built in a hidden sibling file (`.app.pak-1a2b3c4d.accdb`). When the block ends normally,
that file replaces `app.accdb`. When it raises, the file is deleted and `app.accdb`, even an existing one
you asked to overwrite, is untouched. Without `overwrite=True`, a file that someone else created at
`app.accdb` in the meantime is never replaced: closing raises a `CleanupError` wrapping
`DatabaseExistsError`, and the new database is left at its temporary path. Pass `atomic=False` to
build in place.

## Closing, even when things go wrong

Closing runs these steps in order. Each step is guarded, so one failure never skips the rest:

1. Revoke `db.raw` proxies.
2. Close objects PyAccessKit opened in Access, then the database.
3. `Quit` Access without saving (every change PyAccessKit makes is saved explicitly, as it happens).
4. Wait for the process to exit, up to `quit_timeout`. If it is still running, terminate **our** process
   handle.
5. Remove the ledger entry, then commit or discard the atomic temporary file.

If the session is closing because of an exception, cleanup failures are attached to that exception as
notes, so the original error is never masked. On a normal close they are raised together as
`CleanupError`.

## Process ownership

- **A fresh instance, every time.** PyAccessKit starts Access with `CoCreateInstanceEx` as a local server.
  It never uses `Dispatch()` or `GetObject()`, which attach to an Access instance the user already has
  running.
- **Identity, not names.** The process is identified by PID, creation time and image path, and held open
  by handle, so a recycled PID can never be confused with it. PyAccessKit never kills `MSACCESS.EXE` by
  name.
- **Kill on crash.** The process is placed in a Windows job object with *kill-on-close*. If Python dies for
  any reason, including a hard crash or `os._exit`, Windows ends Access too.
- **Ledger.** Each owned process is recorded in `%LOCALAPPDATA%\PyAccessKit\owned`.
  `pyaccesskit cleanup` (or `pyaccesskit.reap_orphans()`) ends only recorded processes whose Python owner
  has exited, and only after re-checking their identity.

## Dialogs, timeouts and Ctrl+C

Office is not designed for unattended automation. A hidden Access can show a modal dialog and wait forever.
PyAccessKit watches for dialogs, but only in windows that belong to its own Access process:

- `dialog_policy="fail"` (default): the dialog is recorded, dismissed (Cancel, then No, then OK) and
  reported as `AccessDialogError`, with its title, text and buttons.
- `dialog_policy="warn"`: dismissed and reported as an `AccessDialogWarning`.
- `dialog_policy="off"`: not watched.

A single operation that runs longer than `call_timeout` (default 600 s) terminates the owned process and
raises `AccessTimeoutError`. Pressing Ctrl+C during a long call terminates the owned process, so
`KeyboardInterrupt` surfaces promptly.

## Threads

COM objects belong to the thread (apartment) that created them. A session can only be used from the thread
that opened it, and that includes `close()`: other threads get `WrongThreadError`. For parallel work, use separate processes, each with
its own session.

## Session options

```python
from pyaccesskit import AccessDatabase, DialogPolicy, MacroSecurity, SessionOptions

options = SessionOptions(visible=True, call_timeout=120, dialog_policy=DialogPolicy.WARN)
with AccessDatabase.open("app.accdb", options=options) as db:
    ...
```

| Option | Default | Meaning |
|---|---|---|
| `visible` | `False` | Show the Access window (useful for debugging) |
| `macro_security` | `MacroSecurity.DISABLE` | What Access may run when it opens the database for design: `DISABLE`, `USE_UI` (the user's Trust Center settings) or `ENABLE` |
| `dialog_policy` | `DialogPolicy.FAIL` | See above |
| `call_timeout` | `600.0` | Seconds per operation; `None` disables the limit |
| `quit_timeout` | `30.0` | Seconds to wait for Access to exit before terminating it |
| `kill_on_parent_exit` | `True` | Use the kill-on-close job object |
| `access_progid` | `"Access.Application"` | For example, `"Access.Application.16"` on machines with several versions |
| `apply_native_defaults` | `True` | Add Access's default database properties when DAO creates a database |


<!-- file: docs/concepts/specs.md -->

# Specs, handles & collections

PyAccessKit has three kinds of objects.

## Specs: what something should look like

Specs are immutable, validated [Pydantic](https://docs.pydantic.dev/) models: `TableSpec`, the column
types returned by `Column.*`, `IndexSpec`, `RelationshipSpec`, `QuerySpec` and `FormSpec`.

```python
from pyaccesskit import Column, TableSpec

customers = TableSpec(
    name="Customers",
    columns=[
        Column.autonumber("CustomerID", primary_key=True),
        Column.text("Email", length=255, unique=True),
    ],
)
```

- **Validated on construction.** Invalid names, a second AutoNumber, `length=300` for Short Text, a
  duplicate column, or options that do not apply to a type (`Column.yes_no("x", length=5)`) raise
  `SpecError` with every problem listed. Nothing touches Access.
- **Serializable.** `customers.model_dump_json()` and `TableSpec.model_validate_json(...)` round-trip.
  `TableSpec.model_json_schema()` describes the format for editors and AI tools. Lengths serialize as
  strings such as `"2cm"`.
- **Canonical.** `spec.normalized()` expands shorthands (`primary_key=True`, `unique=True` on columns)
  into explicit `IndexSpec`s. `Table.to_spec()` returns exactly that normal form, so
  `db.tables.create(spec)` followed by `db.tables[name].to_spec() == spec.normalized()` holds. Future
  `plan`/`apply` will be built on this.

## Handles: live, name-based views

`db.tables["Customers"]`, `table.fields["Email"]`, `db.queries["qryX"]`, `db.forms["frmX"]` and
`db.modules["modX"]` are handles. A handle holds only the session and the object's name, never a COM
object. It stays valid when the session switches engines, and it reads the current state each time you ask.

```python
table = db.tables["customers"]  # case-insensitive, like Access
table.add_column(Column.text("Phone", length=30))
table.rename("Clients")  # the handle follows the rename
spec = table.to_spec()  # an immutable snapshot
```

## Collections

`db.tables`, `db.relationships`, `db.queries`, `db.forms`, `db.modules` and `db.objects` support
`names()`, iteration, `len()`, `in`, `[]` and `get()`, plus `create(...)` and `drop(...)`.
`create` accepts either a spec or keyword arguments:

```python
db.tables.create(customers)  # a spec
db.tables.create("Customers", columns=[...], primary_key="ID")  # keyword arguments
```

## Names

Access compares names case-insensitively. PyAccessKit does the same when looking things up and when
checking for duplicates. Names are validated before Access sees them: at most 64 characters, none of
`` . ! ` [ ] ``, no leading space, no control characters. Legal but troublesome names, such as reserved
words (`Name`, `Date`, `Order`…) or names with spaces, trigger an `AccessNameWarning` pointing at your
code.

## Units

Access measures layouts in twips (1/1440 inch). PyAccessKit uses a `Length` type instead:

```python
from pyaccesskit import cm, inch, mm, pt

width = cm(8) + mm(5)
print(width.twips, width.cm, width.format("in"))
```

`Length.parse("2.5cm")` accepts `tw`, `pt`, `mm`, `cm` and `in`. Arithmetic and comparisons work as
expected.


<!-- file: docs/guides/tables.md -->

# Tables

## Column types

| Access designer | PyAccessKit | Notes |
|---|---|---|
| Short Text | `Column.text(name, length=255)` | `allow_zero_length=False`, `unicode_compression=True`, `input_mask=` |
| Long Text | `Column.long_text(name, rich_text=False, append_only=False)` | |
| Number | `Column.number(name, size=NumberSize.LONG_INTEGER)` | Sizes: `BYTE`, `INTEGER` (16-bit), `LONG_INTEGER`, `SINGLE`, `DOUBLE`, `REPLICATION_ID`; `decimal_places=` |
| Number (Decimal) | `Column.decimal(name, precision=18, scale=0)` | Created with ADO DDL (DAO cannot create decimals) |
| Currency | `Column.currency(name)` | Exact, 4 decimal places |
| AutoNumber | `Column.autonumber(name, replication_id=False)` | Incrementing Long Integer, or a GUID (Replication ID) |
| Date/Time | `Column.date_time(name)` | |
| Yes/No | `Column.yes_no(name)` | Shown as a check box, as in Access |
| Hyperlink | `Column.hyperlink(name)` | |
| OLE Object | `Column.ole_object(name)` | |

Every column also accepts `required`, `default`, `validation_rule`, `validation_text`, `description`,
`caption`, `format`, and `properties={...}` (other Access field properties, set verbatim). Options that do
not apply to a type are rejected.

Columns PyAccessKit cannot create yet (Attachment, calculated, multi-valued, Large Number, Date/Time
Extended) still appear in `to_spec()`, as `UnsupportedColumn` entries that name the Access type, so
introspection never silently drops a field.

## Defaults and expressions

```python
from datetime import date
from pyaccesskit import Column, Expr

Column.text("Country", default="Belgium")  # rendered as "Belgium"
Column.text("Name", default='O"Brien')  # quotes are escaped for you
Column.currency("Total", default=0)
Column.yes_no("IsActive", default=True)
Column.date_time("Since", default=date(2026, 1, 1))
Column.date_time("CreatedAt", default=Expr("Now()"))  # an Access expression, passed verbatim
```

On Text columns, a string default is always a *value*: `default="Now()"` means the four characters
`Now()`. On other columns, a string must be a literal of the column's type (`"42"`, `"True"`,
`"#2026-01-31#"`). Anything else, such as `default="Now()"` on a Date/Time column, is rejected with a
hint to use `Expr(...)`, which prevents the classic unquoted-default bug.

## Keys and indexes

```python
from pyaccesskit import Column, IndexSpec

db.tables.create(
    "People",
    columns=[
        Column.autonumber("PersonID", primary_key=True),
        Column.text("LastName", length=80, indexed=True),  # non-unique index "LastName"
        Column.text("FirstName", length=80),
        Column.text("Email", length=255, unique=True),  # unique index "Email"
    ],
    indexes=[IndexSpec.on("ixFullName", "LastName", "FirstName")],
)

db.tables.create(
    "Enrollment",
    columns=[Column.number("PersonID"), Column.number("CourseID")],
    primary_key=["PersonID", "CourseID"],  # composite primary key, named PrimaryKey as in Access
)
```

`IndexSpec.on(name, *columns, unique=False, ignore_nulls=False, required=False)` accepts
`("Column", "desc")` pairs for descending keys. Limits are checked up front: 32 indexes per table,
10 columns per index, one primary key.

## Changing tables

```python
table = db.tables["People"]
table.add_column(Column.text("Phone", length=30))
table.rename_column("Phone", "Mobile")
table.drop_column("Mobile")
table.create_index(IndexSpec.on("ixEmailLast", "Email", "LastName", unique=True))
table.drop_index("ixEmailLast")
table.description = "Everyone we know"
table.rename("Contacts")
db.tables.drop("Contacts", drop_relationships=True)
```

Table creation is atomic: the table, its fields and its indexes are built and appended in one step. If
anything fails afterwards (for example, a property), the table is removed again. Changing a column's type
or order is not supported yet. It needs copy-and-migrate, which will come with `plan`/`apply`.

## Properties

Access stores many settings as DAO properties that only exist once set. `properties` bags read and write
them:

```python
from pyaccesskit import PropertyType

field = db.tables["People"].fields["Email"]
field.properties["Caption"] = "E-mail address"
db.properties["AppTitle"] = "My application"
db.tables["People"].properties.set(
    "MyTag", 7, PropertyType.BYTE
)  # force the DAO type of a new property
print(db.properties.get("StartUpForm"))  # None if not set
```

## Reading a table back

```python
table = db.tables["People"]
[field.name for field in table.fields]
table.fields["Email"].data_type, table.fields["Email"].size
table.primary_key, table.indexes
table.record_count()
spec = table.to_spec()  # canonical TableSpec; relationship-owned indexes are left out
```


<!-- file: docs/guides/relationships.md -->

# Relationships

```python
db.relationships.create("Customers.CustomerID", "Orders.CustomerID")
db.relationships.create(
    "Orders.OrderID",
    "OrderLines.OrderID",
    cascade_delete=True,
)
```

The first argument is the *primary* side (the "one" side, which needs a primary key or unique index on
those columns). The second is the *foreign* side (the "many" side).

## Options

| Option | Default | Meaning |
|---|---|---|
| `name` | primary table + foreign table (`CustomersOrders`), like Access | Relationship name |
| `enforce_integrity` | `True` | Enforce referential integrity (Access then adds a hidden index on the foreign side) |
| `cascade_update` | `False` | Cascade key updates to related rows |
| `cascade_delete` | `False` | Delete related rows along with the primary row |
| `one_to_one` | `False` | Declare a one-to-one relationship |
| `join` | `JoinType.INNER` | Default join type for the query designer (`INNER`, `LEFT`, `RIGHT`) |

## Composite keys

Pass `(table, [columns])` pairs, or build a `RelationshipSpec`:

```python
from pyaccesskit import RelationshipSpec

db.relationships.create(
    ("Enrollment", ["PersonID", "CourseID"]), ("Grades", ["PersonID", "CourseID"])
)

spec = RelationshipSpec.between("People.PersonID", "Enrollment.PersonID", cascade_delete=True)
db.relationships.create(spec)
```

## Checked before Access sees them

Before calling DAO, PyAccessKit verifies that:

- both tables and all columns exist, and the column counts match
- the column types are compatible (AutoNumber matches Long Integer, Replication ID matches a GUID number)
- the primary side has a primary key or unique index on exactly those columns
- the name is free, and the foreign table has index capacity left

Violations raise `RelationshipError` or `SpecError` with a readable explanation. If existing rows break the
rule you are enforcing, Access refuses the relationship. That surfaces as `IntegrityViolationError`.

## Reading and dropping

```python
for relationship in db.relationships:
    print(relationship.to_spec())
db.relationships.drop("CustomersOrders")
```

Tables involved in relationships cannot be dropped until the relationships are gone.
`db.tables.drop(name, drop_relationships=True)` removes them first.


<!-- file: docs/guides/queries.md -->

# Queries & data

## Saved queries

```python
db.queries.create("qryActive", "SELECT * FROM Customers WHERE IsActive = True;")
db.queries.create("qryActive", "SELECT * FROM Customers WHERE IsActive;", replace=True)

query = db.queries["qryActive"]
query.kind  # QueryKind.SELECT (also UNION, CROSSTAB, UPDATE, DELETE, APPEND, MAKE_TABLE, DDL, PASS_THROUGH…)
query.sql  # the SQL as Access stores it
query.sql = "SELECT CustomerName FROM Customers;"
query.parameters  # declared and implicit parameters
query.rename("qryCustomerNames")
query.drop()
```

Tables and queries share one namespace in Access. Creating a query named like a table raises
`ObjectExistsError`.

Access rewrites SQL when it saves a query: it adds brackets, normalizes whitespace and may reorder
clauses. `query.sql` returns what Access stored. Do not compare it character for character with what you
wrote.

## Running SQL

```python
count = db.execute(
    "UPDATE Orders SET Amount = Amount * [factor] WHERE CustomerID = [id]",
    {"factor": 1.1, "id": 42},
)
rows = db.fetch_all("SELECT * FROM Orders WHERE Amount > [minimum]", {"minimum": 100}, limit=50)
```

- **Parameters are bound by name.** Any `[name]` that is not a column becomes a parameter. Values are
  never interpolated into the SQL, so there is no injection and no quoting trouble. An unknown name in
  `params` raises `SpecError`. A missing value raises `MissingParameterError`.
- `execute` runs with DAO's `dbFailOnError`: a statement that fails part-way raises instead of silently
  applying half its changes.
- `fetch_all` returns a list of dictionaries. Dates come back as naive `datetime`, currency as `Decimal`,
  and Null as `None`.

Saved queries run the same way:

```python
db.queries["qryRaisePrices"].execute({"factor": 1.05})
db.queries["qryOrdersSince"].fetch({"since": date(2026, 1, 1)}, limit=10)
```

This data access is deliberately minimal: enough for seed data, verification and tests. For heavy data
work, use a DB-API driver such as `pyodbc` with the Access ODBC driver.

## Pass-through queries

```python
db.queries.create_pass_through(
    "qryServerVersion",
    "SELECT @@VERSION",
    connect="ODBC;Driver={ODBC Driver 18 for SQL Server};Server=db01;Trusted_Connection=Yes;",
    returns_records=True,
    timeout=30,
)
```

`pyaccesskit inspect` hides passwords (`PWD=…`) in connection strings when it prints them.


<!-- file: docs/guides/forms.md -->

# Forms

!!! note "Provisional"
    The form API is small on purpose and may grow in 0.x releases. Building forms needs Microsoft Access
    (the full product, not the Runtime).

## Building a form

```python
from pyaccesskit import FormView, Vba, cm

with db.forms.create("frmCustomers", record_source="Customers", caption="Customers") as form:
    form.textbox("CustomerName", label="Customer name", width=cm(8))
    form.textbox("Email", width=cm(8))
    form.combobox(
        "CountryID",
        row_source="SELECT CountryID, CountryName FROM Countries ORDER BY CountryName",
        column_count=2,
        column_widths=[cm(0), cm(4)],  # hide the key column, as in Access
    )
    form.checkbox("IsActive")
    form.textbox(
        name="txtCreated", control_source="=Format([CreatedAt], 'Short Date')", locked=True
    )
    form.button("cmdClose", caption="Close", on_click=Vba("DoCmd.Close acForm, Me.Name"))
```

The form is saved when the `with` block ends normally. If the block raises, nothing is saved. Outside a
`with` block, call `form.save()` or `form.discard()`.

### Controls

| Method | Control | Highlights |
|---|---|---|
| `textbox(field, label=, control_source=, format=, enabled=, locked=, after_update=)` | Text box | Bound to `field`, or computed with `control_source="=..."` |
| `checkbox(field, label=, ...)` | Check box | |
| `combobox(field, row_source=, row_source_type=, bound_column=, column_count=, column_widths=, limit_to_list=)` | Combo box | Rows from a table, query, SQL or value list |
| `label(caption)` | Label | |
| `button(name, caption=, on_click=)` | Command button | |

Every control also accepts `name=`, `section=`, `at=(left, top)`, `width=`, `height=`, `visible=` and
`properties={...}`. Text boxes, check boxes and combo boxes get an attached label automatically. Its text
is the field name (or the control name for unbound controls), unless you pass `label="..."` or
`label=False`.

### Layout

Controls are placed for you, and lengths use [units](../concepts/specs.md#units):

- **Stacked** (default for single forms): a label on the left and the control on the right, one row per
  control.
- **Tabular** (default for continuous forms): labels in the form header, and controls side by side in one
  detail row.
- **Explicit**: pass `at=(cm(1), cm(2))` and a size to place a control yourself.

```python
with db.forms.create(
    "frmProducts",
    record_source="Products",
    default_view=FormView.CONTINUOUS,
) as form:
    form.textbox("ProductName", width=cm(6))
    form.textbox("UnitPrice", format="Currency", width=cm(3))
    form.checkbox("Discontinued")
```

Layouts are checked against Access's limits (22 inches per form width and section height) before
anything is built.

### Events and VBA

```python
with db.forms.create("frmOrders", record_source="Orders") as form:
    form.on_load(Vba('Me.Caption = "Orders (" & DCount("*", "Orders") & ")"'))
    form.textbox("Quantity", after_update=Vba("Me.Recalc"))
    form.button("cmdSave", caption="Save", on_click=Vba("DoCmd.RunCommand acCmdSaveRecord"))
    form.module_code("Private Function Helper() As Long\n    Helper = 1\nEnd Function\n")
```

Event procedures (`Form_Load`, `cmdSave_Click`…) are generated in the form's module, which starts with
`Option Compare Database` and `Option Explicit`.

### Form properties

`db.forms.create(name, **options)` accepts: `record_source`, `caption`, `default_view` (single, continuous,
datasheet, split), `layout`, `header`, `width`, `allow_additions`, `allow_edits`, `allow_deletions`,
`data_entry`, `navigation_buttons`, `record_selectors`, `dividing_lines`, `scroll_bars`, `auto_center`,
`pop_up`, `modal`, and `properties={...}` for anything else (set verbatim, like
`properties={"RecordsetType": 2}`).

## Replacing a form safely

`db.forms.create(name, replace=True)` rebuilds an existing form:

1. The new form is built under a temporary name, then saved.
2. The old form is renamed to a backup name, and the new one takes its place.
3. The backup is deleted. If anything fails, the original is restored.

Each build starts from a fresh form, so the 754 *lifetime* controls limit never accumulates.

## Forms as data

A form is described by a `FormSpec`, which can be stored as JSON and built later:

```python
spec = form_builder.to_spec()
text = spec.model_dump_json(indent=2)

from pyaccesskit import FormSpec

db.forms.build(FormSpec.model_validate_json(text), replace=True)
```

## Checking and reading forms

```python
form = db.forms["frmCustomers"]
form.check_opens()  # opens hidden in Form view and closes; raises if Access reports an error
for control in form.controls():
    print(control.name, control.kind, control.control_source, control.left, control.width)
text = form.export_text()  # SaveAsText, as UTF-8 text
form.rename("frmClients")
db.forms.drop("frmClients")
```

Turning arbitrary designer-made forms back into a `FormSpec` is not supported, because that would lose
information. Use [text export](text-io.md) for full fidelity.


<!-- file: docs/guides/modules.md -->

# VBA modules

```python
from pyaccesskit import ModuleKind

db.modules.create(
    "modMath",
    "Public Function Twice(ByVal x As Long) As Long\n    Twice = 2 * x\nEnd Function\n",
)
db.modules.create(
    "clsCounter",
    "Private mCount As Long\n\nPublic Sub Increment()\n    mCount = mCount + 1\nEnd Sub\n",
    kind=ModuleKind.CLASS,
)

module = db.modules["modMath"]
module.kind  # ModuleKind.STANDARD
print(module.code)  # the source, with LF line endings
module.code = module.code.replace("2 * x", "x + x")
module.rename("modArithmetic")
db.modules.drop("modArithmetic")
```

- `Option Compare Database` is added at the top if missing, as Access does. Everything else is stored
  exactly as given.
- `create(..., replace=True)` replaces an existing module. Without it, an existing name raises
  `ObjectExistsError`.
- VBA source is stored in the Windows ANSI code page (for example Windows-1252). Characters that cannot be
  represented there raise `SpecError` instead of turning into `?`.
- Code is not compiled on import. A syntax error surfaces when Access compiles the module, for example when
  a form that uses it opens. Compile checks are planned for 0.2.
- Form and report modules belong to their form or report. Use the form's `on_load`, `module_code` and
  control events instead.

Modules need Microsoft Access (not the Runtime). Listing module names works with any engine:
`db.modules.names()`.


<!-- file: docs/guides/text-io.md -->

# Text import/export

Access can write most objects to a text file (`SaveAsText`) and read them back (`LoadFromText`).
PyAccessKit exposes this through `db.objects`, for forms, reports, macros, modules and queries.

```python
text = db.objects.export_text("form", "frmCustomers")  # str, LF line endings
db.objects.import_text("form", "frmCustomersCopy", text)

path = db.objects.save_text("module", "modMath", "src/modMath.bas")  # UTF-8 file
db.objects.load_text("module", "modMath", path, replace=True)

db.objects.names("macro")
db.objects.rename("report", "rptOld", "rptNew")
db.objects.delete("macro", "mcrUnused")
```

## Encodings

Access uses different encodings per object type, and gets confused if you mix them up:

| Kind | What Access reads and writes | What PyAccessKit gives you |
|---|---|---|
| Forms, reports, macros, queries | UTF-16LE with a byte-order mark | `str`, and UTF-8 files with LF line endings |
| Modules | The Windows ANSI code page | `str`, and UTF-8 files with LF line endings |

UTF-16 files are treated as binary by Git. PyAccessKit converts them for you, so exported objects diff
nicely. Class modules carry four `Attribute VB_...` header lines in their text form. Plain VB6-style `.cls`
files are accepted, and their extra header lines are dropped.

## When to use it

Text export is the **full-fidelity** way to move objects between databases, keep designer-made forms in
source control, or look at what Access really stores. It is not the main way to *build* objects: the text
format is undocumented, version-dependent and unvalidated, and `LoadFromText` overwrites without asking.
PyAccessKit therefore refuses to replace an existing object unless you pass `replace=True`.

Stripping volatile content (checksums, printer settings, GUIDs) for cleaner diffs is planned for the
source-control milestone.


<!-- file: docs/guides/recipes.md -->

# Recipes

Short, complete answers to common tasks. Each snippet assumes `from pathlib import Path` and the imports
it shows.

## Load rows from a CSV file

```python
import csv
from decimal import Decimal
from pathlib import Path

from pyaccesskit import AccessDatabase

with (
    AccessDatabase.open("shop.accdb") as db,
    Path("products.csv").open(newline="", encoding="utf-8") as f,
):
    for row in csv.DictReader(f):
        db.execute(
            "INSERT INTO Products (ProductName, UnitPrice) VALUES ([name], [price])",
            {"name": row["name"], "price": Decimal(row["price"])},
        )
```

Convert CSV strings to real Python types first (`Decimal`, `int`, `date.fromisoformat`...). Parameters keep
their Python type; passing the string `"12.50"` would leave the conversion to Access, which uses the
Windows locale (a decimal comma on many systems).

## Change an existing database safely and repeatably

Write changes so that running them twice does no harm, and keep a backup:

```python
import shutil

from pyaccesskit import AccessDatabase, Column, IndexSpec

shutil.copy2("app.accdb", "app.backup.accdb")
with AccessDatabase.open("app.accdb") as db:
    customers = db.tables["Customers"]
    if "Phone" not in customers.fields:
        customers.add_column(Column.text("Phone", length=30))
    if not any(index.name == "ixCity" for index in customers.indexes):
        customers.create_index(IndexSpec.on("ixCity", "City"))
    if "qryCustomersByCity" not in db.queries:
        db.queries.create(
            "qryCustomersByCity", "SELECT * FROM Customers ORDER BY City, CustomerName;"
        )
```

Changes to an opened database are applied one by one (they are not a single transaction), which is why the
backup matters. `AccessDatabase.create()` is the atomic path: rebuilding from specs is often simpler.

## Snapshot a schema as JSON and rebuild it

```python
import json
from pathlib import Path

from pyaccesskit import AccessDatabase, RelationshipSpec, TableSpec

with AccessDatabase.open("app.accdb", readonly=True) as db:
    snapshot = {
        "tables": [spec.model_dump(mode="json") for spec in db.tables.specs()],
        "relationships": [spec.model_dump(mode="json") for spec in db.relationships.specs()],
    }
Path("schema.json").write_text(json.dumps(snapshot, indent=2), encoding="utf-8")

data = json.loads(Path("schema.json").read_text(encoding="utf-8"))
with AccessDatabase.create("copy.accdb", overwrite=True) as db:
    for table in data["tables"]:
        db.tables.create(TableSpec.model_validate(table))
    for relationship in data["relationships"]:
        db.relationships.create(RelationshipSpec.model_validate(relationship))
```

## Export every object as text (for source control)

```python
from pathlib import Path

from pyaccesskit import AccessDatabase

out = Path("src-access")
with AccessDatabase.open("app.accdb") as db:
    for kind in ("form", "report", "macro", "module", "query"):
        folder = out / f"{kind}s"
        folder.mkdir(parents=True, exist_ok=True)
        for name in db.objects.names(kind):
            db.objects.save_text(kind, name, folder / f"{name}.txt")
```

Text export needs Microsoft Access (the session switches to a design session), so the database is opened
writable and exclusively.

## Copy forms and modules between databases

```python
from pyaccesskit import AccessDatabase

with AccessDatabase.open("template.accdb") as source:
    texts = {name: source.objects.export_text("form", name) for name in source.forms.names()}
with AccessDatabase.open("app.accdb") as target:
    for name, text in texts.items():
        target.objects.import_text("form", name, text, replace=True)
```

Forms reference tables and queries by name: create those in the target first.

## Lookup lists on a form

```python
from pyaccesskit import AccessDatabase, RowSourceType, cm

with (
    AccessDatabase.open("app.accdb") as db,
    db.forms.create("frmOrders", record_source="Orders", replace=True) as form,
):
    form.combobox(
        "CustomerID",
        label="Customer",
        row_source="SELECT CustomerID, CustomerName FROM Customers ORDER BY CustomerName",
        column_count=2,
        column_widths=[cm(0), cm(6)],  # store the ID, show the name
    )
    form.combobox(
        "Status", row_source='"Open";"Shipped";"Closed"', row_source_type=RowSourceType.VALUE_LIST
    )
```

## Watch Access while debugging

```python
from pyaccesskit import AccessDatabase, DialogPolicy, SessionOptions

debug = SessionOptions(visible=True, dialog_policy=DialogPolicy.WARN, call_timeout=None)
with AccessDatabase.open("app.accdb", engine="access", options=debug) as db:
    ...
```

With `visible=True` you see what Access does; `DialogPolicy.WARN` dismisses dialogs but only warns.

## Treat questionable names as errors

```python
import warnings

from pyaccesskit import AccessNameWarning

warnings.simplefilter("error", AccessNameWarning)  # "Name", "Date", spaces... now raise immediately
```

## Password-protected databases

```python
import os

from pyaccesskit import AccessDatabase

with AccessDatabase.open("secret.accdb", password=os.environ["APP_DB_PASSWORD"]) as db:
    print(db.tables.names())
```

Keep passwords out of source code; `pyaccesskit inspect` reads `PYACCESSKIT_PASSWORD`.

## Check the environment from code

```python
from pyaccesskit import diagnose

report = diagnose()
if not report.usable:
    raise SystemExit("\n".join(report.problems))
print("engine='auto' will use", report.auto_engine)
```


<!-- file: docs/reference/errors.md -->

# Errors

Every exception PyAccessKit raises derives from `PyAccessKitError`. Each carries:

- `str(exc)`: what PyAccessKit was doing, and why it failed, in plain words.
- `exc.operation`: the operation, for example `"create table 'Customers'"`.
- `exc.details`: `ErrorDetails` when Access or DAO reported the error. It holds the error number
  (`details.number`), source, description, HRESULT and the `DBEngine.Errors` entries. The original
  `com_error` is chained as `__cause__`.

Several exceptions also subclass a built-in exception, so generic code keeps working:
`DatabaseNotFoundError` is a `FileNotFoundError`, `DatabaseExistsError` is a `FileExistsError`,
`ObjectNotFoundError` is a `LookupError`, and `SpecError` is a `ValueError`.

## Hierarchy

```text
PyAccessKitError
├── EnvironmentProblem
│   └── EngineUnavailableError              .diagnosis explains what is missing
│       ├── AccessNotInstalledError
│       ├── DaoNotAvailableError            e.g. Python/Office bitness mismatch
│       └── AccessRuntimeOnlyError          design features need full Access
├── SessionError
│   ├── SessionClosedError                  used after close(), or a revoked db.raw object
│   ├── WrongThreadError                    used from another thread
│   ├── CapabilityError                     feature impossible with this engine / read-only session
│   └── ReadOnlyError                       write in a readonly=True session
├── DatabaseError                           .path
│   ├── DatabaseNotFoundError
│   ├── DatabaseExistsError
│   ├── DatabaseLockedError
│   ├── InvalidPasswordError
│   └── UnrecognizedFormatError
├── ObjectError                             .kind, .name
│   ├── ObjectNotFoundError
│   ├── ObjectExistsError
│   └── ObjectInUseError
├── SpecError                               .problems: every validation problem
├── SchemaError
│   ├── RelationshipError
│   └── IntegrityViolationError
├── QueryError                              .sql
│   ├── SqlSyntaxError
│   └── MissingParameterError
├── AccessApplicationError
│   ├── AccessDialogError                   .dialogs: title, text, buttons, action taken
│   ├── AccessTimeoutError
│   └── AccessProcessDiedError
├── CleanupError                            .errors: everything that failed while closing
└── ComError                                an Access/DAO error PyAccessKit has no specific class for
```

Warnings: `AccessNameWarning` for a legal but troublesome name, and `AccessDialogWarning` when
`dialog_policy="warn"` dismisses a dialog.

## Common error numbers

| Number | Meaning | Raised as |
|---|---|---|
| 3010, 3012 | Object already exists | `ObjectExistsError` |
| 3265, 2102, 7874 | Item not found | `ObjectNotFoundError` |
| 3024, 3044 | Database file or path not found | `DatabaseNotFoundError` |
| 3204, 7865 | Database file already exists | `DatabaseExistsError` |
| 3045, 3356, 3734, 7866 | Database in use or locked | `DatabaseLockedError` |
| 3031 | Wrong password | `InvalidPasswordError` |
| 3343 | Not an Access database | `UnrecognizedFormatError` |
| 3075, 3129, 3131, 3134, 3141, 3144 | SQL syntax error | `SqlSyntaxError` |
| 3061 | Too few parameters (unknown name in SQL) | `MissingParameterError` |
| 3022 | Duplicate value in a unique index | `IntegrityViolationError` |
| 3200 | Record cannot be deleted: related records exist | `IntegrityViolationError` |
| 3201 | Related record required | `IntegrityViolationError` |
| 3366, 3368, 3609 | Invalid relationship (field count or types, no unique index) | `RelationshipError` |
| 3085 | Undefined function in expression (`Nz` or a VBA function in DAO SQL) | `ComError` |
| 7960 | VBA did not compile | `ComError` |

## Cleanup errors never hide your error

If an exception is already propagating when the session closes, cleanup problems are added to it as
notes (`exc.__notes__`), and the original exception is the one you see. Only a normal close raises
`CleanupError`.

```python
from pyaccesskit import AccessDatabase, IntegrityViolationError, ObjectExistsError, SpecError

try:
    with AccessDatabase.open("app.accdb") as db:
        db.execute("INSERT INTO Orders (CustomerID) VALUES (999)")
except IntegrityViolationError as exc:
    print("fix the data:", exc)
except (ObjectExistsError, SpecError) as exc:
    print("fix the code:", exc)
```


<!-- file: docs/reference/data-types.md -->

# Data types

How Access field types map to PyAccessKit column specs, DAO, and Python values.

| Access designer | Column spec (`type` in JSON) | DAO type | Python value in / out |
|---|---|---|---|
| Short Text | `Column.text` (`"text"`) | `dbText` (10) | `str` |
| Long Text | `Column.long_text` (`"long_text"`) | `dbMemo` (12) | `str` |
| Hyperlink | `Column.hyperlink` (`"hyperlink"`) | `dbMemo` + hyperlink flag | `str` (`display#address#`) |
| Number: Byte | `Column.number(size="byte")` | `dbByte` (2) | `int` 0–255 |
| Number: Integer | `Column.number(size="integer")` | `dbInteger` (3) | `int`, 16-bit |
| Number: Long Integer | `Column.number()` (default) | `dbLong` (4) | `int`, 32-bit |
| Number: Single | `Column.number(size="single")` | `dbSingle` (6) | `float` |
| Number: Double | `Column.number(size="double")` | `dbDouble` (7) | `float` |
| Number: Replication ID | `Column.number(size="replication_id")` | `dbGUID` (15) | `str`, DAO's form `"{guid {…}}"` |
| Number: Decimal | `Column.decimal(precision, scale)` (`"decimal"`) | `dbDecimal` (20), created with ADO | `Decimal` |
| Currency | `Column.currency` (`"currency"`) | `dbCurrency` (5) | `Decimal` (4 decimals) |
| AutoNumber | `Column.autonumber()` (`"autonumber"`) | `dbLong` + auto-increment | `int` (assigned by Access) |
| AutoNumber (Replication ID) | `Column.autonumber(replication_id=True)` | `dbGUID` + `GenGUID()` default | `str`, DAO's form `"{guid {…}}"` |
| Date/Time | `Column.date_time` (`"date_time"`) | `dbDate` (8) | naive `datetime`; `date` and `time` are accepted as input (a time reads back on 1899-12-30, Access's day zero) |
| Yes/No | `Column.yes_no` (`"yes_no"`) | `dbBoolean` (1) | `bool` |
| OLE Object | `Column.ole_object` (`"ole_object"`) | `dbLongBinary` (11) | `bytes` (`bytearray`/`memoryview` accepted) |
| Attachment, Calculated, multi-value/lookup, Large Number, Date/Time Extended | read only: `UnsupportedColumn` (`"unsupported"`) | various | — |

## Choosing types

- **Keys:** `Column.autonumber("XID", primary_key=True)` on the parent, `Column.number("XID")` (Long
  Integer) on the child. The types must match for a relationship.
- **Money:** `Column.currency` (exact, 4 decimals) or `Column.decimal(precision, scale)`. Do not use
  `double` for money.
- **Flags:** `Column.yes_no`. Access stores Yes as -1; PyAccessKit gives you `True`/`False`.
- **Long text:** `Column.long_text`. Short Text is limited to 255 characters.
- **Dates:** Access has no time zones. PyAccessKit writes and reads the wall-clock value of naive
  datetimes. Convert aware datetimes to local time yourself if that matters.

## Defaults

| Column | Literal defaults | Expression defaults |
|---|---|---|
| text, long_text, hyperlink | any `str` (stored as a quoted literal) | `Expr("...")` |
| number | `int` (`float` for single/double) | `Expr("...")` |
| decimal, currency | `int`, `Decimal`, `float` | `Expr("...")` |
| date_time | `date`, `datetime`, `time` | `Expr("Now()")`, `Expr("Date()")` |
| yes_no | `True` / `False` | — |
| autonumber, ole_object | not allowed | not allowed |

## Binary parameters

`bytes` values can be passed as parameters to `db.execute`/`db.fetch_all`; PyAccessKit declares those
parameters as `LongBinary` for you. A **saved** query must declare them itself, because DAO types undeclared
parameters as text, which would corrupt the bytes:

```sql
PARAMETERS [payload] LongBinary;
INSERT INTO Files (Payload) VALUES ([payload]);
```

Passing bytes to an undeclared parameter of a saved query raises `SpecError` instead of storing damaged data.


<!-- file: docs/reference/cli.md -->

# Command line

```console
pyaccesskit [--version] COMMAND [OPTIONS]
python -m pyaccesskit COMMAND [OPTIONS]
```

Exit codes: `0` success, `1` error, `2` usage error, `3` environment unusable. With `--json`, output is
ASCII-safe JSON on stdout; errors go to stderr.

## `doctor`

```console
pyaccesskit doctor [--json] [--probe]
```

Reports Python, Windows, pywin32 and Access versions and bitness, and whether in-process DAO and
Microsoft Access can be used. It also shows what `engine="auto"` will choose and any orphaned Access
processes. `--probe` builds a scratch database with each available engine, starting and closing an owned,
hidden Access. The exit code is 3 when nothing is usable or a probe failed.

JSON fields: `usable`, `auto_engine`, `engines[{engine, available, detail}]`,
`access{version, bits, executable, click_to_run, products}`, `ace_oledb`, `probes[{engine, ok, seconds,
detail}]`, `owned_processes`, `problems`, `notes`, plus version facts.

## `inspect`

```console
pyaccesskit inspect DATABASE [--json] [--counts] [--password PW] [--engine auto|dao|access]
```

Describes tables (columns, types, defaults, indexes), relationships, queries (kind and SQL), and the names
of forms, reports, macros and modules. It opens the database read-only in shared mode, so it works while
the file is open in Access. It never runs startup code. `--counts` adds row counts. The password can also
come from `PYACCESSKIT_PASSWORD`. Passwords inside connection strings are shown as `***`.

JSON: `{path, format_version, transport, tables[{name, linked, rows, spec}], relationships[...],
queries[{kind, name, sql, description, pass_through}], forms, reports, macros, modules, warnings}`. Each
`tables[].spec` is a `TableSpec` in JSON form (`TableSpec.model_validate(entry["spec"])`).

## `cleanup`

```console
pyaccesskit cleanup [--dry-run] [--json]
```

Ends Access processes started by PyAccessKit sessions whose Python process has exited, usually after a
crash. Only processes recorded in PyAccessKit's ownership ledger are considered, and their identity (PID,
creation time, executable) is re-checked first. Access windows you opened yourself are never touched.
The exit code is 1 if a process could not be ended.

## `guide`

```console
pyaccesskit guide [--path]
```

Prints the [guide for AI coding agents](../agents/guide.md) that ships with the installed version, or its
file path with `--path`.

## `schema`

```console
pyaccesskit schema [all|table|column|index|relationship|query|form]
```

Prints the JSON Schema of the spec models. Editors and AI agents can use it to validate specs written as
JSON before touching Access.


<!-- file: docs/environment.md -->

# Environment & troubleshooting

## Supported environments

| | Supported | Notes |
|---|---|---|
| OS | Windows 10 22H2, Windows 11 | Windows Server: in-process DAO only (Microsoft does not support unattended Office automation) |
| Python | CPython 3.11–3.14, 64- and 32-bit | Free-threaded builds are not supported |
| Access | Microsoft 365, 2024, 2021, 2019, 2016 (all version 16.0) | 2013/2010 may work but are untested |
| Access Runtime | In-process DAO (bitness-matched) and Access-hosted DAO | The Runtime cannot design forms or modules |
| File formats | Create `.accdb`; open `.accdb` and `.mdb`; inspect `.accde`/`.mde` | `.adp` is not supported |

The spec models import on any OS. Only opening databases needs Windows and Access or DAO.

## `pyaccesskit doctor`

```console
$ pyaccesskit doctor
PyAccessKit 0.1.0 environment report

Python     3.12.4 (64-bit)  C:\Python312\python.exe
Windows    Windows-11-10.0.26100-SP0
pywin32    311
Access     16.0.19127.20264 (32-bit, Click-to-Run: O365ProPlusRetail)
           C:\Program Files (x86)\Microsoft Office\Root\Office16\MSACCESS.EXE
ACE OLEDB  none for this bitness

Engines
  in-process DAO    unavailable  DAO (Access database engine) is installed for 32-bit programs only, ...
  Microsoft Access  available    Access 16.0.19127.20264, 32-bit
  engine='auto' selects: Microsoft Access
...
```

`doctor` changes nothing: it reads the registry and file headers and tries to load DAO. `--probe`
additionally builds a scratch database with each available engine, in an owned Access instance that is
closed afterwards. `--json` prints the same report for scripts. The exit code is 3 when nothing is usable.

## Common situations

**"DAO is installed for 32-bit programs only, but this Python is 64-bit".** This is normal with Microsoft
365, which is often 32-bit. PyAccessKit then reaches DAO through Access, which works but starts
`MSACCESS.EXE`. For in-process DAO, use a Python of Office's bitness, for example
`uv python install cpython-3.12-windows-x86`.

**`AccessRuntimeOnlyError`.** Only the Access Runtime is installed. Tables, relationships, queries and
data work, but forms, modules and text import/export need the full product.

**`DatabaseLockedError`.** Another process has the database open exclusively, or a stale `.laccdb` lock
file belongs to a crashed process. Close Access, or open read-only (`AccessDatabase.open(path,
readonly=True)` works while the database is open elsewhere).

**`AccessDialogError`.** Access showed a modal dialog in the middle of an operation. The error contains the
dialog's title, text and buttons. Common causes are VBA compile errors in a form's module, broken
references, and startup code of the database (it is disabled by default; see `macro_security`).

**`AccessTimeoutError`.** One operation exceeded `SessionOptions.call_timeout`. The owned Access process
was terminated so your program could continue.

**Leftover `MSACCESS.EXE` after a crash.** Normally Windows ends it with your Python process (job object).
If that was not possible, run `pyaccesskit cleanup --dry-run` to see what PyAccessKit started, and
`pyaccesskit cleanup` to end processes whose Python owner is gone. Access instances you started yourself
are never touched.

**Databases in OneDrive or other synced folders.** Sync clients can lock or copy the file while Access has
it open, which risks corruption. Build into a local folder and copy the finished file.

**Running unattended (CI, services).** Microsoft does not support Office automation from services or
server-side code, because Office may show UI at any time. PyAccessKit's watchdog makes this far more robust,
but run Access-based jobs in an interactive user session (for example a self-hosted runner logged in as a
user). In-process DAO has no such restriction.


<!-- file: docs/faq.md -->

# FAQ

**Does PyAccessKit need Microsoft Access?**
For tables, relationships, queries and data, you need either Microsoft Access or the Access Runtime with a
Python of the same bitness. For forms, VBA modules and text import/export, you need full Microsoft Access.
`pyaccesskit doctor` tells you what works on your machine.

**Will it close the Access window I'm working in?**
No. PyAccessKit always starts its own hidden Access instance and never attaches to one that is running. It
ends only processes it started, identified by PID, creation time and executable.

**What happens if my script crashes?**
The Access process PyAccessKit started is in a Windows job object that ends it together with Python.
For a new database built with `create()`, the half-built file is discarded. If something is ever left
over, `pyaccesskit cleanup` ends it.

**Why is my table named `Name`/`Date`/`Order` flagged?**
They are Access reserved words or built-in property names. They work only when every SQL statement and
expression writes them in brackets, and they clash with form properties. Use `CustomerName`, `OrderDate`,
`SortOrder`… The warning points at the line of your code that used the name.

**My query works in Access but fails from Python with "Undefined function".**
Queries that Python runs go through DAO outside the Access user interface. There, Access-application
functions such as `Nz()`, and your own VBA functions, do not exist. Use engine functions: `IIf(IsNull(x), 0,
x)` instead of `Nz(x, 0)`. Forms can still use `Nz` and VBA functions.

**Can I build reports, subforms or ribbons?**
Not yet: see the [roadmap](index.md#roadmap). You can import report and macro text exported from Access
(`db.objects.import_text`), or use `db.raw.access` for one-off automation.

**How do I change a column's type?**
Access can't change a column type in place through DAO without risking data loss. Create a new column (or
table), copy the data with an `UPDATE` or `INSERT INTO … SELECT`, then drop the old one. Automated migrations
are planned with `plan`/`apply`.

**Does it work with `.mdb` files?**
You can open, inspect and change them. New databases are always `.accdb`.

**Can I use it from several threads?**
One session per thread. COM objects belong to the thread that created them. For parallel work, use
separate processes, each with its own session and file.

**Can AI coding assistants use PyAccessKit?**
Yes, and the library is designed for it. See [Building with AI agents](agents/index.md).
