# Root

This codebase is an Access VBA application that implements a **financial transaction reconciliation pipeline**. It ingests internal transactions and bank statements from Excel files, matches them against each other using a multi-tier matching strategy, and exports the results as a color-coded Excel report.

There are no source files indexed at the top level — all implementation lives under the `vba-source` sub-module.

## Overview

The system exists to help financial operators reconcile internal transaction records against bank statement data. A user opens the Access frontend, browses to an Excel file (either internal transactions or a bank statement), imports the data with a single click, runs a reconciliation pass, and produces a final report showing matched, unmatched, and duplicate transactions with their variances.

The entire logic is implemented in Access VBA modules and a single class, without any external framework dependencies. It uses DAO for database access and Excel's COM automation for file I/O.

## Sub-module Guide

### vba-source

The `vba-source` module contains the full reconciliation pipeline logic. It is organized into five VBA units that each have a distinct responsibility:

| Unit | Type | Responsibility |
|---|---|---|
| `modImport` | Standard module | File browsing, Excel parsing, row validation, database insertion |
| `modReconcile` | Standard module | Multi-tier matching (exact, fuzzy, unmatched) between transactions and bank statements |
| `modExport` | Standard module | Excel report generation with variance highlighting |
| `modUtil` | Standard module | Shared utilities — batch numbering, locale-safe SQL formatting, error logging |
| `clsTransaction` | Class module | Transaction data model with built-in validation |

These units work together as a three-phase pipeline: Import → Reconcile → Export. Each phase reads from or writes to a small set of Access tables, and `modUtil` provides shared helpers across all phases.

See the [vba-source module wiki](vba-source.md) for complete unit-level documentation.

## Architecture

The system follows a classic **three-phase batch pipeline** pattern with shared utilities:

```mermaid
flowchart LR
    subgraph importPhase["Import Phase"]
        BrowseForFile["BrowseForFile - User selects file"]
        RunImport["RunImport - Reads Excel, validates, inserts rows"]
    end

    subgraph reconcilePhase["Reconcile Phase"]
        RunReconcile["RunReconcile - Matches transactions against bank statements"]
        InsertResult["InsertResult - Writes match results to tblReconcileResult"]
    end

    subgraph exportPhase["Export Phase"]
        RunExport["RunExport - Generates Excel report"]
    end

    BrowseForFile --> RunImport
    RunImport --> RunReconcile
    RunReconcile --> RunExport
    modUtil --> RunImport
    modUtil --> RunReconcile
    modUtil --> RunExport
    modUtil --> InsertResult
    clsTransaction["clsTransaction - Validation"] --> RunImport
```

### Data flow

1. **User interaction** — The `frmImport` form exposes file-browse and action buttons. `modImport.BrowseForFile` populates a text box with the selected file path.
2. **Import** — `modImport.RunImport` opens the Excel file (with retry logic for file-lock contention), parses each row into a `clsTransaction` object, validates it via `clsTransaction.IsValid()`, and inserts valid rows into either `tblTransactions` or `tblBankStatement` depending on the file type. A batch ID is generated and stored in `modUtil.gCurrentBatchID`.
3. **Reconcile** — `modReconcile.RunReconcile` reads transactions for the current batch and attempts three-tier matching against bank statements: exact `RefNo` match, fuzzy date+amount match, and finally an "unmatched" fallback. Results are written to `tblReconcileResult`.
4. **Export** — `modExport.RunExport` reads the reconciliation results joined with transaction data, generates an Excel workbook with light-red highlighting on variance rows, and saves it to the project directory.

### Module dependency graph

```mermaid
flowchart LR
    subgraph DataLayer["Database Tables"]
        tblTransactions["tblTransactions"]
        tblBankStatement["tblBankStatement"]
        tblImportBatch["tblImportBatch"]
        tblReconcileResult["tblReconcileResult"]
        tblErrorLog["tblErrorLog"]
    end

    subgraph LogicLayer["VBA Logic"]
        modImport["modImport"]
        modReconcile["modReconcile"]
        modExport["modExport"]
        modUtil["modUtil"]
    end

    subgraph ModelLayer["Data Model"]
        clsTransaction["clsTransaction"]
    end

    modImport --> modUtil
    modImport --> clsTransaction
    modImport --> tblTransactions
    modImport --> tblBankStatement
    modImport --> tblImportBatch
    modImport --> tblErrorLog

    modReconcile --> modUtil
    modReconcile --> tblTransactions
    modReconcile --> tblBankStatement
    modReconcile --> tblReconcileResult

    modExport --> modUtil
    modExport --> tblTransactions
    modExport --> tblReconcileResult
```

