# AnniesApp → SQL Express (linked tables)

AnniesApp keeps **SQL Express connection settings inside the front-end** (`UsysSqlConfig` local table). It does **not** read SyncEngine `.env` at runtime.

| Component | Config | Purpose |
|-----------|--------|---------|
| **SyncEngine** | Repo `.env` (`SQL_EXPRESS_*`) | Mirror backend `.mdb` → SQL Express; publish to MySQL |
| **AnniesApp** | Local `UsysSqlConfig` in `AnniesApp.mdb` | ODBC linked tables → SQL Express |

## UsysSqlConfig (local table, single row)

Windows Authentication only — no SQL username/password in the app.

Operators configure **server, database, and schema only**. The ODBC driver version is **auto-selected** at connect time (not typed in).

| Column | Type | Example | Notes |
|--------|------|---------|-------|
| `strServer` | Text(128) | `DESKTOP-5OUND46\SQLEXPRESS` | Required |
| `strDatabase` | Text(128) | `AnniesAngels` | Required |
| `strSchema` | Text(64) | `dbo` | Default `dbo` |
| `strOdbcDriver` | Text(128) | *(optional)* | Last detected driver for diagnostics only; **ignored** when building connect strings |
| `dtmUpdated` | Date/Time | auto | |

DDL (created by `EnsureUsysSqlConfigTable` in `mdSqlExpress`):

```sql
CREATE TABLE [UsysSqlConfig] (
  strServer TEXT(128),
  strDatabase TEXT(128),
  strSchema TEXT(64),
  strOdbcDriver TEXT(128),
  dtmUpdated DATETIME
)
```

### Auto ODBC driver selection

`ResolveSqlOdbcDriver` (VBA) and [`scripts/Resolve-SqlOdbcDriver.ps1`](../scripts/Resolve-SqlOdbcDriver.ps1) pick the first installed driver from:

1. `ODBC Driver 18 for SQL Server`
2. `ODBC Driver 17 for SQL Server`
3. `ODBC Driver 13 for SQL Server`
4. `SQL Server Native Client 11.0`
5. `SQL Server` (legacy)

Discovery uses the ODBC registry hive for the **current process bitness** (32-bit Access / SysWOW64 scripts → 32-bit drivers). At least one of the above must be installed for Access’s bitness; there is no version field to fill in the app.

### Source of truth vs linked-table Connect

Access **requires** each linked `TableDef` to store its own `Connect` string — forms never read `UsysSqlConfig` directly. That Connect value is treated as a **cache**:

| Event | Behavior |
|-------|----------|
| **Save** on SQL Express Connection | Writes `UsysSqlConfig`, then rewrites every SQL-linked table’s `Connect` from config + auto driver |
| **Test Connection** | ADO test from config; on success, same full rewrite of linked tables |
| **Switchboard open** | Quiet sync if Connect drifted (e.g. different PC / driver) |

Do not hand-edit connect strings in Linked Table Manager — change server/database on the SQL Express form and Save.

Connect string pattern (driver name filled in automatically):

```
ODBC;DRIVER={…auto…};SERVER=...;DATABASE=...;Trusted_Connection=Yes;TrustServerCertificate=Yes
```

No System DSN required. SyncEngine uses ADO.NET SqlClient via `.env` — separate from AnniesApp ODBC.
## VBA (version control)

| File | Purpose |
|------|---------|
| `access/AnniesApp/vba/mdSqlExpress.bas` | Connect string, test, link, apply |
| `access/AnniesApp/vba/Form_frmSystem.bas` | Form code to merge into existing `frmSystem` |

### mdSqlExpress public API

| Procedure | Purpose |
|-----------|---------|
| `GetSqlOdbcConnectString()` | DSN-less ODBC string (auto driver + `UsysSqlConfig` server/db) |
| `ResolveSqlOdbcDriver()` | Best installed SQL Server ODBC driver for this Access bitness |
| `GetSqlConfig` / `SaveSqlConfig` | Read/write config row (driver column diagnostics-only) |
| `TestSqlConnection()` | ADODB test; on success rewrites all linked-table Connect strings |
| `PropagateSqlConfigToLinkedTables()` | After Save: rewrite all linked tables from config (MsgBox if updated/failed) |
| `ApplySqlLinkedTables()` / `ApplySqlLinkedTablesQuiet()` | Drift-aware `RefreshLink` from canonical connect string |
| `SyncSqlLinksOnStartup()` | Quiet apply from Switchboard `Form_Open` |
| `LinkSqlTables()` | First-time link all SQL Express tables |
| `InitSqlExpressConfig()` | Create table + seed defaults |
## Phase 3 — Add config to AnniesApp.mdb (in Access)

