# Root

## Overview

This repository contains a **self-contained Microsoft Access application** for Accounts Receivable (AR) reconciliation. It ingests financial transaction data from Excel workbooks, reconciles internal transactions against bank statement records, and produces reconciled report exports. The application bridges two data sources — internal ERP transactions and external bank statements — using a three-tier matching engine (duplicate detection, exact match, and fuzzy match) to identify matched, partially matched, and unmatched transactions.

The system is designed as a desktop data-processing tool: a single user opens the Access frontend, imports files via a form-based UI, runs reconciliation, and exports results — all without requiring a server or database administrator.

## Sub-module Guide

### ar-reconciliation-vba-access

The entire application lives within a single Access frontend (`Frontend.accdb`) backed by two Access database files (`Data_Backend.accdb` for persistent tables, `Data_Log.accdb` for logging). The core logic is expressed in VBA modules, which together form the reconciliation pipeline.

The sub-modules and their responsibilities are:

| Sub-module | What it does |
|------------|-------------|
| **VBA Source** (`vba-source/`) | Five VBA modules implementing the three-phase reconciliation workflow: import, match, export. |
| **Database Files** | `Frontend.accdb` (forms, queries, UI), `Data_Backend.accdb` (core data tables), `Data_Log.accdb` (error/log tables). |
| **Sample Data** | Example Excel files (`BankStatement_202606.xlsx`, `Transactions_HCM_202606.xlsx`, `Transactions_HN_202606.xlsx`) for testing and demo. |
| **Schema** (`schema/`) | SQL schema definition (`backend_schema.sql`) and an ER diagram (`er-diagram.md`) describing the table relationships. |
| **Forms Spec** (`forms-spec.md`) | User-facing form specifications for the Access application. |
| **Demo Talk Track** (`demo-talk-track.md`) | Instructions and talking points for presenting the tool to stakeholders. |

The VBA source sub-module is the most critical: it contains all business logic. The Access forms (`Frontend.accdb`) provide the UI layer that invokes the VBA functions.

### How the Sub-modules Relate

The data flows through the system in a linear pipeline:

```
Excel Files → modImport → Access Tables → modReconcile → modExport → Excel Report
                  ↓                                   ↑
            clsTransaction                    tblReconcileResult
                  ↓                                   ↑
            modUtil (batch IDs, logging)     modUtil (shared state)
```

The schema definitions (`schema/`) ensure that the Access tables match the expected structure for the VBA modules to operate against. Sample data (`sample-data/`) allows developers and demo users to exercise the full pipeline without needing production files. The demo talk track and forms spec serve a non-technical purpose: explaining the tool to stakeholders and defining the UI expectations.

## Key Patterns and Architecture

### Three-Phase Pipeline Architecture

The reconciliation workflow is organized as three sequential phases, each owned by a dedicated module:

| Phase | Module | Input | Output |
|-------|--------|-------|--------|
| **Import** | `modImport` | Excel files (`.xlsx`) | Populated `tblTransactions`, `tblBankStatement` |
| **Reconcile** | `modReconcile` | Transaction + bank statement tables | `tblReconcileResult` with match status |
| **Export** | `modExport` | Reconciliation results table | Excel report file |

This separation ensures each phase can be tested and reasoned about independently. Import only deals with file I/O and data validation. Reconcile only deals with matching logic. Export only deals with report formatting.

### Module-Level Global State

The only shared state between modules is `modUtil.gCurrentBatchID`, a module-level global variable. This variable is set during import and consumed by both reconcile and export when no explicit batch ID is provided. It acts as an implicit pipeline identifier, keeping the three phases aligned on which batch of data they're processing.

This is a simple but fragile approach — if multiple imports could run concurrently, the global would become a race condition.

### Three-Tier Matching Strategy

The reconciliation engine applies matching in a cascading order:

1. **Duplicate detection** — flags transactions that share the same `RefNo` within the import batch.
2. **Exact match** — joins `tblTransactions.RefNo` to `tblBankStatement.BankRefNo`.
3. **Fuzzy match** — same `Amount` AND `TxnDate` within +-1 day of the transaction date.

The tiered approach ensures deterministic results: a transaction that matches exactly is never re-evaluated against fuzzy rules. This prevents false positives from overlapping match criteria.

### Data Model

The system operates on five Access tables:

| Table | Purpose |
|-------|---------|
| `tblTransactions` | Internal transactions loaded from Excel |
| `tblBankStatement` | Bank statement rows loaded from Excel |
| `tblReconcileResult` | Reconciliation outcomes linking transactions to bank records |
| `tblImportBatch` | Import session tracking (batch ID, file name, row count, status) |
| `tblErrorLog` | Per-row validation failures |

The `tblReconcileResult` table is the central artifact of the pipeline: it bridges `tblTransactions` and `tblBankStatement` via foreign keys and encodes the matching outcome as `MatchStatus` and `Variance`.

### Transaction Model (`clsTransaction`)

The `clsTransaction` class is a lightweight data model with public fields (`TransID`, `Branch`, `TxnDate`, `Amount`, `RefNo`) and a single validation method (`IsValid()`). It is instantiated once per row during import and discarded after database insertion — essentially a transient value object rather than a persistent entity.

## Diagram: System Architecture

