# Repository Overview

Welcome! This repository houses an **Access VBA application** for automating financial transaction reconciliation. It reads batch transaction data from Excel files, matches those transactions against bank statements, and produces a color-coded Excel report summarizing the results. If you are new here, think of this as a three-step pipeline: **import**, **reconcile**, then **export** — all driven from within Microsoft Access via a simple user form.

This is a small, focused codebase (one VBA module package with five members) designed for single-user use on shared Access backends. It trades enterprise-scale patterns for simplicity and directness, which makes it a great place to learn VBA data-pipeline patterns — and a good model for what *not* to do at larger scale (e.g., implicit state, string-concatenated SQL).

## Overview

The application implements a **batch reconciliation pipeline** for financial data. A user opens an Access form, selects an Excel file (either an internal transaction file or a bank statement), and the system:

1. **Imports** rows from the Excel file into Access tables, validating each row along the way.
2. **Reconciles** internal transactions against bank statements using three matching tiers: exact reference-number match, fuzzy date-plus-amount matching, and a fallback \"unmatched\" flag.
3. **Exports** the reconciliation results to a fresh Excel workbook, with rows showing non-zero variance highlighted in light red.

Everything is orchestrated from a single form (`frmImport`) and flows through a chain of VBA modules. A shared utility module (`modUtil`) tracks the current batch ID and provides SQL-safe formatting helpers.

## Technology Stack

| Category | Detail |
|---|---|
| **Language** | VBA (Visual Basic for Applications) |
| **Host Application** | Microsoft Access (backend database) |
| **Database Access** | DAO (`CurrentDb`, `DAO.Recordset`) |
| **File I/O** | Microsoft Excel (late binding via `CreateObject`) |
| **File Format** | `.xlsx` (Excel Open XML) |
| **User Interface** | Access form (`frmImport`) |
| **External API** | Windows `Sleep` (64-bit `PtrSafe` declaration) |

No external frameworks or third-party libraries were detected. The code relies entirely on VBA's built-in DAO library and late-bound Excel automation.

## Architecture

The codebase follows a straightforward three-phase pipeline, with a shared utility module at its center:

```mermaid
flowchart TD
    subgraph Modules["VBA Modules"]
        modImport["modImport"]
        modReconcile["modReconcile"]
        modExport["modExport"]
        modUtil["modUtil"]
        clsTransaction["clsTransaction"]
    end

    subgraph Pipeline["Pipeline Flow"]
        ImportPhase["Import"]
        ReconcilePhase["Reconcile"]
        ExportPhase["Export"]
    end

    clsTransaction --> modImport
    modImport --> ImportPhase
    ImportPhase --> ReconcilePhase
    modImport --> modUtil
    modReconcile --> ReconcilePhase
    modReconcile --> modUtil
    modExport --> ExportPhase
    modExport --> modUtil
    ReconcilePhase --> ExportPhase
```

The pipeline runs sequentially: import feeds reconcile, which feeds export. Each module depends on `modUtil` for shared helpers, and `clsTransaction` provides the data model consumed during import.

**Key design notes:**

- `modUtil` is the leaf of the dependency graph — it has no internal VBA dependencies.
- `modImport` is the entry point: it creates the data that both `modReconcile` and `modExport` consume.
- Implicit state flows through `modUtil.gCurrentBatchID`, a module-level variable set at the end of import and read by the downstream phases. This means phases must run in order for the same data.
- All Excel interaction uses late binding, so no compile-time reference to the Excel library is required. This means the code works across Excel versions but loses IntelliSense and compile-time type checking.

## Module Guide

### vba-source — Transaction Reconciliation Pipeline

The `vba-source` module is the only module package in this repository. It contains the full reconciliation pipeline as five VBA members: one class module and four standard modules.

**Key members:**

- **`clsTransaction`** — The single data model. Represents one financial transaction with fields for `TransID`, `Branch`, `TxnDate`, `Amount`, and `RefNo`. Exposes an `IsValid()` method that enforces business rules: amount must be positive, reference number must be non-empty, branch must exist, and date must be on or after January 1, 2000. Invalid rows are logged but not persisted.

