# AnniesApp → SQL Express (linked tables)

AnniesApp keeps **SQL Express connection settings inside the front-end** (`UsysSqlConfig` local table). Forms talk to SQL Express through ODBC linked tables.

## UsysSqlConfig (local table, single row)

Operators configure **server, database, schema, and auth** (Trusted Connection or SQL UID/PWD). The ODBC driver version is **auto-selected** at connect time (not typed in). Default auth is Windows / Trusted.

| 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 |
| `blnTrustedConnection` | Yes/No | `True` | Default `True`; when False use `strUid` / `strPwd`. Jet `ALTER` often defaults new Yes/No to **False** — `EnsureUsysSqlConfigTable` backfills True when no UID is set. |
| `strUid` | Text(64) | | SQL login when Trusted is off |
| `strPwd` | Text(64) | | SQL password when Trusted is off |
| `dtmUpdated` | Date/Time | auto | |

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

```sql
CREATE TABLE [UsysSqlConfig] (
  strServer TEXT(128),
  strDatabase TEXT(128),
  strSchema TEXT(64),
  strOdbcDriver TEXT(128),
  blnTrustedConnection YESNO,
  strUid TEXT(64),
  strPwd TEXT(64),
  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 | ADO test first; on OK, snapshot config + all ODBC Connects, save `UsysSqlConfig`, `RelinkSqlTables`; on any `RefreshLink` failure restore links **and** config |
| **Test Connection** | ADO test from form values only — does **not** rewrite linked tables |
| **Switchboard open** | Quiet: if ADO OK, rewrite drifted `TableDef.Connect` only — **no** `RefreshLink` (avoids Access ODBC dialogs on launch) |

Do not hand-edit connect strings in Linked Table Manager — change server/database/auth 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
```

SQL auth (Trusted off):

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

No System DSN required.

## VBA (version control)

| File | Purpose |
|------|---------|
| `access/AnniesApp/vba/mdSqlExpress.bas` | Connect string, test, transactional relink, form builder |
| `access/AnniesApp/vba/Form_frmSqlExpress.bas` | Trusted/UID/PWD UI + Test/Save/Cancel |
| `access/AnniesApp/vba/Form_frmSystem.bas` | Legacy stubs (SQL Express lives on `frmSqlExpress`) |

### mdSqlExpress public API

| Procedure | Purpose |
|-----------|---------|
| `GetSqlOdbcConnectString()` | DSN-less ODBC string (auto driver + auth from `UsysSqlConfig`) |
| `ResolveSqlOdbcDriver()` | Best installed SQL Server ODBC driver for this Access bitness |
| `GetSqlConfig` / `SaveSqlConfig` | Read/write config (incl. trusted/uid/pwd; driver diagnostics-only) |
| `TestSqlConnection()` / form Test | ADODB test only — does not rewrite links |
| `RelinkSqlTables()` | Backup Connects → RefreshLink all → restore all on any failure |
| `PropagateSqlConfigToLinkedTables()` | Transactional relink from saved config |
| `ApplySqlLinkedTables()` / `ApplySqlLinkedTablesQuiet()` | Force = transactional relink; quiet False = drift-only |
| `SyncSqlLinksOnStartup()` | Quiet: ADO check then rewrite drifted Connect only (no RefreshLink) |
| `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` |
| Check box | `chkTrusted` | Trusted Connection (default on) |
| Text box | `txtSqlUid` / `txtSqlPwd` | Enabled only when Trusted is off |
| Button | `btnSqlTest` | ADO test only (reports auto-selected driver) |
| Button | `btnSqlSave` | Test → save → transactional relink → close |
| Button | `btnSqlCancel` | Discard and close |

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

Re-apply form + module (close AnniesApp first):

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

**Not on this form:** Link All / Apply — use [`scripts/Link-SqlExpressTables.ps1`](../../scripts/Link-SqlExpressTables.ps1) (reads trusted/uid/pwd).

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. Connection settings live in `UsysSqlConfig` only.

## 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.

### ODBC error 3197 (Delivery Notes / Quotes)

**Symptom:** save or move-next shows “This record has been changed by another user…” with Save greyed out (Copy/Cancel only). Bound edits do **not** persist.

**Evidence (live exports 2026-08-03):**

