# Getting Started

Welcome to the **AR Reconciliation Tool** — a self-contained demo application built with VBA and Microsoft Access that simulates the core workflow of an Accounts Receivable reconciliation system.

## What This Project Does

This project simulates an AR reconciliation workflow: branch offices export daily transaction lists in Excel, the bank sends back matching statements, and this tool automates the matching process. It imports Excel files into an Access backend, runs exact-and-fuzzy matching logic, and produces a reconciled report highlighting any variances. The entire pipeline runs inside MS Access via VBA using a split-database architecture.

---

## Recommended Reading Order

Start here if you're new to the team:

1. **[Repository Overview](../overview.md)** — Full system architecture, technology stack, and module guide.
2. **[README](../ar-reconciliation-vba-access/README.md)** — High-level intro, demo context, and build instructions.
3. **`vba-source/clsTransaction.cls`** — The simplest file in the codebase. Read this first to understand the data model (22 lines).
4. **`vba-source/modUtil.bas`** — Shared helpers used everywhere else. The SQL formatting functions here clarify how data gets persisted.
5. **`vba-source/modImport.bas`** — The import pipeline. Main entry point for data ingestion from Excel to Access.
6. **`vba-source/modReconcile.bas`** — The reconciliation matching engine (exact match + fuzzy fallback).
7. **`vba-source/modExport.bas`** — The report generator that writes reconciled data back to Excel.
8. **`schema/backend_schema.sql`** — The database DDL that backs all the modules.
9. **`schema/er-diagram.md`** — Visual entity-relationship diagram of the database.
10. **[Forms Spec](../ar-reconciliation-vba-access/forms-spec.md)** — Control layout and handler mapping for the Access UI.

---

## Key Entry Points

| File | Role | Why read it first |
|---|---|---|
| `clsTransaction.cls` | Transaction record model | Defines the domain data shape and validation rules |
| `modImport.bas` | Excel file import | Entry point for the data pipeline; shows the full ingest flow |
| `modReconcile.bas` | Matching engine | Core business logic — the "reconcile" part of the tool |
| `modExport.bas` | Report generation | Final step — writes reconciled results back to Excel |
| `modUtil.bas` | Shared helpers | Cross-cutting utilities (batch ID management, error logging, SQL formatting) |

The data flows left to right through three main modules:

```
modImport → modReconcile → modExport
```

Each module depends on `modUtil` for shared functionality.

---

## Project Structure

```
ar-reconciliation-vba-access/
├── README.md                      # High-level intro and build guide
├── forms-spec.md                  # Form/control layout specification
├── Frontend.accdb                 # Access frontend (forms + VBA, no data)
├── Data_Backend.accdb             # Linked backend tables (core data store)
├── Data_Log.accdb                 # Error logging database
├── vba-source/                    # VBA source of truth (version-controlled)
│   ├── clsTransaction.cls         # Transaction domain model
│   ├── modImport.bas              # Excel import module
│   ├── modReconcile.bas           # Reconciliation matching module
│   ├── modExport.bas              # Report export module
│   └── modUtil.bas                # Shared utilities
├── schema/                        # Database DDL + diagram
│   ├── backend_schema.sql         # Jet/ACE table definitions
│   └── er-diagram.md              # Entity-relationship diagram
└── sample-data/                   # Fake Excel files with edge cases
    ├── BankStatement_202606.xlsx
    ├── Transactions_HCM_202606.xlsx
    └── Transactions_HN_202606.xlsx
```

### Database Split

The Access application uses a **split-database** pattern:

- **Frontend.accdb** — Forms, VBA code, queries. Contains no user data.
- **Data_Backend.accdb** — Linked tables for transactions, bank statements, import batches, and reconciliation results. This is the core data store.
- **Data_Log.accdb** — Error logging via `tblErrorLog`.

The two data databases are linked from the frontend — VBA code accesses them transparently as if they were local tables.

### Architecture

```mermaid
flowchart TD
    subgraph INPUT["Excel Input Files"]
        TXN["Transactions_HC*.xlsx"]
        BANC["BankStatement_*.xlsx"]
    end

    subgraph PROCESS["VBA Application Layer"]
        IMP["modImport"]
        REC["modReconcile"]
        EXP["modExport"]
        TRANS["clsTransaction"]
        UTIL["modUtil"]
    end

    subgraph STORE["Access Databases"]
        FRONT["Frontend.accdb"]
        BACK["Data_Backend.accdb"]
        LOGS["Data_Log.accdb"]
    end

    subgraph OUTPUT["Excel Output"]
        RPT["ReconcileReport_*.xlsx"]
    end

    INPUT -->|"Import"| PROCESS
    IMP -->|"Persist records"| STORE
    REC -->|"Query and match"| STORE
    PROCESS -->|"Reconcile data"| STORE
    EXP -->|"Write report"| OUTPUT
    TRANS -->|"Transaction model"| IMP
    UTIL -->|"Helpers"| IMP
    UTIL -->|"Helpers"| REC
    UTIL -->|"Helpers"| EXP
```

