# Getting Started

## What This Project Does

This is an **AR (Accounts Receivable) Reconciliation Tool** built on VBA + MS Access. It imports branch transaction exports and bank statements from Excel, matches them by reference number (with a fuzzy fallback for same amount within +-1 day), and produces a reconciliation report in Excel. The architecture uses a split database pattern: a Frontend Access file holds all forms and VBA, while linked tables in two backend Access files store raw data and error logs separately.

This repo is a self-built demo sample that illustrates the legacy VBA + MS Access pattern described in the Customer VBA System Transition MoM, and is used to demonstrate TextIQ's ability to analyze VBA + Access codebases and generate detail design documents from them.

---

## Recommended Reading Order

New engineers should read resources in this order:

1. **`README.md`** at the repo root -- high-level overview, build instructions, and project layout
2. **`vba-source/modUtil.bas`** -- shared utilities used by every other module; read this to understand the common patterns (batch ID management, SQL formatting, error logging)
3. **`vba-source/clsTransaction.cls`** -- the core data model; a small class representing a single transaction with validation logic
4. **`vba-source/modImport.bas`** -- entry point for data ingestion; shows the full Excel file import flow
5. **`vba-source/modReconcile.bas`** -- the heart of the tool; implements the matching algorithm
6. **`vba-source/modExport.bas`** -- final output; shows how to automate Excel via COM from VBA

---

## Key Entry Points

### `clsTransaction` -- the data model

[`clsTransaction.cls`](vba-source/clsTransaction.cls) is the only class module in the project. It represents a single transaction with five public fields (`TransID`, `Branch`, `TxnDate`, `Amount`, `RefNo`) and a single validation method `IsValid()`. Every row imported from Excel becomes an instance of this class before being written to the backend database.

### `modImport` -- the import entry point

[`modImport.bas`](vba-source/modImport.bas) exposes the public function `RunImport()`. This is the main function called by the `frmImport` form's Import button. It:

- Reads a file path from the UI
- Detects whether the file is a bank statement (filename contains "BankStatement") or a branch transaction export
- Opens the Excel file via COM automation with retry logic (up to 3 attempts on file lock)
- Iterates rows, creates `clsTransaction` objects, validates them, and inserts into `tblTransactions` or `tblBankStatement` in the backend
- Tracks row counts and errors via `modUtil`

Also provides `BrowseForFile()` -- a file picker wrapper for the UI.

### `modReconcile` -- the matching engine

[`modReconcile.bas`](vba-source/modReconcile.bas) exposes `RunReconcile()`. For each transaction in the given batch, it:

1. Checks for duplicate `RefNo` within the same batch (marks as "Duplicate")
2. Tries an **exact match** by `RefNo` against bank statements
3. Falls back to a **fuzzy match**: same amount and transaction date within +-1 day
4. Marks anything not matched as "Unmatched" with the full amount as variance

### `modExport` -- the report generator

[`modExport.bas`](vba-source/modExport.bas) exposes `RunExport()`. It:

- Queries the `tblReconcileResult` joined to `tblTransactions` for a given batch
- Creates an Excel workbook via late-bound COM automation (`CreateObject("Excel.Application")`)
- Writes headers and populates rows from the recordset, highlighting rows with non-zero variance in red
- Saves as `ReconcileReport_<YYYYMM>.xlsx` in the project directory

### `modUtil` -- the shared utilities

[`modUtil.bas`](vba-source/modUtil.bas) is referenced by every other module. Key members:

- `gCurrentBatchID` -- public module-level variable tracking the current import batch
- `GetNextBatchNumber()` -- reads the max `BatchID` from `tblImportBatch` and returns the next value
- `LogError()` -- writes an error record to `tblErrorLog`
- `FormatDateForSql()` -- formats a VBA Date into Access SQL date literal (`#mm/dd/yyyy#`)
- `FormatNumberForSql()` -- converts a Currency value to a string safe for SQL concatenation, handling locale differences (use `Str()` which always uses `.` as decimal separator, unlike `CStr` on non-US locales)

---

## Project Structure

```
.
|-- README.md                         -- Project overview and build instructions
|-- Frontend.accdb                    -- Built Access frontend (forms + VBA, not in git)
|-- Data_Backend.accdb                -- Built Access backend (raw data tables, not in git)
|-- Data_Log.accdb                    -- Built Access backend (error log tables, not in git)
|-- sample-data/
|   |-- BankStatement_202606.xlsx     -- Sample bank statement with edge cases
|   |-- Transactions_HCM_202606.xlsx  -- Sample HCM branch transactions
|   |-- Transactions_HN_202606.xlsx   -- Sample HN branch transactions
|-- vba-source/                       -- Source of truth (version-controlled text)
|   |-- clsTransaction.cls            -- Transaction data model class
|   |-- modImport.bas                 -- Excel import module
|   |-- modReconcile.bas              -- Reconciliation matching logic
|   |-- modExport.bas                 -- Excel report export module
|   |-- modUtil.bas                   -- Shared utility functions
|-- schema/                           -- Access DDL and ER diagram
|-- forms-spec.md                     -- Form and control layout specification
|-- build/                            -- Build, validation, and generator scripts
    |-- Build-AccessDemo.ps1          -- PowerShell build script (Windows + MS Access)
    |-- validate_sources.py           -- Consistency validator
    |-- generate_sample_data.py       -- Deterministic sample data generator
```

### Data flow

```mermaid
flowchart TD
    A["Excel Input
Transactions / Bank Statement"] -->|"Import"| B["Frontend.accdb
Forms + VBA"]
    B -->|"Linked Tables"| C["Data_Backend.accdb
Raw Data"]
    B -->|"Linked Tables"| D["Data_Log.accdb
Error Logs"]
    B -->|"Export"| E["Excel Output
ReconcileReport.xlsx"]
```