1. Open `AnniesApp.mdb` (database password **393939**; Setup menu uses level-2 password **5902** via `CheckLevel2Access`).
2. VBA Editor → **File → Import** → `access/AnniesApp/vba/mdSqlExpress.bas` (replace existing module if prompted).
3. Immediate window: `InitSqlExpressConfig` (first time only).

**Menu:** **Setup → System Setup → SQL Express Connection**

- Added to `Switchboard Items` (SwitchboardID `2`, ItemNumber `6`).
- Runs `OpenSqlExpressSetup`, which creates `frmSqlExpress` on first use and opens the SQL config form.

To apply the menu item from the repo (close AnniesApp first):

```powershell
powershell -ExecutionPolicy Bypass -File scripts\Apply-SqlExpressMenu.ps1
```

Or from the Immediate window while the app is open (after importing latest `mdSqlExpress.bas`):

```vba
AddSqlExpressSwitchboardItem
```

### frmSqlExpress controls

| Control | Name | Notes |
|---------|------|-------|
| Text box | `txtDescription` / server | SQL instance (`strServer`) |
| Text box | `txtSqlDatabase` | e.g. `AnniesAngels` |
| Text box | `txtSqlSchema` | default `dbo` |
| Button | `btnSqlTest` | Test Connection (reports auto-selected driver) |
| Button | `btnSqlSave` | Save config and close |
| Button | `btnSqlCancel` | Discard and close |

ODBC driver is **not** on the form (auto-selected). Legacy `txtSqlOdbcDriver` / `lblDriver` controls are hidden if present.

**Not on this form:** Link All / Apply — use [`scripts/Link-SqlExpressTables.ps1`](../scripts/Link-SqlExpressTables.ps1) and SyncEngine instead.

Created automatically the first time you open **SQL Express Connection** from the menu.

### Optional: extend frmSystem

The existing **System** item under System Setup opens `frmSystem` (letterhead settings). SQL Express has its own menu item and form; you do not need to add controls to `frmSystem` unless you want everything on one form.

Legacy note — if merging into **frmSystem** instead, add controls in Design view:

No System DSN required. No `.env` involvement for AnniesApp.

## Local tables (do not link)

| Table | Reason |
|-------|--------|
| `UsysSqlConfig` | SQL settings (local only) |
| `Switchboard Items` | Switchboard (local only; do not duplicate as ODBC link) |
| `tblDeliveryNoteItemORIG` | Scratch/legacy if still present locally |
| `UsysDN` | Per-session edit flag for Delivery Notes (`DELETE` on form load) |
| `UsysIN` | Per-session edit flag for Invoices (`DELETE` on form load) |

`adn` and `adni` remain ODBC-linked business/staging tables. SyncEngine inclusion is controlled by the **live** `.env` value of `ACCESS_SQL_EXCLUDE_TABLES` (empty = all discovered tables sync).

### ODBC error 3197 (Delivery Notes)

Root cause when selecting a customer: `cmbCustomer_AfterUpdate` opened an updatable dynaset on `tblDeliveryNote` while the form was bound to the same SQL table. Fix applied via [`scripts/Fix-DnOdbcWrites.ps1`](scripts/Fix-DnOdbcWrites.ps1):

- `ApplyCustomerDeliveryNoteChange` in `mdDaoSql.bas` — snapshot read + `Execute UPDATE` by PK
- All DN form dynaset `Edit`/`Update` on linked tables replaced with snapshot + Execute
- `CalcDNTotal` is compute-only; invoice line sync runs in `Form_AfterUpdate` after save
- `RecordLocks = No Locks` on data entry forms

Relocalize session tables if they were linked to SQL:

```powershell
powershell -ExecutionPolicy Bypass -File scripts\Unlink-SessionTables.ps1
```

Verify:

```powershell
powershell -ExecutionPolicy Bypass -File scripts\Export-DnForms.ps1
```

