Replacing a Microsoft Access database or Excel macro tool

Direct answer

If a critical process runs on an Access database or a macro-driven Excel workbook that one person built and only that person really understands, replacing it with a properly built web application is usually worth planning before it fails, not after. The safe route is to extract what the tool actually does, including rules hidden in macros, design a clean data model, rebuild the workflows people use, migrate the data with reconciliation and run old and new side by side before retiring the original.

Access, Excel and VBA are named here only as common examples of the legacy tool. We make no claims about their features, limits or pricing. Two honest limits apply to any replacement: we do not promise automatic conversion of your file, and we cannot promise the new tool behaves identically in every corner, because the old behaviour includes accidents nobody chose. What we can do is make the intended behaviour explicit and tested. For the general question of moving off spreadsheets see replacing spreadsheets with internal tools.

Signs it is time

Most teams recognise several of these long before they act.

• Only one person (or nobody still at the company) can safely change it
• It lives on one machine or a shared drive folder with informal backups
• Multiple people need to use it at the same time and take turns, or file copies circulate
• Something is done by hand before or after it runs, such as copying results into another file
• Access control is a folder permission, so everyone who can open it can see everything
• It breaks after an operating system or Office update and someone fixes it under pressure
• New requirements are refused because nobody dares touch it

Rules hiding in macros

The most valuable and most dangerous content of a legacy tool is the logic nobody documented: macros, formulas, query criteria, form validation, hidden columns and hard-coded values. A price adjustment for a certain customer type, a rounding habit, a check that blocks a certain combination: these are business rules that only exist because someone once needed them.

Treat them as requirements to be recovered and confirmed, not code to be translated line by line. For each one, write down what it does in plain language, why it exists (ask the person who uses the result), whether it is still wanted, and an example input with the expected output. Rules nobody can explain and nobody misses are candidates to drop; rules people depend on become tests.

Extraction: learn what the tool really does

Before any design, build an inventory from the working system, not from memory.

• List every table, sheet and query, with fields, types and approximate record counts
• List every form, report, macro and button, and who uses each and how often
• Record inputs and outputs: what comes in from other files or systems, and what goes out by email, print or export
• Watch two or three real users perform their normal week, including the manual steps around the tool
• Collect a set of real worked examples, with inputs and correct outputs, to use later as acceptance tests
• Note what breaks, is slow, or is avoided today; those are the priorities for improvement

The new data model

Legacy databases often carry years of accumulated shortcuts: repeated columns, free-text where a list should be, and records with no reliable identifier. Design the new data model around the business entities and their relationships rather than copying the old structure. Decide stable keys, controlled lists for categories and statuses, and where history must be kept instead of overwritten.

Keep a mapping from every old field to its new home or to an explicit decision to drop it. That mapping is the backbone of the migration and the answer to the question people will ask later: where did this column go?

Users and permissions

A file on a shared drive gives everyone the same access. A web application can give each role only what it needs. Decide who enters data, who approves, who only views and who administers, and which records or fields are sensitive. Include the people who receive only the outputs; they often get a report view instead of a copy of the file. The principles are described in roles and permissions for web applications. Connecting to a company sign-in system is not assumed and would be scoped separately.

Migration and parallel run

Migrate the data using written cleaning rules, then reconcile counts and key totals against the source and review a sample with the people who know the records. Keep a list of rows that did not import and why. The procedure is covered step by step in the guide to spreadsheet to database migration, and much of it applies to Access data as well.

Then run both systems for a defined period on real work, comparing outputs on the worked examples you collected. Differences are either bugs in the new tool, rules you missed, or errors in the old one; each needs a decision. Retire the legacy file only after that period, make it read-only and archive it.

What to preserve, and what to leave behind

Preserve the outputs people depend on, such as the report layouts and the exports other systems or clients receive, until they choose to change them; changing the process and the tool at the same time multiplies risk. Preserve the business rules that were confirmed and the full history of the data. Leave behind the workarounds, duplicated columns, manual copy steps and fragile file handling that existed only because the old tool could not do better.

If you are weighing a hosted no-code or low-code alternative first, read low-code vs custom internal tool. When you are ready to scope a replacement, the parent page is internal business tools, and the automation opportunity finder can help you see which surrounding manual steps to remove at the same time.