### Module dependency graph

```mermaid
flowchart TD
    A["modImport.bas"] -->|"Creates"| B["clsTransaction"]
    B -->|"Inserts into"| C["Data_Backend.accdb"]
    D["modReconcile.bas"] -->|"Reads from"| C
    D -->|"Writes to"| E["Reconcile Results"]
    F["modExport.bas"] -->|"Reads from"| C
    F -->|"Reads from"| E
    F -->|"Writes to"| G["Excel Report"]
    H["modUtil.bas"] -->|"Shared by"| A
    H -->|"Shared by"| D
    H -->|"Shared by"| F
```

---

## Configuration

### Key config files

| File | Purpose |
|------|---------|
| `README.md` | Project overview, build instructions, validation commands |
| `vba-source/` | All VBA source lives here -- this is the single source of truth |
| `schema/` | Access table DDL definitions and ER diagram |
| `forms-spec.md` | Form/control layout spec used by the build script to generate `.accdb` files |
| `build/Build-AccessDemo.ps1` | PowerShell script that compiles VBA source + schema + forms spec into runnable `.accdb` files (Windows-only) |
| `build/validate_sources.py` | Validates that VBA references match `schema/backend_schema.sql` and that every `Module.Proc` handler in `forms-spec.md` exists in the VBA source |
| `build/generate_sample_data.py` | Generates the sample Excel input files deterministically (fixed random seed) |

### Access split-database architecture

This project uses Microsoft Access's split-database pattern:

- **Frontend** (`Frontend.accdb`) -- forms, reports, macros, and all VBA code. Contains no data tables.
- **Backend** (`Data_Backend.accdb`) -- raw data tables only (`tblTransactions`, `tblBankStatement`, etc.). No VBA or forms.
- **Log Backend** (`Data_Log.accdb`) -- error and log tables only (`tblErrorLog`, `tblImportBatch`).

The frontend links to the backend tables. During the build process, the build script creates the backend databases, creates tables, then links them into the frontend.

The `.accdb` files are **not committed to git** -- they are built from the text source at runtime.

---

## Common Patterns

### SQL string construction

Because this is VBA with no ORM, all SQL is built as string concatenation. Key conventions:

- **Date literals** use `#mm/dd/yyyy#` format -- always route through `modUtil.FormatDateForSql()`
- **Currency values** must use `.` as decimal separator -- always route through `modUtil.FormatNumberForSql()` to avoid locale issues on non-US Windows
- **String literals** escape single quotes via `Replace(value, "'", "''")`
- **NULL values** use the VBA `IIf(IsNull(...), "NULL", value)` pattern

### Excel COM automation

Excel is opened via late-bound COM (`CreateObject("Excel.Application")`) rather than early-bound reference. This avoids version-specific dependency issues:

- Always set `xlApp.Visible = False` for headless operation
- Close workbooks with `SaveChanges:=False` when discarding changes
- Quit the application and set all object variables to `Nothing` to avoid orphaned Excel processes
- Handle error code 70 ("Permission denied") with retry logic -- this occurs when another process has the file open

### Import error handling

The import flow uses a consistent pattern:

1. Assign a batch ID via `modUtil.GetNextBatchNumber()` before processing
2. Process rows in a `For` loop, creating `clsTransaction` for each
3. Call `IsValid()` on each transaction -- if false, log via `modUtil.LogError()` and increment error counter
4. On success, increment row counter
5. Insert an `tblImportBatch` record with final counts
6. Update the form's status label with a summary

File lock retries (up to 3 attempts with 2-second sleeps) handle the common case where the file is briefly open in Excel by the user.

### Batch isolation

All transactions and bank statements from a single import share an `ImportBatchID`. The reconciliation and export operations accept an optional `batchID` parameter (defaulting to `modUtil.gCurrentBatchID`). Before reconciling a batch, all previous reconcile results for that batch are deleted (`DELETE FROM tblReconcileResult`). This allows re-importing and re-reconciling a batch without residual state.

### Validation workflow

Before building, or after changing any VBA or schema file, run:

```bash
python3 build/validate_sources.py
```

This ensures table names referenced in VBA code match the schema, and that every procedure referenced in the forms spec actually exists in the VBA source. This is a key consistency gate.

### Sample data

The `sample-data/` directory contains three Excel files with deliberate edge cases:

- **Duplicate reference numbers** -- tests the dedup detection in `modReconcile`
- **Rounding variances** -- small amount differences to test fuzzy matching
- **1-day date shifts** -- transactions where the bank date differs by one day from the branch record
- **Unmatched rows** -- transactions with no corresponding bank entry

Regenerate deterministically with:

```bash
python3 build/generate_sample_data.py
```

---

## Building from source

To build the runnable `.accdb` files, you need Windows with MS Access installed. Follow the instructions in `build/README-build.md`. The build script compiles the VBA source from `vba-source/`, creates the backend databases from `schema/`, and links everything into `Frontend.accdb`.

---

## Quick reference: module responsibilities

| Module | Responsibility | Key Public Function |
|--------|---------------|---------------------|
| [`clsTransaction.cls`](vba-source/clsTransaction.cls) | Transaction data model | `IsValid()` |
| [`modImport.bas`](vba-source/modImport.bas) | Excel file import | `RunImport()`, `BrowseForFile()` |
| [`modReconcile.bas`](vba-source/modReconcile.bas) | Matching transactions to bank statements | `RunReconcile()` |
| [`modExport.bas`](vba-source/modExport.bas) | Export results to Excel | `RunExport()` |
| [`modUtil.bas`](vba-source/modUtil.bas) | Shared utilities | `GetNextBatchNumber()`, `LogError()`, `FormatDateForSql()`, `FormatNumberForSql()` |