```
flowchart TD
    subgraph DataSource["Data Sources"]
        Bank["Bank Statement Excel"]
        Internal["Internal Transaction Excel"]
    end

    subgraph Import["Import Phase"]
        UI["frmImport form"]
        ImpMod["modImport"]
        TxnClass["clsTransaction"]
        Util["modUtil"]
    end

    subgraph Storage["Access Tables"]
        TblTxn["tblTransactions"]
        TblStmt["tblBankStatement"]
        TblBatch["tblImportBatch"]
        TblErr["tblErrorLog"]
        TblResult["tblReconcileResult"]
    end

    subgraph Reconcile["Reconcile Phase"]
        RecMod["modReconcile"]
    end

    subgraph Export["Export Phase"]
        ExpMod["modExport"]
    end

    Bank --> ImpMod
    Internal --> ImpMod
    ImpMod --> TxnClass
    ImpMod --> Util
    ImpMod --> TblTxn
    ImpMod --> TblStmt
    ImpMod --> TblBatch
    ImpMod --> TblErr

    TblTxn --> RecMod
    TblStmt --> RecMod
    RecMod --> TblResult
    RecMod --> Util

    TblResult --> ExpMod
    TblTxn --> ExpMod
    ExpMod --> Report["Excel Report"]
```

## Dependencies and Integration

### External Dependencies

- **Microsoft Excel** — The VBA modules use late-bound COM automation (`CreateObject("Excel.Application")`) to open, read, and write Excel workbooks. No compile-time reference to the Excel object library is required, making the application portable across Excel installations.
- **Microsoft Access / Jet SQL** — All data persistence is in Access `.accdb` files. SQL statements use Access/Jet SQL syntax (e.g., `#mm/dd/yyyy#` date literals, `Max()` aggregate).

### Internal Dependencies

- The VBA modules (`modImport`, `modReconcile`, `modExport`, `modUtil`) depend on each other through `modUtil.gCurrentBatchID` and direct function calls.
- The Access frontend (`Frontend.accdb`) depends on the VBA modules and the backend tables (`Data_Backend.accdb`).
- The schema definition (`schema/backend_schema.sql`) and ER diagram (`schema/er-diagram.md`) serve as the contract between the VBA logic and the database tables.

## Notes for Developers

### VBA-specific conventions and gotchas

- **Late-bound COM automation**: Excel is opened via `CreateObject("Excel.Application")` rather than a reference to the Excel object library. Named constants like `xlUp` are unavailable; use their numeric values (`-4162` for `xlUp`, `51` for `xlOpenXMLWorkbook`) directly in the code.

- **Locale-safe SQL formatting**: `modUtil.FormatNumberForSql()` uses `Str()` + `Trim()` to ensure a dot (`.`) decimal separator regardless of Windows locale. This is critical because `Currency` values concatenated as strings inherit the system locale's decimal separator (e.g., comma on Vietnamese Windows), which would break SQL statement parsing.

- **Module-level globals**: `modUtil.gCurrentBatchID` is the only shared state between modules. If multiple concurrent imports are ever supported, this global would become a race condition. Consider passing batch IDs explicitly between functions instead.

- **SQL injection risk**: String concatenation is used for all SQL statements. Reference numbers are escaped via `Replace(..., "'", "''")`, but there is no parameterized query usage. If `Branch` or `RefNo` fields could contain malicious input, this is a risk vector.

- **Recordset cleanup**: In `modReconcile.RunReconcile()`, the `rsBank` recordset is opened conditionally and closed explicitly. VBA tolerates double-close on already-closed recordsets, but this pattern is fragile and could break with stricter error handling.

- **Error handling during import**: `On Error Resume Next` is scoped to the file-opening retry loop only. Row-by-row inserts use implicit error handling — if `db.Execute` fails on a single row, the entire import crashes. Consider wrapping the inner `db.Execute` in its own `On Error Resume Next` to allow partial imports.

### Extension points

- **Fuzzy matching**: The current fuzzy match only checks amount equality and a +-1 day date window. Additional strategies (fuzzy string matching on `RefNo`, amount tolerance) could improve match rates.

- **Export format**: `RunExport` hard-codes the output path and filename pattern. Consider making the save location configurable or prompting the user.

- **Validation rules**: The date threshold in `clsTransaction.IsValid()` (`2000-01-01`) is a hardcoded magic date. Consider making this configurable.

- **Batch ID strategy**: `GetNextBatchNumber` uses a simple `max + 1` approach. Consider using an AutoNumber field in `tblImportBatch` for guaranteed uniqueness.

### Architecture strengths

- **Clear separation of concerns**: Each phase (import, reconcile, export) is a single module, making the code easy to navigate and test in isolation.
- **Batching**: The import batch model (`tblImportBatch`, `gCurrentBatchID`) allows the same tables to hold data from multiple import sessions without data collision.
- **No external dependencies beyond Office**: The system runs entirely within Microsoft Access and Excel, with no web server, no middleware, and no third-party libraries.

### Architecture limitations

- **Single-threaded**: All processing is sequential. Import, reconcile, and export cannot run in parallel, and the UI will freeze during long imports or reconciliations.
- **No audit trail**: Aside from the `ReconciledBy` and `ReconciledDate` fields in `tblReconcileResult`, there is no detailed audit of who changed what and when.
- **No rollback**: Once data is imported or a reconciliation is run, there is no built-in mechanism to undo or roll back to a previous state.