When you replace a Microsoft Access database, your data moves across, but almost everything else is rebuilt. The tables and records are cleaned, mapped and migrated, with counts and totals checked against the original. The forms people type into are redesigned in the new system. The reports are rebuilt around what each one is used for, not copied screen for screen. The hidden parts, such as queries, macros and VBA code, are read one by one because they often hold business rules nobody wrote down. And the people who rely on the database need the most care: the person who built it, the people who use it every day, and the people who only ever see its reports. This article goes through each part, what changes, and how to keep the business running while it happens.
If you're still deciding what to replace Access with, start with our guide to Microsoft Access and FileMaker alternatives. If the trigger is Microsoft's support dates, see Access 2021 end of support. This post assumes you've decided to move and want to know what the move does to everything around the database.
Your data: what moves, and how you know it's right
The data is the one part that moves rather than being rebuilt. In practice that means:
- Taking stock of every table. Including the ones nobody remembers, the linked tables that point to another file, and the Excel sheets imported every month.
- Cleaning it on the way. Access databases that have run for years collect duplicate customers, free-text fields used for several purposes, dates stored as text and records that point to nothing. This is data cleansing: fixing the information before it moves so the new system doesn't start with old problems. Our post on data cleansing before a migration covers how.
- Deciding how much history to bring. Open jobs, current customers and stock on hand always move. Older closed records can move too, or stay in the Access file as a read-only archive.
- Trial runs and reconciliation. At least one trial migration, then record counts and key totals compared between Access and the new system, such as the number of active customers, open job values and stock quantities. Differences are explained before anyone relies on the new system.
- Attachments and linked files. Photos, PDFs and documents stored in the database or linked by file path are moved to proper document storage, and the links checked.
After the switch, the Access file is set to read-only and kept, so anyone can still look up an old record.
Your reports: rebuilt for what they're used for
Reports are where people feel the change most, because many staff never open the database at all. They see a printed job sheet, a weekly sales summary emailed to the owner, or a stock report exported to Excel every Friday.
Before anything is rebuilt, list each report with three answers: who uses it, how often, and what decision they make from it. That list usually shows:
- some reports nobody has looked at in a long time, which don't need rebuilding;
- several reports that are really the same report with different filters, which can become one;
- a few reports that are critical and must be checked line by line against the old version before go-live.
Watch for the reports that leave the database. An Excel workbook linked to Access, a Word mail merge for letters, or an export someone imports into the accounting software are all reports in disguise. If they're missed, someone finds out the first Friday after the switch. Where a report feeds another system, it's often better replaced by an integration, so nobody re-keys what the new system already knows. That is the double data entry problem, and a replacement is a good time to remove it.
Your forms: redesigned around how people work
Forms are the screens people type into. They're rebuilt, not copied, and that's an opportunity:
- Drop the fields nobody fills in. Most long-running forms have them.
- Add the checks that were missing. Required fields, lists instead of free text, and warnings for duplicates.
- Keep what works. If the team relies on a particular layout or the order of fields matches the paperwork they work from, keep it. Change for its own sake slows people down.
- Make it work where people are. A new browser-based system can be used from the warehouse, the site or home, which an Access file on an office PC usually can't.
The hidden parts: queries, macros and VBA code
Queries, macros and VBA code (the programming behind buttons and forms) are where an Access database does its real work. A button labelled "close off month" may update prices, mark jobs complete and create an export file all at once. A query may apply a discount for certain customers that nobody remembers agreeing to.
Each one has to be read and understood, then either rebuilt as a rule in the new system, replaced by a standard feature, or deliberately dropped. Write down each rule in plain English as you go. That list becomes part of the documentation for the new system, and it's often the first time the business has seen its own rules written down.
The people who rely on it
This is the part most replacement projects underestimate.
The person who built it
If they're still around, their knowledge is the most valuable input to the project. Book time with them early, while they have it. If they've left, the database itself is the documentation, and it takes longer to read. Either way, the goal is that nobody is the only person who understands the new system. Our glossary explains key person risk.
The people who use it every day
They know the workarounds: the field that's used for something else, the report that's only right if you run it twice. Involve them in mapping how the work really runs and in testing the new system on real examples. Run both systems side by side for an agreed period so they can check their work before the switch. Give them a short written procedure for each changed task.
The people who only see the output
Managers, the accountant and customers who receive documents from the database need to know what will look different and when. Show them the rebuilt reports and documents before go-live, and agree what's acceptable.
What it looks like when it's done
The data is in a supported, shared system that people can use from wherever they work. The reports people actually use show the same numbers as before, and the ones nobody used are gone. Information that used to be exported and re-keyed into the accounts goes across through an integration. The rules that were hidden in VBA code are written down. And the old Access file sits in an archive, read-only, for the occasional look-up.
Frequently asked questions
Will we lose any data when we replace Access?
Every table you decide to keep is migrated, and record counts and key totals are reconciled between the old and new systems before you switch. The Access file is kept as a read-only archive, so older records can still be looked up.
Can we keep our existing reports?
You keep the reports you use, rebuilt in the new system and checked against the old versions. It's a good time to drop the ones nobody uses and merge the ones that are really the same report.
What if the person who built the database has left?
The database can still be replaced. Its tables, queries, forms and code show what it does; reading them takes longer without the builder, and testing with the people who use it every day matters more.
How do we keep working during the switch?
Run the old and new systems side by side for an agreed period, with staff checking their work in both, then switch on an agreed date. Access stays available read-only afterwards.
Do our staff need training?
Some. A new system that follows the way your team already works needs less. A short written procedure for each changed task, and help in the first weeks, usually matters more than a long training session.
If you have an Access database the business depends on, see our Access database replacement service, or book a free initial chat to talk through what yours does and the options.