Retiring Microsoft Access Without Stopping the Shop

We get a very specific kind of call. There’s an Access database, built years ago by somebody who has since left, running something the business can’t stop doing. It works on one PC, and that PC is old. Everybody knows it’s a problem, and nobody wants to be the one who breaks it.

The problem is solvable and the path is well worn. How you walk that path matters more than the technology you end up on.

Why it got this way

Access is good at what it was built for. Somebody in the business who understood the work built exactly what the work needed, with no budget and no project. That’s a success story, and it explains why the database fits so well. A user built it straight from the work, with no requirements document in between.

Access struggles with twenty years of use, several people working at once, remote access, and upkeep by somebody who didn’t write it. Those problems arrive slowly, long after the original choice made sense.

The database did its job for years, and the business grew past what it was built to handle.

Step one: find out what’s in there

Before anything else, we document the whole thing. That means every table, query, form, report and macro, including the ones nobody knew were still running.

This stage always turns up surprises. There are queries feeding reports nobody reads, and a form that’s the only way one task gets done, used twice a year by one person. Business rules turn up buried in VBA with no record anywhere else. They aren’t in a policy document or in anyone’s memory, only in the code.

That last category carries the real risk in these projects. Rebuild without finding those rules and the new system comes out subtly wrong, in a way that surfaces three months later during an audit.

Step two: look hard at the data

Twenty years of data entry leaves residue. Dates get stored as text. The same customer shows up under four spellings, and somebody started using a "notes" field to hold a second phone number. Orphaned records point at things that were deleted.

We find these and decide on them before migration starts. Every ambiguous case needs a ruling from somebody in the business, and we write the rulings down. It’s the least interesting phase, and it decides whether the numbers match at the end.

Step three: blueprint, and agree it before code

We map the old structure to the new one: which tables merge, which split, what gets normalised, and what stays denormalised because the reporting depends on it. We also decide which screens get rebuilt as they are and which get improved. Improvements get chosen deliberately at this stage, so they don’t pile up during the build.

You approve the blueprint before development starts. After that, changing your mind gets expensive.

Step four: migrate, prototype, and let people click it

The data moves with validation at both ends: row counts, sums on key numeric columns, and spot checks on records people recognise. Then a working front end goes in front of real users, running against real records, before anyone commits to it.

Ten minutes of clicking turns up things no specification review catches. That’s why the checkpoint exists, and it’s why prototypes save more money than good estimates do.

Step five: the parallel run, where the money is well spent

Both systems run live. Work goes into both, and we compare the outputs until they agree.

It duplicates effort and people dislike it, and it’s the most valuable phase of the project. This is where you find that the old system rounded a calculation differently, or that a report left out a category nobody remembered excluding. Finding that during a parallel run is an ordinary Tuesday. After cutover, with the old system gone, it’s an emergency.

Step six: cutover and handover

The old system goes read-only and stays around for a set period before anyone deletes it. You get documentation, training and the credentials. You own the system, the data and the documentation.

Ownership matters here. If you can’t change anything after the migration without calling the vendor, the dependency has just moved somewhere new.

What gets left behind

Some features don’t come across, and we decide which ones in the open. Queries nobody has run in five years, reports replaced by other reports, forms for a process that ended. Each one gets listed and dropped on purpose, so nothing disappears quietly.

Some Access databases don’t need replacing at all. A single-user database running a stable process with no growth pressure can be fine as it is. If that’s your situation, the honest answer is a supported machine and a tested backup, and then leave it alone. That answer costs us a project, and sometimes it’s the right one.

The full process is on the Access migration page. The scoping quiz will tell you roughly which of these phases carries the risk in your case.