`modUtil` is the leaf of the dependency tree — it has no internal VBA dependencies. All other units depend on it for shared functionality. `clsTransaction` is the only class and is used exclusively by `modImport` during validation.

## Key Patterns and Architecture

### Implicit state via module-level variable

The `modUtil.gCurrentBatchID` public variable acts as implicit state passing between pipeline phases. `RunImport` sets it at the end, and `RunReconcile` / `RunExport` read it when no explicit batch ID is provided. This is a simple pattern that couples the phases into a strict execution order (import → reconcile → export), and it means there is no session or user isolation in a shared environment.

### Three-tier matching strategy

Reconciliation uses a cascading match approach:
1. **Exact match** on `RefNo` — the most confident match.
2. **Fuzzy match** on `Amount` with `TxnDate` within ±1 day — handles minor timing differences.
3. **Unmatched** — anything that doesn't fit either category is flagged as potentially problematic.

Duplicates within the same batch are flagged immediately without attempting matching.

### Locale-safe SQL formatting

`modUtil.FormatNumberForSql` wraps VBA's `Str()` function, which always uses `.` as the decimal separator regardless of Windows regional settings. This is critical because `&` concatenation and `CStr` use the system locale's decimal separator, which would corrupt SQL strings on non-US machines. Do not replace `Str()` with `CStr` without re-testing.

### File-lock retry logic

Excel file opening includes a 3-attempt retry loop with 2-second sleeps on error 70 (file locked by another process). This handles the common case where the file is already open in another Excel instance.

## Dependencies and Integration

### External dependencies

- **Microsoft Excel** — All Excel interaction uses late binding via `CreateObject("Excel.Application")`, so no VBA reference to the Excel type library is required. The module works with `.xlsx` files.
- **DAO** — All database access uses `DAO.Database` and `DAO.Recordset` objects through `CurrentDb()`.
- **`frmImport`** — The import form is the user-facing entry point. The modules read and write controls on this form and cannot run outside the Access host application.

### Data tables

| Table | Purpose |
|---|---|
| `tblImportBatch` | Tracks each import run with batch ID, filename, date, and row count |
| `tblTransactions` | Imported internal transactions, keyed to a batch |
| `tblBankStatement` | Imported bank statement rows (branch hardcoded to `"BANK"`), keyed to a batch |
| `tblReconcileResult` | Reconciliation outcomes linking transactions to bank statements |
| `tblErrorLog` | Validation and import errors with batch association |

### Integration notes

- `tblTransactions.ImportBatchID` and `tblBankStatement.ImportBatchID` link rows to a specific import batch.
- `tblReconcileResult.TransID` and `tblReconcileResult.StmtID` form foreign-key-like links to transactions and bank statements.
- `tblErrorLog.BatchID` associates errors with an import batch.

## Notes for Developers

### Single-user design

The module is designed for single-user use within a shared Access backend. `modUtil.gCurrentBatchID` is a global variable that gets overwritten on each import — there is no locking or session isolation. Concurrent users will interfere with each other's batches.

### SQL injection risk

All SQL is constructed via string concatenation. While `modUtil` provides locale-safe date and number formatters, and single quotes are doubled for string escaping, the module is vulnerable to SQL injection if `RefNo` or `FileName` values contain crafted SQL payloads. For production hardening, consider switching to DAO parameter queries or an ADO command object with parameters.

### Export filename collision

Exported reports are saved as `ReconcileReport_yyyymm.xlsx`. If multiple batches are exported in the same month, later exports silently overwrite earlier ones — there is no day-level granularity in the filename.

### Date validity cutoff

`clsTransaction.IsValid()` rejects any transaction dated before January 1, 2000. This hard-coded cutoff may need adjustment if historical data import becomes necessary.

### Duplicate handling

If a `RefNo` appears multiple times within the same batch, all occurrences are flagged as `"Duplicate"` without attempting matching. Only one unique transaction per `RefNo` can be matched in a given batch. Consider whether deduplication at import time would be a better UX.

### Bank statement detection

Bank statement files are detected by checking if the filename contains `"BankStatement"` (case-insensitive). This is a fragile heuristic — if a non-bank file happens to contain that substring, it will be treated as a bank statement with all rows assigned branch `"BANK"`.