# Ar-reconciliation-vba-access

## Overview

The `ar-reconciliation-vba-access` module is a self-contained Accounts Receivable reconciliation subpackage built in VBA for a Microsoft Access application. It ingests financial transaction data from Excel files, reconciles internal transactions against bank statement records, and produces reconciled report exports. It forms the core data-processing layer of the AR reconciliation workflow, handling the full lifecycle from file import through matching to report output.

The reconciliation process follows a clear three-phase pipeline:

1. **Import** — Load transactions from Excel files (both internal transactions and bank statements) into Access tables, with row-level validation and error logging.
2. **Reconcile** — Match internal transactions against bank statement records using a three-tier strategy (duplicate detection, exact match, fuzzy match), producing a reconciliation result set.
3. **Export** — Generate a color-coded Excel report summarizing match status and variance for each transaction.

## Sub-module Guide

This module contains a single VBA subpackage (`vba-source`) that is organized into five modules with a clear separation of concerns. Together, they form a linear data pipeline:

| Module | Responsibility |
|--------|----------------|
| [clsTransaction](ar-reconciliation-vba-access/vba-source/clsTransaction.cls) | Data model — represents a single financial transaction with validation rules |
| [modImport](ar-reconciliation-vba-access/vba-source/modImport.bas) | File ingestion — Excel file picker, COM-based file opening, row-by-row import |
| [modReconcile](ar-reconciliation-vba-access/vba-source/modReconcile.bas) | Matching engine — three-tier matching strategy (duplicate, exact, fuzzy) |
| [modExport](ar-reconciliation-vba-access/vba-source/modExport.bas) | Report generation — produces color-coded Excel output |
| [modUtil](ar-reconciliation-vba-access/vba-source/modUtil.bas) | Shared utilities — batch ID sequencing, SQL formatting, error logging |

### How the sub-modules relate

The sub-modules are arranged as a **linear processing pipeline**, where each stage feeds its output into the next:

```
[Excel File] → modImport → tblTransactions / tblBankStatement → modReconcile → tblReconcileResult → modExport → [Excel Report]
```

- **modImport** is the entry point. It reads Excel files (both internal transactions and bank statements), validates rows through **clsTransaction**, and writes them into the appropriate Access tables. It also manages batch tracking via **modUtil**.
- **modReconcile** consumes data from the transaction and bank statement tables, runs the matching engine, and writes results to `tblReconcileResult`. It depends on **clsTransaction** for data model semantics and **modUtil** for SQL formatting.
- **modExport** reads from `tblReconcileResult` joined with `tblTransactions` and produces the final Excel report. It depends on **modUtil** for SQL formatting and batch ID resolution.

All three processing modules share **modUtil** as a common utility layer — batch ID management, date/number formatting for SQL, and error logging flow through it. This keeps cross-cutting concerns in one place.

**modUtil.gCurrentBatchID** is the only piece of shared state between modules. It is set during import and consumed by reconcile and export, acting as the implicit pipeline token that ties all operations for a single reconciliation session together.

## System Architecture

### The Reconciliation Pipeline

```mermaid
flowchart TD
    A["Excel Source"] --> B["modImport"]
    B --> C["clsTransaction"]
    B --> D["tblTransactions"]
    B --> E["tblBankStatement"]
    B --> F["tblImportBatch"]
    B --> G["tblErrorLog"]
    B --> H["modUtil"]

    I["modReconcile"] --> C
    I --> H
    I --> D
    I --> E
    I --> J["tblReconcileResult"]

    K["modExport"] --> H
    K --> D
    K --> J
    K --> L["Excel Report"]
```

### Data Model

The module operates on five Access tables:

| Table | Purpose |
|-------|---------|
| `tblTransactions` | Internal transactions loaded from Excel (Branch, TxnDate, Amount, RefNo) |
| `tblBankStatement` | Bank statement transactions loaded from Excel (TxnDate, Amount, BankRefNo) |
| `tblReconcileResult` | Reconciliation outcome linking transactions to bank records with match status and variance |
| `tblImportBatch` | Tracks each import session (batch ID, filename, row count, status) |
| `tblErrorLog` | Logs validation failures per row (batch ID, row number, error message) |

### The Three-Tier Matching Strategy

The reconciliation engine (`modReconcile`) applies matching in priority order:

1. **Duplicate detection** — If the same `RefNo` appears multiple times in the transaction table, flagged as `"Duplicate"` with zero variance.
2. **Exact match** — `RefNo` in transactions equals `BankRefNo` in bank statement. Status: `"Matched"`.
3. **Fuzzy match** — Same `Amount` AND `TxnDate` within +-1 day. Status: `"Matched (Fuzzy)"`.