If the form **freezes** on open or customer select, run [`scripts/Fix-DnFormFreeze.ps1`](scripts/Fix-DnFormFreeze.ps1) — removes `cmbSearch.Requery` from `Form_Current` (heavy ODBC JOIN on every row change) and uses a lightweight new-record path.

See [`access/AnniesApp/vba/_scan_DnDynasetEdit.txt`](../access/AnniesApp/vba/_scan_DnDynasetEdit.txt).

## Phase 4 — Inventory linked tables

```powershell
powershell -ExecutionPolicy Bypass -File scripts\Inventory-AnniesTables.ps1
```

Writes [`docs/anniesapp-table-inventory.md`](anniesapp-table-inventory.md): linked Access backend vs ODBC vs local tables.

## Phase 5 — Relink

1. **Backup** (automatic in link script): `scripts\Backup-AnniesApp.ps1`
2. Final sync from Access backend (SyncEngine, uses `.env` only):

   ```bat
   SyncEngine.exe access
   ```

3. **Link all tables** (close forms in Access first):

   ```powershell
   powershell -ExecutionPolicy Bypass -File scripts\Link-SqlExpressTables.ps1

4. Re-run inventory to confirm ODBC link counts.

## Phase 6 — Fix queries / VBA after relink

Export all saved queries:

```powershell
powershell -ExecutionPolicy Bypass -File scripts\Export-AnniesQueries.ps1
```

SQL lives under `access/AnniesApp/queries/`. Apply ODBC/SQL Server fixes:

```powershell
powershell -ExecutionPolicy Bypass -File scripts\Fix-SqlExpressQueries.ps1
```

Automated test (tables + queries):

```powershell
powershell -ExecutionPolicy Bypass -File scripts\Test-AnniesQueries.ps1
```

Writes [`docs/anniesapp-query-test.md`](anniesapp-query-test.md). Every saved query is executed — report queries get a live date substituted for `Forms!frmReport!txtDate`; action queries run for real (`UPDATE` / `INSERT`).

| Jet | SQL Server |
|-----|------------|
| `#1/1/2025#` | `'2025-01-01'` |
| `IIf(a,b,c)` | `IIF` or `CASE` |
| `Nz(x,0)` | `ISNULL(x,0)` |
| `UPDATE t1, t2 SET ...` | single-table UPDATE + subquery or JOIN |

See [access-sql-express-compatibility.md](access-sql-express-compatibility.md).

## Phase 7 — Smoke test

```powershell
powershell -ExecutionPolicy Bypass -File scripts\Test-AnniesQueries.ps1
```

Manual checks in Access:

- Products, Customers, Delivery Notes, Invoices — browse and edit
- Run daily turnover / DN-not-invoiced reports from switchboard (with report date)
- Confirm changes in SSMS on `AnniesAngels`

## Phase 8 — SyncEngine cutover (`.env` only)

After AnniesApp runs reliably against SQL Express, set in `.env`:

```env
SYNC_ACCESS_TO_SQL_EXPRESS=false
SYNC_SQL_EXPRESS_TO_MYSQL=true
```

**Applied:** Leg 1 disabled — do not run `SyncEngine.exe access` or `all` (would overwrite SQL Express from stale `.mdb`). Schedule `SyncEngine.exe mysql` only.

## Changing server on a new PC

1. Install **any** Microsoft SQL Server ODBC driver matching **Access bitness** (x86 for 32-bit Access). Verify in the matching ODBC admin:
   - 32-bit: `%windir%\SysWOW64\odbcad32.exe`
   - 64-bit: `%windir%\System32\odbcad32.exe`
2. AnniesApp → **Setup → System Setup → SQL Express Connection** → set server / database / schema → **Save** (updates all linked-table Connect strings) or **Test Connection** (same rewrite after ADO OK).
3. If forms still fail with “There was an error executing the command”, the Switchboard is hiding an ODBC link error — Save/Test again after fixing network/ODBC, or run `scripts\Link-SqlExpressTables.ps1` (close Access first).
4. Update SyncEngine `.env` separately if the sync pipeline should use the same server.

### Troubleshooting: “Data source name not found and no default driver specified”

This means no preferred SQL Server ODBC driver is installed for Access’s bitness (not a missing System DSN — AnniesApp is DSN-less). Install the x86 (or x64) ODBC Driver package to match Access; do not type a driver name into the app. SyncEngine SqlClient succeeding does not prove Access ODBC is configured.