# Repository Overview

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. It takes branch-level transaction exports and bank statement files
(in Excel format), imports them into an Access backend, runs matching logic to reconcile
them, and produces a reconciled report back to Excel. Think of it as a practical, end-to-end
example of a legacy VBA-plus-Access architecture, useful for understanding how this kind of
enterprise data-pipeline operates under the hood.

If you are new to this codebase, you are in the right place. This page will orient you to
what the project does, how it is structured, and where to go next.

---

## Overview

This project simulates an **AR Reconciliation** workflow. In a real-world setting, branch
offices export daily transaction lists while the bank sends back matching statements. This
tool automates the heavy lifting:

1. **Import** — Read Excel files containing either branch transactions or bank statements
   into an Access backend database.
2. **Reconcile** — Match each branch transaction against bank records using exact reference
   number matching, with a fuzzy fallback for amounts and dates within a one-day window.
3. **Export** — Write the reconciled results back to a new Excel report, highlighting
   variance rows for easy review.

The entire pipeline runs inside MS Access via VBA, with a split-database architecture:
`Frontend.accdb` holds the forms and VBA logic, while `Data_Backend.accdb` and
`Data_Log.accdb` store the raw data separately.

---

## Technology Stack

| Layer | Technology |
|---|---|
| Application | Microsoft Access (.accdb) with VBA |
| Data | MS Access Jet/ACE database (split frontend/backend) |
| Input / Output | Excel (.xlsx) files via COM automation |
| Language | VBA (Visual Basic for Applications) |
| Build tooling | PowerShell (`Build-AccessDemo.ps1`), Python (`validate_sources.py`, `generate_sample_data.py`) |

No external frameworks or third-party libraries were detected — this is a pure VBA
solution using only the built-in DAO objects, Excel COM automation, and Access built-ins.

---

## Architecture

The system follows a three-tier pattern: Excel serves as the data source and output
target, VBA in the Access frontend is the application layer, and the Access backend
databases handle persistence.

```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
```

### Data flow

1. A user opens **Frontend.accdb** and uses the import form to pick an Excel file.
2. **modImport** reads the file, creates a `clsTransaction` object for each row, validates
   it, and inserts the records into the appropriate backend table.
3. **modReconcile** queries both `tblTransactions` and `tblBankStatement` from the
   backend, performs matching (exact or fuzzy), and writes results to
   `tblReconcileResult`.
4. **modExport** reads the reconciliation results and generates an Excel report,
   flagging any rows with a non-zero variance.

### Database split

- **Frontend.accdb** — Forms, VBA code, and queries. Contains no user data.
- **Data_Backend.accdb** — Linked tables for `tblTransactions`, `tblBankStatement`,
  `tblImportBatch`, and `tblReconcileResult`. This is the core data store.
- **Data_Log.accdb** — Error logging via `tblErrorLog`.

---

## Module Guide

### `clsTransaction` — Transaction record model

This class module defines the domain model for a single financial transaction. It stores
fields for `TransID`, `Branch`, `TxnDate`, `Amount`, and `RefNo`. The `IsValid()` method
encapsulates the validation rules: the amount must be positive, the reference number must
be non-empty, the branch must be specified, and the date must be after 2000-01-01.

While the class definition itself is minimal, every imported row gets its own instance —
it is the primary building block used by `modImport` during data ingestion.

### `modImport` — Excel file import

This is the entry point for the reconciliation pipeline. `RunImport()` opens an Excel
workbook via late-bound COM automation, reads each row, constructs a `clsTransaction`
object, validates it, and inserts valid records into either `tblTransactions` or
`tblBankStatement` depending on whether the file is a bank statement (detected by the
filename pattern `BankStatement`). Invalid rows are counted and logged via `modUtil`.

The module also handles file-lock contention gracefully by retrying up to three times
before giving up, and provides a `BrowseForFile()` helper that opens the standard Windows
file picker dialog.

### `modReconcile` — Matching engine

This module implements the core reconciliation logic. `RunReconcile()` iterates over all
transactions in a given batch and, for each one:

- Checks for duplicate reference numbers within the same batch (marks as `"Duplicate"`).
- Attempts an **exact match** by `RefNo` against the bank statement table.
- Falls back to a **fuzzy match** if no exact match is found: the same amount with a
  transaction date within plus or minus one day.
- Marks any remaining unmatched rows as `"Unmatched"`.

Matched pairs get a variance computed as `branch_amount minus bank_amount`. All results
are persisted to `tblReconcileResult`.

### `modExport` — Report generation

After reconciliation completes, `modExport.RunExport()` queries the join between
`tblTransactions` and `tblReconcileResult`, opens a new Excel workbook through COM
automation, and writes the reconciled data into six columns: `RefNo`, `Branch`,
`TxnDate`, `Amount`, `MatchStatus`, and `Variance`. Rows with a non-zero variance are
highlighted in light red for quick visual scanning. The output file is saved to the
current project directory with a date-stamped name (e.g. `ReconcileReport_202607.xlsx`).

### `modUtil` — Shared helpers

This module provides cross-cutting utilities used across the pipeline:

- **`gCurrentBatchID`** — A public variable holding the ID of the most recently imported
  batch, so downstream modules know which data to work with.
- **`GetNextBatchNumber()`** — Generates sequential batch IDs by finding the maximum
  existing `BatchID` in `tblImportBatch` and incrementing.
- **`LogError()`** — Inserts error records into `tblErrorLog` with batch ID, row number,
  message, and timestamp.
- **`FormatDateForSql()`** and **`FormatNumberForSql()`** — Safely format dates and
  currency values for embedding directly in SQL text. The number formatter uses `Str()`
  to avoid locale-related decimal separator issues on non-US Windows installations.

---

## Getting Started

### 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.

### Recommended reading order

1. **README.md** in `ar-reconciliation-vba-access/` for a high-level introduction and
   demo context.
2. **`vba-source/clsTransaction.cls`** — The simplest file in the codebase. Read this
   first to understand the data model.
3. **`vba-source/modUtil.bas`** — Shared helpers used everywhere else. Understanding the
   SQL formatting functions here clarifies how data gets persisted.
4. **`vba-source/modImport.bas`** — The import pipeline. This is the main entry point
   for data ingestion and demonstrates the full flow from file read to database insert.
5. **`vba-source/modReconcile.bas`** — The reconciliation logic. Read this after
   modImport to understand how imported data gets matched against bank records.
6. **`vba-source/modExport.bas`** — The report generator. This is the last step in the
   data pipeline and produces the final output.
7. **`schema/backend_schema.sql`** — The database schema that backs all the modules above.
8. **`forms-spec.md`** — The form and control layout specification for the Access frontend.

### Trying it out

Open `Frontend.accdb` on a Windows machine with Access installed. Use the import form to
select one of the sample Excel files from the `sample-data/` directory, run the
reconciliation, and export the report. The sample files include deliberate edge cases
(duplicate reference numbers, rounding variances, a one-day date shift, and an unmatched
row) to demonstrate how the system handles real-world data quality issues.