---

## Configuration

### Database Schema

All tables are defined in [`schema/backend_schema.sql`](../ar-reconciliation-vba-access/schema/backend_schema.sql). Key tables:

| Table | Database | Purpose |
|---|---|---|
| `tblImportBatch` | Data_Backend | Tracks each import file (filename, date, row count, status) |
| `tblTransactions` | Data_Backend | Branch transaction records imported from Excel |
| `tblBankStatement` | Data_Backend | Bank statement records imported from Excel |
| `tblReconcileResult` | Data_Backend | Match results: matched, fuzzy-matched, duplicate, unmatched |
| `tblErrorLog` | Data_Log | Error records with batch ID, row number, and message |

The entity-relationship diagram is documented in [`schema/er-diagram.md`](../ar-reconciliation-vba-access/schema/er-diagram.md).

### Global State

The only module-level shared state is `modUtil.gCurrentBatchID` (type `Long`), which holds the most recently imported batch ID. Downstream modules (`modReconcile`, `modExport`) read this when called without an explicit `batchID` argument (defaulting to `-1`, meaning "use the global").

### Sample Data

The `sample-data/` directory contains fake Excel files with deliberate edge cases:
- Duplicate reference numbers
- Rounding variances
- 1-day date shifts between branch and bank records
- Unmatched rows

These are used for testing. They can be regenerated with `python3 build/generate_sample_data.py`.

---

## Common Patterns

### Transaction Validation

Every imported row gets a `clsTransaction` instance. Validation is centralized in `clsTransaction.IsValid()`:

- Amount must be positive
- Reference number must be non-empty
- Branch must be specified
- Date must be after 2000-01-01

Invalid rows are counted and logged via `modUtil.LogError()`.

### SQL Formatting

The project uses raw SQL text concatenation (no parameterized queries — this is VBA/Access). To avoid locale-related issues:

- Dates use `modUtil.FormatDateForSql()` which wraps values in `#mm/dd/yyyy#` — Access's native date literal format.
- Currency values use `modUtil.FormatNumberForSql()` which calls `Str()` to force a `.` decimal separator regardless of Windows locale, then trims the leading space.
- String values have single quotes escaped via `Replace(..., "'", "''")`.

### Batch Management

Each import creates a new batch:

1. `modUtil.GetNextBatchNumber()` finds the max `BatchID` in `tblImportBatch` and increments it.
2. All records from a single import share the same `ImportBatchID`.
3. Reconciliation and export operate on the current batch by default (using `gCurrentBatchID`), but both accept an optional explicit `batchID` for multi-batch scenarios.

### File Import Pattern

The import module (`modImport`) follows this pattern:

1. Open Excel via late-bound COM automation (`CreateObject("Excel.Application")`).
2. Retry up to 3 times if the file is locked (Error 70).
3. Detect file type by filename pattern — `BankStatement` in the name means bank data.
4. Build `clsTransaction` objects per row and validate.
5. Insert valid rows directly into the appropriate backend table.
6. Log invalid rows.
7. Record the batch metadata and update the global batch ID.

### Reconciliation Matching

The reconcile module (`modReconcile`) uses a three-tier matching strategy per transaction:

1. **Duplicate check** — If the same `RefNo` appears more than once in the batch, mark as `"Duplicate"`.
2. **Exact match** — Look for a bank statement record with the same `BankRefNo`.
3. **Fuzzy match** — If no exact match, look for the same amount with a transaction date within +/-1 day.
4. **Unmatched** — If neither exact nor fuzzy match is found, mark as `"Unmatched"` with variance equal to the full transaction amount.

### Error Handling

Errors are logged to `Data_Log.accdb` via `modUtil.LogError(batchID, rowNo, message)`. The import module wraps the Excel COM open in a retry loop; all other modules assume valid input and let DAO errors propagate.

---

## Trying It Out

### Prerequisites

- Windows machine with **Microsoft Access** installed (any recent version supporting .accdb files).
- No build step is required — the `.accdb` files are included and ready to open.

### Quick start

1. Open `Frontend.accdb` on a Windows machine with MS Access.
2. Click **Import**, browse to a sample Excel file in `sample-data/`, and click **Start Import**.
3. Click **Reconcile** to run the matching engine.
4. View results on the reconciliation form, or click **Export** to generate the Excel report.

### Validating source consistency

If you change any VBA, schema, or forms spec file, run:

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

This checks that table names referenced in VBA match `backend_schema.sql`, and that every form handler referenced in `forms-spec.md` actually exists in the VBA source.

---

## Next Steps

- Read the full **[Repository Overview](../overview.md)** for deep dives into each module.
- Review the **[Forms Spec](../ar-reconciliation-vba-access/forms-spec.md)** to understand the UI layer.
- Explore the **[ER Diagram](../ar-reconciliation-vba-access/schema/er-diagram.md)** for the complete data model.
- Check the **[Demo Talk Track](../ar-reconciliation-vba-access/demo-talk-track.md)** for a click-by-click walkthrough script.