| Form | RecordSource | Form_AfterUpdate Execute on bound table? |
|------|--------------|------------------------------------------|
| `frmCategory` / `frmColour` | Explicit columns only | No — works |
| `frmDN` / `frmDN_With_Con` | Was `SELECT tblDeliveryNote.*` (included `_sync_*` + many `FLOAT`s) | Yes (`dblCommission`) — secondary risk |
| `frmQuote` / `frmQuote_Sub` | Was `SELECT tblQuote*.*` (included `_sync_*` + `FLOAT`s) | **No** AfterUpdate Execute |

Quote fails the same way **without** an AfterUpdate self-`Execute`, so the proximate bound-write failure is Access ODBC concurrency on a wide `SELECT *` row (sync metadata + floats), not “AfterUpdate before persist.” (`Form_AfterUpdate` only runs after a successful bound write.)

**Fix applied** via [`scripts/Fix-3197BoundRecordSource.ps1`](../../scripts/Fix-3197BoundRecordSource.ps1):

1. Narrow DN/Quote (and subform) `RecordSource` to business columns only — exclude `_sync_row_hash`, `_sync_is_deleted`, `_sync_last_seen_at`, `_sync_updated_at`.
2. Add SQL `DEFAULT ((0))` for nullable BIT flags used on those forms (`blnCanceled`, `blnLost`, `blnReplaced`).
3. DN: set `Me.dblCommission` in `Form_BeforeUpdate`; remove post-save `ExecuteSql UPDATE … dblCommission` from `Form_AfterUpdate` (keep search requery only).

Earlier customer-select 3197 (second updatable dynaset) remains addressed by [`scripts/Fix-DnOdbcWrites.ps1`](../../scripts/Fix-DnOdbcWrites.ps1) (snapshot + `Execute` by PK).

Relocalize session tables if they were linked to SQL:

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

Re-apply / verify:

```powershell
powershell -ExecutionPolicy Bypass -File scripts\Backup-AnniesApp.ps1 -Label pre_3197
powershell -ExecutionPolicy Bypass -File scripts\Fix-3197BoundRecordSource.ps1
powershell -ExecutionPolicy Bypass -File scripts\Export-DnQuoteCategoryForms.ps1
```

See [`access/AnniesApp/vba/_scan_3197_form_compare.txt`](../../access/AnniesApp/vba/_scan_3197_form_compare.txt), [`_scan_3197_sql_schema.txt`](../../access/AnniesApp/vba/_scan_3197_sql_schema.txt), [`_3197_event_order_result.txt`](../../access/AnniesApp/vba/_3197_event_order_result.txt).

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. Prefer [`scripts/Fix-DnLoadFlash.ps1`](../../scripts/Fix-DnLoadFlash.ps1) after a 3197 RecordSource pass (see below) so BeforeUpdate commission changes are not overwritten.

### ODBC error 3197 (Payments)

**Symptom:** same write-conflict dialog on `frmPayment` (Invoice → Payment). Bound `RecordSource` was the table name `tblPayment` (equivalent to `SELECT *`), so the row included `_sync_*` plus `FLOAT dblAmount`.

**Evidence (2026-08-13):**

| Form | RecordSource before | Form_AfterUpdate Execute? |
|------|---------------------|---------------------------|
| `frmPayment` | `tblPayment` (includes `_sync_*` + `dblAmount` float) | No |
| `frmPaymentType` | Explicit `PaymentTypeID, strDescription` | No — already OK |

`tblPayment.blnCanceled` already had `DEFAULT ((0))` and zero nulls. No AfterUpdate self-`Execute` (same as Quote).

**Fix applied** via [`scripts/Fix-Payment3197.ps1`](../../scripts/Fix-Payment3197.ps1):

1. Narrow `frmPayment.RecordSource` to business columns only: `PaymentID, refInvoiceID, refCustomerID, refPaymentTypeID, strDate, dblAmount, strChequeNr, blnCanceled`.
2. Keep `RecordLocks = No Locks`; clear a stale table-qualified `Filter`.
3. Idempotent SQL `DEFAULT ((0))` for `blnCanceled` if missing.

Do **not** run [`scripts/Fix-3197BoundRecordSource.ps1`](../../scripts/Fix-3197BoundRecordSource.ps1) for this — that script also rewrites DN commission VBA.

Re-apply / verify (close AnniesApp first):

```powershell
powershell -ExecutionPolicy Bypass -File scripts\Fix-Payment3197.ps1
powershell -ExecutionPolicy Bypass -File scripts\Dig-Payment3197.ps1
powershell -ExecutionPolicy Bypass -File scripts\Test-Payment3197PersistSmoke.ps1
```

