An Access database or a workbook full of macros is often the real system of record. It was built by someone useful, it works, and it has become a risk. Replacing it goes best in phases, with the old system running until the new one has proved itself.
Why these systems become risky
- One person understands them, and that person may leave.
- They live on a file share or a laptop, and backups are informal.
- They break when a Windows or Office update changes something.
- They cannot serve more than a few users at once.
- Business rules sit in macros, queries and cell formulas that are not documented.
Step 1: audit what it does
List every table, query, form, report, macro and scheduled task. Interview the people who use it and write down each manual step around it. The result is a description of what the system really does, including rules that were never written down.
Step 2: rebuild, buy or mix
Compare a packaged product, a custom rebuild and a mix, using the audit as the requirement list. If a package covers the process with settings, buy it. If the workbook encodes something that makes your business different, rebuild that part.
Step 3: replace one function at a time
Start with the function that carries the most risk or costs the most time. Build it, load real data and run old and new side by side. Compare outputs until they agree for a period you choose, including at least one month-end close.
Step 4: migrate the data
- Move open transactions and current balances, and master data, always.
- Decide by rule what history to move and what to archive as read-only.
- Build an ID crosswalk from old codes to new ones and keep it.
- Reconcile to control totals: customer and item counts, on-hand quantity and value by item, open order lines, receivable and payable aging.
Step 5: cut over and keep the old one read-only
Freeze the old system on a stated date, load the final changes, and switch. Keep the old data available read-only so anyone can check a past figure.