Unmatched transactions receive status `"Unmatched"` with variance equal to the full transaction amount.

## Key Patterns and Architecture

### Pipeline Pattern with Implicit State

The entire module follows a linear **import → reconcile → export** pipeline. The only state connecting stages is `modUtil.gCurrentBatchID` — a module-level global that identifies the current import batch. Each stage optionally accepts an explicit batch ID; when omitted, it falls back to the global. This pattern keeps the interface simple at the cost of a shared mutable variable.

### Validation-as-Gatekeeper

`clsTransaction.IsValid()` acts as the gate for the import pipeline. Rows that fail validation (zero amount, blank reference, empty branch, or pre-2000 date) are rejected and logged to `tblErrorLog`. This ensures data quality at the entry point rather than propagating bad records into the matching engine.

### Late-Bound COM Automation

Both import and export use late-bound COM automation to interact with Excel (`CreateObject("Excel.Application")`). This avoids a compile-time dependency on the Excel object library but requires using magic numbers for Excel constants (e.g., `-4162` for `xlUp`, `51` for `xlOpenXMLWorkbook`). The code also implements a retry loop (up to 3 attempts with 2-second sleep) to handle file-locking errors during import.

### SQL Injection via String Concatenation

All SQL statements are built through string concatenation, with only single-quote escaping on string values (`Replace(…, "'", "''")`). There is no parameterized query usage. This is a known VBA constraint but represents a risk if reference numbers or branch codes could contain malicious input.

### Locale-Safe Numeric Formatting

`modUtil.FormatNumberForSql()` addresses a well-known VBA pitfall: `Currency` values concatenated as strings pick up the system locale's decimal separator (comma on Vietnamese Windows, dot on US Windows). The function uses `Str()` + `Trim()` to force a dot decimal separator, ensuring SQL statements parse correctly regardless of locale.

## Dependencies and Integration

### Internal dependencies

The subpackage is self-contained within the Access application. Its only dependencies are:

- **Microsoft Access runtime** — the host application.
- **Microsoft Excel (COM)** — used for file reading during import and report generation during export.

### External integrations

- **Excel files** — the module reads from `.xlsx` files and writes reconciliation reports to `.xlsx` files. The file format is external and not version-controlled.
- **Windows API** — file picker uses `msoFileDialogFilePicker` (Office UI library) and the retry loop uses `Sleep` (Windows kernel32 API).

## Notes for Developers

### VBA-specific conventions and gotchas

- **Magic numbers for Excel constants**: Since late binding is used, named constants like `xlUp` are unavailable. Use their numeric values (`-4162`, `51`, etc.) directly in the code.

- **Module-level globals**: `modUtil.gCurrentBatchID` is the sole shared state between modules. If the application ever runs concurrent imports, this global becomes a race condition.

- **Recordset cleanup**: In `modReconcile.RunReconcile()`, the `rsBank` recordset is opened conditionally. Calling `Close` twice (once after exact match, once after fuzzy match) is tolerated by VBA but is fragile — if the recordset is already closed, the call silently succeeds.

- **Row 2 anchor**: During import, row 2 is used as the anchor for `lastRow` detection because column 1 (Branch) is intentionally blank in bank statement files. Using `xlUp` on column 2 (TxnDate) avoids the edge case where column 1 reports no last-used row.

- **Error handling scope**: `On Error Resume Next` is scoped to the file-opening retry loop only. Row-by-row inserts use implicit error handling — if `db.Execute` fails on a single row, the entire import crashes. Wrapping the inner `db.Execute` in `On Error Resume Next` would allow partial imports.

### Extension points

- **Fuzzy matching**: The current fuzzy match only checks amount equality and a +-1 day date window. Additional strategies (fuzzy string matching on `RefNo`, amount tolerance) could be added to `modReconcile` for better match rates.

- **Export location**: `modExport.RunExport` hard-codes the output path pattern. Making it configurable or prompting the user for a save location would improve usability.

- **Validation threshold**: The date threshold in `clsTransaction.IsValid()` (`2000-01-01`) is a hardcoded magic date. Consider making this configurable.

- **Batch ID strategy**: `modUtil.GetNextBatchNumber` uses a simple max-plus-one approach. Using an AutoNumber field in `tblImportBatch` would eliminate gap/duplicate risks.

- **Connection string**: The connection string used to open the Access database is defined in `modUtil`. If the database is moved, this value needs updating.