See [`access/AnniesApp/vba/_scan_payment_3197_form_compare.txt`](../../access/AnniesApp/vba/_scan_payment_3197_form_compare.txt), [`_scan_payment_3197_sql_schema.txt`](../../access/AnniesApp/vba/_scan_payment_3197_sql_schema.txt), [`_payment_3197_persist_smoke.txt`](../../access/AnniesApp/vba/_payment_3197_persist_smoke.txt).

`frmInvoice` / `frmInvoice_Sub` still use `RecordSource = tblInvoice` / `tblInvoiceItem` (same class of bug; not part of this pass).

### Delivery Note load / clear / reload (post-3197)

After narrowing `RecordSource`, some DNs flashed blank then reloaded (or stayed blank) because `Form_Current` still did `Application.Echo False` + `cmbSearch.Requery` (full ODBC DN+customer JOIN) on every navigation. Search used `INNER JOIN` customer and subforms used `INNER JOIN` product/consumable, so orphan FKs disappeared from search/lines.

**Fix applied** via [`scripts/Fix-DnLoadFlash.ps1`](../../scripts/Fix-DnLoadFlash.ps1):

1. Remove `Echo` / `cmbSearch.Requery` from `Form_Current`; keep `inCurrent` + `On Error` cleanup; requery search only in `Form_AfterUpdate` (no Echo).
2. `cmbSearch.RowSource`: `LEFT JOIN` customer from `tblDeliveryNote`.
3. Keep `frmDN_Sub` / `frmDN_Sub_Con` on **INNER JOIN** product/consumable (LEFT JOIN on those ODBC subform `RecordSource`s can blank the linked line grid — restored via [`scripts/Fix-DnSubformInnerJoin.ps1`](../../scripts/Fix-DnSubformInnerJoin.ps1)).
4. `btnSearch_Click`: report `NoMatch` instead of silent fail.

```powershell
powershell -ExecutionPolicy Bypass -File scripts\Fix-DnLoadFlash.ps1
powershell -ExecutionPolicy Bypass -File scripts\Fix-DnSubformInnerJoin.ps1
powershell -ExecutionPolicy Bypass -File scripts\Export-DnQuoteCategoryForms.ps1
```

Note: newest DNs such as **64005** can legitimately have **zero** line items in SQL Express and the Access backend — the product grid stays empty until items exist. Use search for a DN that has items (e.g. 64002) when checking the subform.

### Delivery Note prices not loading (post-3197 regression)

After narrowing subform `RecordSource` to an explicit column list, money fields (daily rate / amount / replace / header totals) stopped painting reliably on ODBC. Pre-change backup used `SELECT tblDeliveryNoteItem.*, tblProduct.strCode ...`.

**Fix applied** via [`scripts/Fix-DnPricesRecordSource.ps1`](../../scripts/Fix-DnPricesRecordSource.ps1):

1. Keep `frmDN_Sub` / `frmDN_Sub_Con` on explicit **business columns** + INNER JOIN (do **not** use `SELECT *` — that pulls `_sync_*` back and causes Dirty/3197 popups on navigate). Parent header RecordSource stays narrowed.
2. Remove `Application.Echo False/True` from `CalcDNTotal`; `Form_Current` error path forces `Application.Echo True`.
3. Maps in `Fix-3197BoundRecordSource.ps1` / `Fix-DnLoadFlash.ps1` / `Fix-DnSubformInnerJoin.ps1` keep the same explicit subform shape.

### Delivery Note navigation blocked by popups

Restoring subform `SELECT *` made rows Dirty (ODBC + `_sync_*` / float compare). Moving record then ran `Form_BeforeUpdate` (overlap / PO MsgBoxes) or Access 3197.

**Fix applied** via [`scripts/Fix-DnNavPopups.ps1`](../../scripts/Fix-DnNavPopups.ps1):

1. Subform RecordSource again excludes `_sync_*`.
2. `CalcDNTotal` only writes `txtChequeAmount` when the value changes (avoids spurious Dirty).
3. Do **not** use `Me.Undo` on Current — that caused ODBC bookmark bounce (see below).

### Delivery Note multi-record bounce on open / select

SQL Express-era [`Fix-DnRecordLocks.ps1`](../../scripts/Fix-DnRecordLocks.ps1) deferred `GoToRecord acNewRec` via `TimerInterval = 50`, so open painted the first DN then jumped to new. Later `Me.Undo` in `Form_Current` jumped bookmarks when selecting one DN.

**Fix applied** via [`scripts/Fix-DnRecordBounce.ps1`](../../scripts/Fix-DnRecordBounce.ps1):

