# Repository Overview

Welcome! This repository contains **AR-Reconciliation-Tool**, a VBA + MS Access application that automates the reconciliation of branch-level transaction exports against bank statements. If you are new to this codebase, you are about to explore a self-contained demo built to illustrate how legacy VBA-based financial reconciliation tools operate end-to-end — from Excel import through matching logic to a polished Excel report output. Think of it as a complete, runnable mini-system that models the workflow customers used before migrating to modern platforms.

## Overview

This tool simulates an Accounts Receivable (AR) reconciliation workflow used by financial teams. The system performs three core operations in sequence:

1. **Import** — Read transaction data from Excel files (branch exports or bank statements), validate each row, and persist it into an MS Access backend database.
2. **Reconcile** — Match imported branch transactions against bank statement records using exact reference-number lookup, with a fuzzy fallback for amount + date range.
3. **Export** — Generate a formatted Excel report (`.xlsx`) summarizing all transactions, their match status, and any variance, with color-coded highlighting for discrepancies.

The architecture separates the frontend (forms + VBA logic in `Frontend.accdb`) from the data layer (`Data_Backend.accdb` and `Data_Log.accdb`), a common pattern in the Access ecosystem.

## Technology Stack

| Category        | Technology                          |
|-----------------|-------------------------------------|
| Language        | VBA (Visual Basic for Applications) |
| Database        | MS Access (Jet/ACE engine)          |
| Data interchange| Excel (.xlsx via COM Automation)    |
| OS              | Windows (COM + FileDialog APIs)     |

No well-known external frameworks were detected. The project relies on:

- **DAO (Data Access Objects)** — for database operations (recordsets, queries).
- **Microsoft Excel COM** — `CreateObject("Excel.Application")` for late-bound Excel automation.
- **Windows Kernel32** — `Sleep` API for retry-backoff when opening locked files.
- **VBA Class module** — `clsTransaction` for the transaction data model.

## Architecture

The system follows a linear pipeline: **Import → Reconcile → Export**, with `modUtil` providing shared utilities to every phase.

```mermaid
flowchart LR
    subgraph Import["Import Phase"]
        modImport["modImport<br/>Excel to Access"]
        clsTransaction["clsTransaction<br/>Transaction model"]
    end

    subgraph Reconcile["Reconcile Phase"]
        modReconcile["modReconcile<br/>Match matching"]
    end

    subgraph Export["Export Phase"]
        modExport["modExport<br/>Access to Excel report"]
    end

    modUtil["modUtil<br/>Shared utilities"]

    modImport --> clsTransaction
    modImport --> modUtil
    modImport --> modReconcile
    modReconcile --> modUtil
    modExport --> modUtil
```

The data lives in two Access databases:

- **Data_Backend.accdb** — holds the core reconciliation tables (`tblTransactions`, `tblBankStatement`, `tblReconcileResult`, `tblImportBatch`).
- **Data_Log.accdb** — stores audit/error logs (`tblErrorLog`).

### Matching strategy

The reconciliation module employs a two-tier matching approach:

- **Exact match** — a branch transaction's `RefNo` is looked up against the bank statement's `BankRefNo`.
- **Fuzzy fallback** — if no exact match is found, the system looks for a bank record with the same amount and a transaction date within +-1 day.
- **Duplicate detection** — if a `RefNo` appears more than once in the transactions table for the same batch, it is flagged as `"Duplicate"`.

## Module Guide

### `clsTransaction.cls` — Transaction Data Model

A class module representing a single financial transaction. It exposes five public fields (`TransID`, `Branch`, `TxnDate`, `Amount`, `RefNo`) and an `IsValid()` method that validates that the amount is positive, the reference number is non-empty, a branch is set, and the date is after January 1, 2000. Instances of this class are created for each row in the import source and are the primary data unit flowing through the pipeline.

### `modImport.bas` — Excel Import Engine

