# Getting Started

## What This Project Does

AR-Reconciliation-Tool is a VBA + MS Access application that automates the reconciliation of branch-level transaction exports against bank statements. It imports Excel files into an Access backend, matches transactions using exact and fuzzy reference-number lookup, and exports a formatted color-coded report highlighting any variance. This repo is a self-contained demo built to illustrate the legacy VBA/Access architecture described in the Customer VBA System Transition, and to demonstrate TextIQ's ability to analyze such codebases.

## Recommended Reading Order

Start here to orient yourself quickly:

1. **[README.md](../..//README.md)** — Full context, build instructions, and how this demo fits into the broader customer migration program.
2. **[`.codewiki/overview.md`](../..//.codewiki/overview.md)** — Module-level breakdown of every VBA module, the architecture diagram, and the matching strategy.
3. **`schema/backend_schema.sql`** — The Access database DDL. Five tables across two databases; understanding the schema makes the VBA code trivial to follow.
4. **`schema/er-diagram.md`** — Visual entity-relationship diagram for the data model.
5. **`vba-source/modUtil.bas`** — The smallest, most self-contained module. Read it first to understand the shared helpers (`GetNextBatchNumber`, `LogError`, `FormatDateForSql`, `FormatNumberForSql`) that every other module depends on.
6. **`vba-source/clsTransaction.cls`** — The domain model. 12 lines of VBA that defines the transaction data type and its validation logic.
7. **`vba-source/modImport.bas`** — The import engine. Follow `RunImport()` to see how data flows from Excel into the database.
8. **`vba-source/modReconcile.bas`** — The reconciliation logic. The most complex module; step through `RunReconcile()` to understand the two-tier matching strategy.
9. **`vba-source/modExport.bas`** — Report generation. A straightforward read showing how reconciled data is rendered into an Excel workbook.
10. **`forms-spec.md`** — Form/control layout spec with positions, captions, and VBA event handlers. Useful when navigating the Access UI or debugging form behavior.

## Key Entry Points

The following files are the most important to understand first:

### `vba-source/modImport.bas` — Entry Point

The system's public face. `RunImport()` is the first thing a user calls. It:

- Prompts for an Excel file via Windows file picker.
- Detects file type (bank statement vs. branch transaction) from the filename.
- Opens the workbook through COM automation, with retry-and-wait for locked files.
- Iterates over rows, validates each as a `clsTransaction`, and inserts into the correct table.

### `vba-source/modReconcile.bas` — Core Business Logic

`RunReconcile()` is where the actual AR reconciliation happens. It:

- Deletes stale results for the batch.
- Iterates every transaction, checking for duplicates by `RefNo`.
- Performs exact lookup against `tblBankStatement` by `BankRefNo`.
- Falls back to fuzzy matching (same amount, date within +/-1 day).
- Writes outcomes to `tblReconcileResult`.

### `vba-source/modExport.bas` — Report Output

`RunExport()` generates the final Excel report. It joins `tblReconcileResult` with `tblTransactions`, writes six columns, highlights non-zero variance rows in light red, and saves as `ReconcileReport_yyyymm.xlsx`.

### `vba-source/modUtil.bas` — Shared Infrastructure

Though only ~40 lines, every module reads or writes from this module. Key items:

- **`gCurrentBatchID`** — Global variable holding the active import batch across forms.
- **`GetNextBatchNumber()`** — Returns the next sequential batch ID from `tblImportBatch`.
- **`LogError()`** — Appends a row to `tblErrorLog`.
- **`FormatDateForSql()`** — Wraps a date into Jet SQL `#mm/dd/yyyy#` syntax.
- **`FormatNumberForSql()`** — Converts Currency to a locale-safe SQL string via `Str()`, avoiding the comma-vs-period issue on non-English Windows.

### `vba-source/clsTransaction.cls` — Domain Model

The single class module. Five public fields (`TransID`, `Branch`, `TxnDate`, `Amount`, `RefNo`) and an `IsValid()` method enforcing:

- Amount must be positive.
- Reference number must be non-empty.
- Branch must be set.
- Date must be after January 1, 2000.

## Project Structure

```
README.md                    -- Project context and build instructions
forms-spec.md                -- Form control layout + event handler spec
codewiki/
  overview.md                -- Full module documentation
  guides/
    getting-started.md       -- This guide
schema/
  backend_schema.sql         -- Access DDL for all tables
  er-diagram.md              -- ER diagrams for both databases
vba-source/
  clsTransaction.cls         -- Transaction data model (class module)
  modImport.bas              -- Excel import engine
  modReconcile.bas           -- Reconciliation/matching logic
  modExport.bas              -- Report generation
  modUtil.bas                -- Shared utilities (batch numbers, SQL formatting)
```

## Architecture

The system follows a linear **Import -> Reconcile -> Export** pipeline. Data and logic are separated: the frontend (Forms + VBA) lives in one Access database, while the data layer lives in two backend databases.

```mermaid
flowchart LR
    subgraph Data_Layer["Data Layer"]
        Data_Backend["Data_Backend.accdb<br/>core tables"]
        Data_Log["Data_Log.accdb<br/>error logging"]
    end

    subgraph Frontend["Frontend (Frontend.accdb)"]
        Forms["Forms<br/>UI layer"]
        VBA["VBA Modules<br/>business logic"]
    end

    subgraph Pipeline["Import -> Reconcile -> Export"]
        modImport["modImport"]
        modReconcile["modReconcile"]
        modExport["modExport"]
        modUtil["modUtil<br/>shared utils"]
        clsTransaction["clsTransaction<br/>data model"]
    end

    modImport --> clsTransaction
    modImport --> modUtil
    modImport --> modReconcile
    modReconcile --> modUtil
    modExport --> modUtil
    Forms --> VBA
    VBA --> Data_Backend
    VBA --> Data_Log
```

### Matching Strategy

The reconciliation module uses a three-tier approach:

1. **Duplicate detection** — If a `RefNo` appears more than once in the same batch, it is flagged as `"Duplicate"`.
2. **Exact match** — Transaction `RefNo` looked up against bank statement `BankRefNo`.
3. **Fuzzy fallback** — If no exact match: same `Amount` and transaction date within +/-1 day.
4. **Unmatched** — If neither tier matches, the transaction is recorded as `"Unmatched"` with the full amount as variance.

## Configuration

### Key Files

| File | Purpose |
|---|---|
| `schema/backend_schema.sql` | Defines all five tables (`tblImportBatch`, `tblTransactions`, `tblBankStatement`, `tblReconcileResult`, `tblErrorLog`) and their columns. This is the single source of truth for the data model. |
| `forms-spec.md` | Describes three forms (`frmMain`, `frmImport`, `frmReconcileResult`) with control positions, captions, and VBA click handlers. Used to manually build forms in Access. |

### Database Layout

Two Access `.accdb` files hold all persisted data:

- **Data_Backend.accdb** — Core reconciliation tables. Linked from the frontend for reads and writes.
- **Data_Log.accdb** — Error logging table (`tblErrorLog`). Stored separately since error data is independent of the reconciliation workflow.

No enforced foreign keys across `.accdb` files; consistency relies on shared `BatchID` conventions.

### Build & Validation

- **`build/Build-AccessDemo.ps1`** — PowerShell script to create the Access databases, run DDL, and import VBA source. Windows-only, requires MS Access.
- **`build/validate_sources.py`** — Python script that checks schema/VBA/form-spec consistency. Run after any source change: `python3 build/validate_sources.py`.
- **`build/generate_sample_data.py`** — Regenerates deterministic sample Excel files with edge cases (fixed random seed).

## Common Patterns

### Error Handling

- VBA uses the classic `On Error Resume Next` / `Err.Clear` / `On Error GoTo 0` pattern for COM automation (Excel file open retries).
- Row-level import errors are captured in `tblErrorLog` via `modUtil.LogError()`, not raised as exceptions.
- The global `On Error` is never left dangling — always restored after the protected block.

### SQL Construction

All SQL is built by string concatenation (no parameterized queries — DAO/Jet does not support them). Safety is handled by:

- Escaping single quotes in string values with `Replace(val, "'", "''")`.
- Using `modUtil.FormatDateForSql()` for Jet date literals (`#mm/dd/yyyy#`).
- Using `modUtil.FormatNumberForSql()` for Currency values, which calls `Str()` to force a locale-independent decimal separator.

### Batch Isolation

Every import run gets a new `BatchID` (from `GetNextBatchNumber()`). All downstream operations — reconcile, export, error logging — target a specific batch. This means:

- A user can import multiple files and reconcile each independently.
- The global `gCurrentBatchID` in `modUtil` tracks the active batch across form boundaries.
- Both `RunReconcile()` and `RunExport()` accept an optional `batchID` parameter, defaulting to `-1` (use the global).

### COM Automation

Excel is used via late-bound COM (`CreateObject("Excel.Application")`). Key patterns:

- File-open retry with `Sleep 2000` on error 70 (file locked), up to 3 attempts.
- All Excel objects (`Application`, `Workbook`, `Worksheet`) are explicitly set to `Nothing` after use.
- Late binding is used throughout — no type library reference is required.

### Global State

The only global variable in the project is `gCurrentBatchID` in `modUtil`. It is set once during import and read by reconcile/export. This is intentional: in the Access/VBA world, a module-level `Public` variable is the standard way to share state across forms.

## Quick Start

To try the application end-to-end:

1. Open `Frontend.accdb` in MS Access on a Windows machine with Access installed.
2. Use the **Import** button to select a sample Excel file from `sample-data/`.
3. Click **Reconcile** to run matching logic against the imported bank statement.
4. Click **Export** to generate the color-coded reconciliation report.

No build step is needed — the `.accdb` files are included and ready to open.