# Database Guidelines (Access, SQLite, SQL Server, Postgres)

1. **MS Access (.mdb / .accdb):**
   - Use correct date literals (`#YYYY-MM-DD#`).
   - Escape field names with square brackets (e.g., `[Order Date]`).
   - **Concurrency (web apps with multiple ASP sessions writing to the same file):**
     - Never hold a connection open across requests (no connection cached in `Session`/`Application` scope) — acquire it at the start of the request and close it at the end, so locks are held for the shortest possible time. See `.devfoundry/references/asp-classic-framework.md` for how the RouteKit `DAL()`/`DatabaseConnection()` pair already does this per-request.
     - Wrap multi-statement writes in a transaction (`BeginTransaction`/`CommitTransaction`/`RollbackTransaction`) so a lock conflict can't leave a partial write.
     - On a write that fails with an Access lock-conflict error (e.g. `3260` "Could not update; currently locked", `3197`, `3218` "Couldn't update; currently locked by another session"), retry with a short backoff (e.g. 3 attempts, increasing delay) before surfacing the error — do not retry silently forever.
     - Run structural/batch/migration operations (`ALTER TABLE`, bulk `UPDATE`/`DELETE`, `Migrator_Class` runs) during a maintenance window with no concurrent user traffic; table-level locking during DDL can starve concurrent requests even with retries.
2. **Cross-Engine Dialect Awareness:**
   - Access: `TOP n`, `IIF()`, string concatenation with `&`
   - SQLite: `LIMIT n`, `IFNULL()`, string concatenation with `||`
   - SQL Server: `TOP (n)`, `ISNULL()`, `CONCAT()`
   - Postgres: `LIMIT n`, `COALESCE()`, `||`, boolean literals (`TRUE`/`FALSE`)
3. **Parameterization:** Always use parameterized queries regardless of engine.
4. **Migrations:** Every schema change must include a rollback/backward-compatibility note in the spec.