- **`modImport`** — Orchestrates the import phase. Provides `RunImport()`, which opens an Excel file via a retry loop (handles file-lock contention with up to 3 attempts), iterates rows, validates each with `clsTransaction.IsValid()`, and inserts valid rows into either `tblTransactions` or `tblBankStatement` depending on whether the filename contains \"BankStatement.\" Also provides `BrowseForFile()`, which opens a file-picker dialog for selecting the Excel file.

- **`modReconcile`** — Performs the matching logic. `RunReconcile()` iterates through imported transactions and attempts three matching tiers against bank statements: (1) exact `RefNo` match, (2) fuzzy match on amount within +-1 day, and (3) an \"unmatched\" flag. Results are written to `tblReconcileResult` with status labels like \"Matched,\" \"Matched (Fuzzy),\" or \"Unmatched.\"

- **`modExport`** — Generates the final reconciliation report. Reads from `tblReconcileResult` joined with `tblTransactions`, writes an Excel workbook with columns for `RefNo`, `Branch`, `TxnDate`, `Amount`, `MatchStatus`, and `Variance`, highlights variance rows in light red, and saves the report as `ReconcileReport_yyyymm.xlsx`.

- **`modUtil`** — The shared utility layer. Maintains `gCurrentBatchID` as implicit state between phases, provides `GetNextBatchNumber()` for batch ID generation, `LogError()` for error logging, and SQL-safe formatting helpers (`FormatDateForSql`, `FormatNumberForSql`) that handle locale-specific decimal separators.

The module appears to be designed for a single-user workflow: the implicit state variable means concurrent access would cause batch ID collisions, so it is best suited for individual reconciliation tasks rather than multi-user batch processing.

## Getting Started

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

1. **Start with `.codewiki/vba-source.md`** — This is the only module page and provides the complete picture of the reconciliation pipeline, including data model, table schemas, and known limitations.

2. **Read `clsTransaction.cls` next** — At 5 public fields and one validation method, this is the smallest and most self-contained member. It defines the data shape that everything else operates on.

3. **Then read `modImport.bas`** — This is the entry point of the pipeline. Understanding how rows are read from Excel and validated will give you the foundation for understanding everything downstream.

4. **Follow with `modReconcile.bas`** — Now that you understand the data shapes, the matching logic (exact match, fuzzy fallback, unmatched flag) will be easy to follow.

5. **Finish with `modExport.bas` and `modUtil.bas`** — Export is short and visual; `modUtil` is the supporting glue. Reading them last makes sense because their helpers are used throughout the earlier modules.

### Recommended reading order summary

| Step | Member | Why |
|---|---|---|
| 1 | `.codewiki/vba-source.md` | Full pipeline overview with data model and table schemas |
| 2 | `clsTransaction.cls` | Data model and validation rules |
| 3 | `modImport.bas` | Pipeline entry point — file reading, validation, table insertion |
| 4 | `modReconcile.bas` | Matching logic — how transactions meet bank statements |
| 5 | `modExport.bas` | Report generation — turning results into an Excel file |
| 6 | `modUtil.bas` | Shared utilities — the glue holding it all together |

### Things to keep in mind

- **Run inside Access.** The modules cannot execute outside the Access host — they reference forms (`frmImport`) and use `CurrentProject` / `CurrentDb` which only exist in the Access runtime.
- **One batch at a time.** Because `gCurrentBatchID` is a global variable, importing data from two users simultaneously will cause collisions. The pipeline is designed for single-user workflows.
- **Locale matters.** Number formatting uses `Str()` to avoid locale-specific decimal separators — do not swap in `CStr` without re-testing on non-US regional settings.
- **SQL is concatenated, not parameterized.** The code is vulnerable to SQL injection if transaction data contains crafted payloads. This is a known limitation documented in the module page.

### Database tables (for context)

| Table | Purpose |
|---|---|
| `tblImportBatch` | Tracks each import run with file name, date, and row count |
| `tblTransactions` | Imported internal transaction records |
| `tblBankStatement` | Imported bank statement records |
| `tblReconcileResult` | Reconciliation outcomes (matched, fuzzy, unmatched) |
| `tblErrorLog` | Validation and import errors |

---

This repository is intentionally small — five members and a handful of tables — but it demonstrates real-world patterns for data import, matching, and reporting in a VBA context. Use it as a reference point for understanding how transaction reconciliation workflows can be automated, and as a case study in the trade-offs of small-scale, single-user VBA applications.