1. Restore original Jet open: `Form_Load` clears `UsysDN` then `DoCmd.GoToRecord acDataForm, Me.Name, acNewRec`; `TimerInterval = 0`; `Form_Timer` is a no-op.
2. Remove all `Me.Undo` from `Form_Current`.
3. Keep cheque change-guard; leave original `MySetForm` lock sequence.
4. `Fix-DnRecordLocks.ps1` no longer re-applies the Timer deferral.

```powershell
powershell -ExecutionPolicy Bypass -File scripts\Fix-DnRecordBounce.ps1
powershell -ExecutionPolicy Bypass -File scripts\Export-DnQuoteCategoryForms.ps1
```

Verify: open DN → land on new only (no oldest-DN flash); search **63998** → single jump.

### Delivery Note load / clear / load paint flash

Without `Application.Echo`, two remaining causes made the form look like it was clearing and reloading: (1) `MySetForm True` then `False` on every Current thrashing the subform, (2) `CalcDNTotal` assigning `txtSubtotal = 0` before recalculating, plus `SyncInvoiceItems` UPDATE on every navigation.

**Fix applied** via [`scripts/Fix-DnPaintFlash.ps1`](../../scripts/Fix-DnPaintFlash.ps1):

1. `Form_Open` → `GoToRecord acNewRec` before first Current; `Form_Load` only clears `UsysDN` (no Timer).
2. Single lock decision + `MySetForm` no-op if already in that state.
3. `CalcDNTotal` computes into locals and assigns unbound totals once; syncs invoice amounts only when they differ.

```powershell
powershell -ExecutionPolicy Bypass -File scripts\Fix-DnPaintFlash.ps1
powershell -ExecutionPolicy Bypass -File scripts\Export-DnQuoteCategoryForms.ps1
```

Verify with DN **63998** (SQL has non-zero rates/amounts; sum amount 112). DN **64002** can still show R 0,00 rates — those lines store zero `dblDailyRate`/`dblAmount` in SQL while replace values are populated.

```powershell
powershell -ExecutionPolicy Bypass -File scripts\Fix-DnPricesRecordSource.ps1
powershell -ExecutionPolicy Bypass -File scripts\Fix-DnNavPopups.ps1
powershell -ExecutionPolicy Bypass -File scripts\Fix-DnRecordBounce.ps1
powershell -ExecutionPolicy Bypass -File scripts\Export-DnQuoteCategoryForms.ps1
```

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 [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. **Link all tables** (close forms in Access first):

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

3. 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 [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`).

AnniesApp still runs **Jet SQL against ODBC-linked tables** (not T-SQL). Typical Jet/ODBC failures and the scripts that patch them:

| Symptom | Fix |
|---------|-----|
| Saved query `Nz` / join / date issues | [`scripts/Fix-SqlExpressQueries.ps1`](../../scripts/Fix-SqlExpressQueries.ps1) |
| Report Console `DELETE *`, `DateDiff`/`CDate`, stale Filters | [`scripts/Fix-AnniesReports.ps1`](../../scripts/Fix-AnniesReports.ps1) |

## Phase 7 — Smoke test

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

Query smoke test writes [anniesapp-query-test.md](anniesapp-query-test.md).

Report Console test writes [anniesapp-report-test.md](anniesapp-report-test.md). It opens `frmReport` (Unlock simulated), seeds each of the 28 options plus Product Tags from live SQL Express rows, previews the report, and requires HasData / pages / recordsource rows. Jet/ODBC report fixes live in [`scripts/Fix-AnniesReports.ps1`](../../scripts/Fix-AnniesReports.ps1) (`DELETE *` → pass-through `OdbcDeleteAll`, age-analysis `DateDiff`, stale Filters, staging-table PKs, Top N `InputBox` skipped when Access is automated).

Manual checks in Access:

- Products, Customers, Delivery Notes, Invoices — browse and edit
- Switchboard **Report** → Unlock (`5902`) → preview a few console reports (age analysis and DN by Job/Goods are slow on ODBC)
- Confirm rows in SSMS on the database named in `UsysSqlConfig` (typically `AnniesAngels`)

## 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 / auth → **Save** (ADO test first, then transactional relink). Use **Test Connection** to probe without changing links.
3. If forms still fail with “There was an error executing the command”, the Switchboard is hiding an ODBC link error — **Save** again after fixing network/ODBC, or run `scripts\Link-SqlExpressTables.ps1` (close Access first).

### 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.