The entry point for the system. `RunImport()` orchestrates the full import workflow:

1. Prompts the user to select an Excel file via a Windows file picker.
2. Detects the file type (bank statement vs. branch transaction) by examining the filename.
3. Assigns a new batch number (via `modUtil.GetNextBatchNumber`) to track which rows belong together.
4. Opens the Excel workbook through COM automation, with a retry-and-wait loop for locked files (up to 3 attempts).
5. Iterates over each row, instantiating a `clsTransaction` for validation, then inserts valid rows into either `tblBankStatement` or `tblTransactions` depending on file type.
6. Logs invalid rows to `tblErrorLog` and writes a summary to the UI status label.

`BrowseForFile()` provides the UI helper for selecting input files.

### `modReconcile.bas` — Reconciliation Engine

The heart of the business logic. `RunReconcile()` clears any prior results for the current batch, then iterates over every transaction record:

- Checks for duplicate `RefNo` values within the batch (flagged as `"Duplicate"`).
- Attempts an exact match against `tblBankStatement` by `BankRefNo`.
- Falls back to a fuzzy match: same amount, transaction date within +-1 day.
- Records each outcome in `tblReconcileResult` with the appropriate status and computed variance (transaction amount minus bank statement amount).

`InsertResult` is a private helper that builds and executes the INSERT statement for the results table.

### `modUtil.bas` — Shared Utilities

A module of cross-cutting helpers used by every other module:

- `gCurrentBatchID` — a global variable tracking the active import batch.
- `GetNextBatchNumber()` — queries `tblImportBatch` for the maximum existing batch ID and returns the next sequential number.
- `LogError()` — records validation failures into `tblErrorLog`.
- `FormatDateForSql()` — wraps a VBA `Date` into Jet SQL date literal syntax (`#mm/dd/yyyy#`).
- `FormatNumberForSql()` — converts a `Currency` value to a SQL-safe string using `Str()` to avoid locale-dependent decimal separator issues (e.g., comma vs. period).

### `modExport.bas` — Report Export

Generates a reconciled report in Excel format. `RunExport()` queries the joined result of `tblReconcileResult` and `tblTransactions` for a given batch, creates a new Excel workbook via COM, populates six columns (RefNo, Branch, TxnDate, Amount, MatchStatus, Variance), highlights rows with non-zero variance in light red, auto-fits columns, and saves the file as `ReconcileReport_yyyymm.xlsx` in the Access project directory. Returns the output file path.

## Getting Started

If you are exploring this codebase for the first time, here is a recommended reading order:

1. **`README.md`** — Start here for the full context, including how to build and run the demo, and how it fits into the broader customer migration program.

2. **`schema/backend_schema.sql`** — The Access database DDL defines the five core tables and their columns. Understanding this schema makes the VBA code much easier to follow.

3. **`vba-source/modUtil.bas`** — The smallest and most self-contained module. Reading this first gives you the shared helpers that the other modules depend on.

4. **`vba-source/clsTransaction.cls`** — The single class module; a short, focused read that defines the domain model.

5. **`vba-source/modImport.bas`** — The import engine. Follow `RunImport()` line by line to understand how data flows from Excel into the database.

6. **`vba-source/modReconcile.bas`** — The reconciliation logic. This is the most complex module; step through `RunReconcile()` to understand the matching strategy.

7. **`vba-source/modExport.bas`** — The export module. A straightforward read that shows how data is rendered into a user-facing Excel report.

### Quick start

To try the application, you will need a Windows machine with MS Access installed:

1. Open `Frontend.accdb` in MS Access.
2. Use the Import form to select an Excel transaction file.
3. Click the Reconcile button to run matching logic.
4. Click the Export button to generate the reconciliation report.

The `build/` directory contains the PowerShell build script (`Build-AccessDemo.ps1`) and a Python-based validator (`validate_sources.py`) to ensure consistency between VBA code, the schema, and the form specifications.