Replacing Access & Excel Macro Apps With a Web App
Many profitable businesses still run a critical process on a Microsoft Access database or an Excel workbook full of VBA macros. It started as a smart shortcut. Years later it holds the pricing logic, the customer history
Many profitable businesses still run a critical process on a Microsoft Access database or an Excel workbook full of VBA macros. It started as a smart shortcut. Years later it holds the pricing logic, the customer history and the month-end report, it lives on one shared drive, and only one person really understands it. Replacing it is not a rewrite of screens. It is a migration of data, hidden business rules and daily habits, and that is where most projects fail.
At Loop Origin I build custom web applications on Laravel, Vue, Nuxt and PostgreSQL, and as a Principal Software Engineer I have moved several of these spreadsheet-era systems to the browser. I wrote this with help from AI tools and checked every command and code sample myself. This is the playbook I use.
How do you replace an Access or Excel macro app with a web app?
Direct answer: Inventory every table, form, query, macro and report first. Extract the data with scripted, repeatable imports, and capture each business rule as a test before rewriting it. Build the web app in small modules, run it in parallel with the old file, reconcile outputs daily, then cut over one team at a time and freeze the legacy file as read-only.
Signs it is time to move
You do not need to migrate just because Access is old. Move when the risk is real:
- More than a few people need to edit at once, and you see locking errors or "who has the file open?" messages.
- The file is corrupted or restored from backup more than once a year.
- Remote staff or branches need access, and you are passing copies by email or VPN.
- An auditor or customer asks who changed a value, and you cannot answer.
- The one person who knows the VBA is leaving, or already left.
- Integrations (accounting, e-commerce, couriers) are done by copy-paste.
Your options compared
| Option | Best for | Main strengths | Main risks | Ongoing cost drivers |
|---|---|---|---|---|
| Keep Access, move tables to SQL Server or PostgreSQL | Small teams on one office network | Fast, little retraining | Front end still on desktops; VBA knowledge risk stays | Desktop licences, one maintainer |
| Low-code (Power Apps, AppSheet and similar) | Simple forms and approvals inside one vendor's ecosystem | Quick to build, built-in hosting | Per-user licensing, limits on complex logic, lock-in to the platform | Licences grow with every user |
| Off-the-shelf SaaS | The process is standard (CRM, helpdesk, basic inventory) | No build, vendor maintains it | Your special rules may not fit; data export limits | Subscription per seat or tier |
| Custom web app (Laravel/PostgreSQL + Vue or Nuxt) | Core processes with unique rules and integrations | You own the code and data; fits the real workflow; any device | Needs a disciplined migration and a maintainer | Hosting, maintenance retainer |
I usually recommend custom only when the logic is the business's edge or the integrations are complex. If a standard SaaS covers the process, buy it.
Step 1: Inventory the legacy app
Treat the Access file or workbook as a system with parts, not a document. For Access, the open-source mdbtools package reads .mdb and .accdb files on Linux:
# List tables, dump the schema, export each table to CSV
mdb-tables -1 legacy.accdb
mdb-schema legacy.accdb postgres > schema_from_access.sql
for t in $(mdb-tables -1 legacy.accdb); do
mdb-export legacy.accdb "$t" > "export/${t}.csv"
done
# Saved queries (newer mdbtools versions)
mdb-queries -L legacy.accdb
Forms, reports and VBA modules do not come out cleanly this way. Open the file in Access, use the Database Documenter, and export each VBA module to text from the editor. For Excel, list every sheet, named range, formula-heavy column, pivot and macro.
Record the results in an inventory sheet:
| Item | Type | Used by | How often | Business rule inside? | Keep, change or drop |
|---|---|---|---|---|---|
| tblOrders | Table | Sales, Finance | Daily | No | Keep |
| qryMonthEndCommission | Query | Finance | Monthly | Yes: commission tiers | Keep, test first |
| frmQuickQuote | Form + VBA | Sales | Daily | Yes: discount caps | Change |
| rptOldBranchSummary | Report | Nobody since 2023 | Never | No | Drop |
Expect to drop a real share of the old reports. Ask who opened each one in the last year.
Step 2: Turn hidden rules into tests
The dangerous logic hides in VBA event handlers, query expressions and cell formulas: discount caps, rounding, tax, commission tiers, status changes. Before rewriting any of it, write down known inputs and outputs from the legacy system and turn them into tests. These are characterization tests: they pin down what the old system does today, bugs included, so you can choose which behavior to keep. I wrote about the technique in Characterization Tests Before You Refactor AI-Written Code.
// tests/Unit/CommissionTest.php (Pest)
// Each row: [monthly sales, expected commission] taken from the legacy query's output
dataset('legacy_commission', [
[0, 0.00],
[49999, 1499.97],
[50000, 2500.00],
[120000, 7200.00],
]);
it('matches the legacy month-end commission', function (int $sales, float $expected) {
expect(app(\App\Services\Commission::class)->for($sales))->toBe($expected);
})->with('legacy_commission');
Take the expected values from real runs of the legacy file, not from someone's memory of the rules.
Step 3: Design the new data model, then import repeatably
Access schemas often carry years of drift: text columns holding dates, duplicate customers, "Yes/No" stored three ways, and no foreign keys. Do not copy that into PostgreSQL. Design a clean schema, then write an import that maps and cleans the legacy data, and run it again and again until it is boring.
// app/Console/Commands/ImportLegacyOrders.php (excerpt)
public function handle(): int
{
$rows = SimpleExcelReader::create(storage_path('legacy/tblOrders.csv'))->getRows();
$bad = 0;
DB::transaction(function () use ($rows, &$bad) {
foreach ($rows as $r) {
$date = Carbon::createFromFormat('m/d/y H:i:s', $r['OrderDate'], 'Asia/Karachi');
$customer = Customer::firstWhere('legacy_id', $r['CustomerID']);
if (! $date || ! $customer) { $bad++; Log::warning('skip', $r); continue; }
Order::updateOrCreate(
['legacy_id' => $r['OrderID']],
['customer_id' => $customer->id, 'ordered_at' => $date,
'total' => round((float) $r['Total'], 2)]
);
}
});
$this->info("Imported with {$bad} rejected rows");
return self::SUCCESS;
}
Two habits matter. Keep a legacy_id on every migrated row so you can trace any record back. And make the import idempotent with updateOrCreate, so you can rerun it on fresh exports during the parallel run. Check the date format against your own export, because mdb-export output depends on version and options.
Step 4: Build in modules, not one big bang
Pick the module that hurts most, often order entry or approvals, and ship that first while the rest stays in the old file. Add the things a shared file never had: proper roles and permissions per team and branch, and a record of who changed what. I covered both in Laravel RBAC for Multi-Organization Portals and Immutable Audit Trails in Postgres for SaaS.
Keep the screens familiar at first. Users who spent ten years on one Access form will accept a cleaner web form with the same field order far faster than a redesign.
Step 5: Parallel run and reconcile
For two to four weeks, both systems run. Import fresh legacy data every night, and compare the outputs that matter: order totals by day, invoice counts, commission per salesperson, stock on hand.
-- Daily reconciliation: new system vs nightly legacy import
SELECT d.day,
l.total AS legacy_total,
n.total AS new_total,
n.total - l.total AS diff
FROM generate_series(current_date - 30, current_date, '1 day') AS d(day)
LEFT JOIN legacy_daily_totals l ON l.day = d.day
LEFT JOIN (
SELECT ordered_at::date AS day, SUM(total) AS total FROM orders GROUP BY 1
) n ON n.day = d.day
WHERE COALESCE(n.total, 0) <> COALESCE(l.total, 0);
Every difference is either a bug in the new app, a bug in the old one, or a data-entry mistake. Decide each one with the business owner and write the decision down.
Step 6: Cut over and freeze
Move one team or branch at a time. On cutover day, run the final import, set the legacy file to read-only, and keep it archived with its exports. Do not delete it for at least one audit cycle.
Failure modes I see most
| Failure | What causes it | Prevention |
|---|---|---|
| Totals differ by small amounts | Different rounding between VBA, Excel and PHP | Test rounding rules explicitly; store money as integers or fixed decimals |
| Dates shift by a day | Time zones and text dates in the legacy file | Parse with an explicit format and time zone; reconcile by day |
| Users keep the old file "just in case" | No read-only freeze, no trust in the new numbers | Freeze on cutover; share reconciliation results |
| A forgotten monthly report breaks finance | Inventory skipped rarely-used items | Ask finance and management to sign off the inventory |
| Scope doubles mid-project | Every wish list item added "while we are at it" | Phase 1 replaces what exists; improvements come after cutover |
Migration checklist
- Inventory every table, query, form, report, macro and integration, with an owner for each.
- Mark each item keep, change or drop, signed off by the business.
- Write characterization tests from real legacy outputs for every business rule.
- Design a clean schema; map every legacy column to it or to "dropped".
- Build an idempotent import with
legacy_idtraceability and a rejected-row log. - Ship the highest-pain module first, with roles and an audit trail.
- Run in parallel with nightly imports and a daily reconciliation report.
- Cut over team by team, then freeze and archive the legacy file.
- Train users on the screens they use, not on the whole system.
- Agree on a maintenance owner before go-live.
FAQs
How long does an Access to web app migration take?
It depends on the number of forms, rules and integrations, not file size. A focused single-process app can move in weeks; a database running several departments is a phased project. The inventory in Step 1 is the only honest basis for an estimate.
Can we keep using Excel for reporting?
Yes. Many teams are happiest when the web app is the source of truth and exports clean Excel files or a read-only reporting connection. Just stop editing master data in spreadsheets.
What happens to the VBA code?
It becomes requirements and tests, not code you port line by line. Read it to find the rules, capture them as tests, then implement them cleanly in the new app.
Is moving Access tables to SQL Server enough?
Sometimes, for a small team on one network. It fixes storage and multi-user locking but not remote access, audit history or the single-maintainer risk in the forms and VBA.
Should we choose low-code instead of custom?
If the process is simple and you already pay for that vendor's ecosystem, low-code can be the right choice. Choose custom when the rules are complex, the integrations matter, or per-user licences will grow with your team.
How do we avoid losing data during cutover?
Run the import many times before cutover, keep a rejected-row log at zero or explained, reconcile daily during the parallel run, and archive the final legacy file read-only.
The short version
Treat the Access file or macro workbook as a real system. Inventory it, test its hidden rules, import its data repeatably, build in modules, prove the numbers with a parallel run, then cut over and freeze. The screens are the easy part; the rules and the data are the project.
Follow & Connect
- LinkedIn: Loop Origin
- X: Muhammad Ali Mughal (@AliMughal887)
- GitHub: Loop Origin (@LoopOrigin)
Watch on YouTube: Resume Boost AI ยท PixelValut ยท Daily AI News By Ali
Originally published by Dev.to WebDev. Aggregated on AIWithGhost for educational purposes โ full credit and traffic to the original publisher.