A plan for leaving Access while the business keeps running: mapping what depends on the file, moving data to SQL Server, and what sets the cutover date.
Key takeaways
- Map what depends on the file before mapping what is inside it. The list of external consumers is the most important document in the migration.
- Phase one is almost always moving the tables alone to SQL Server, with the Access screens staying in place as linked tables.
- SSMA, Microsoft's free Access migration tool, converts the tables and links them back in a single operation.
- Running both systems in parallel fails unless one side is explicitly the writer and the other explicitly the reader.
- The cutover date is set by the business cycle - month end, stocktake, peak season - not by code readiness.
The risky part of leaving Access is rarely the tables. It is everything wired around the file: the Word mail merge, the label printer, the monthly export the accountant expects, the query somebody runs every Sunday. A plan that moves only data breaks the business in the week it goes live.
Map the process, not just the database
An old Access file almost never lives alone. Connections accreted around it over years, undocumented, and most of them surface only when they stop. Before a line of code is written, build a list of everything that touches the file.
| Dependency type | Typical example | What happens if it is missed |
|---|---|---|
| Manual export | A monthly Excel export for the accountant | The report is not produced, discovered at month end |
| Document merge | A Word letter pulling fields from Access | Customer documents stop being generated |
| Hardware | A label printer or scanner reading a table | The packing line stops |
| A file dropped in a folder | A supplier file imported nightly | Data stops arriving with no visible error |
| A user's own query | A saved query somebody runs themselves | The knowledge disappears with the query |
| An old form still in use | A screen nobody remembered was live | An entire sub-process falls over |
That list is collected by talking to users, not by reading the file. The question that works: "what do you take out of this system, and where does it go next?".
In what order do you replace things?
Not all at once. The order that reduces risk separates data from interface and moves them in distinct steps.
| Phase | What happens | What still runs on Access | Way back |
|---|---|---|---|
| 0 | Split front end and back end, automate backups, map dependencies | Everything | Not needed |
| 1 | Move the tables to SQL Server, link them back | Every screen and report | Relink to the original back-end file |
| 2 | Build web screens for the core process only | Sub-processes, reports | Switch the new screen off |
| 3 | Move reports and exports | Peripheral screens | Run the old report |
| 4 | Retire Access, keep it read-only | Nothing | Reopen it read-only |
The detail that decides it: through phases 1 to 3, both sides read and write the same single database. No syncing, no copying, no second version of the truth.
Phase 1: the data moves, the screens stay
This is the step with the best ratio of risk removed to effort spent, and it touches no business logic. SSMA - SQL Server Migration Assistant for Access - is a free, supported Microsoft tool that converts Access objects and migrates the data into SQL Server or Azure SQL. Ticking the link-tables option makes it link the tables back into the Access file at the end, so the existing screens keep working against the server.
Three technical points are worth settling in advance:
- When migrating to SQL Server 2016 or later, add a
rowversioncolumn to any linked table that needs updating. Access uses that field to determine when the record was last updated. - By default SSMA does not persist the user id and password in the connection string, so decide up front how users authenticate.
- After the move, the 2GB Access format limit applies only to the front-end file, which now holds nothing but forms.
If the file you are starting from is already damaged or will not open, that is not the start of a migration but of a recovery - see Access reporting an unrecognized database format and a recovery plan for a corrupted MDB. A migration starts from a file that opens, closes and backs up cleanly.
Running two systems without two versions of the truth
The common failure in parallel running is that each side writes to its own copy, and then somebody tries to "sync" them. Three rules prevent it:
- One database. Both interfaces write to the same SQL Server. No export, no import, no nightly copy.
- One entity, one writer. If orders moved to the new screen, nobody opens an order in Access. Not even "just for a small correction".
- Differences are checked daily, not weekly. A count and a sum per migrated entity at a fixed hour. A one-day gap can be traced; a one-week gap cannot.
What sets the cutover date?
Not code readiness. The rhythm of the business, and specifically whatever cannot be run twice:
- Month-end close and the bookkeeping export - a mid-month cutover splits the report across two sources.
- Stocktake - the weeks either side of it are the most sensitive period there is.
- Peak season - going live on the busiest day of the year removes any ability to fix things while running.
- People's availability - the cutover needs whoever knows the process, not only whoever wrote the code.
- The rollback window - if returning to Access takes more than two hours, the date has to leave room for that.
What goes wrong in month three
- An Access form nobody listed turns out to be in daily use by one person.
- A quarterly report fails, because the query behind it leaned on a table that changed shape.
- A free-text field that held dates in three different formats reaches a date column and rejects rows.
- Permissions: in Access everybody saw everything, and in the new system a correct restriction is reported as a fault.
- Speed: a query that ran locally against a file now crosses the network, and the difference shows up on exactly the busiest form.
In a wider frame, the same decision arrives from the spreadsheet side too - see when a spreadsheet has outgrown itself.
Sources
Frequently asked questions
How long does a migration like this take?
It depends on the number of screens in daily use, not on the size of the file. The data-move phase alone is usually a matter of days, because SSMA does most of the work. Replacing the interface is where the real time goes, measured by how many distinct processes the system actually covers.
Can we move everything at once and be done?
You can, and that is how these projects fail. A single-shot cutover requires every external dependency to have been identified in advance, and the dependency list is almost never complete on the first pass. A phased move surfaces the gaps while a fast way back still exists.
What do we do with the old history?
Separate operational data from archive. Whatever is needed for daily work moves in full; whatever is needed only for viewing can go to archive tables or stay in a read-only Access copy. That decision shortens the migration and shrinks the surface that has to be tested.
Is SQL Server required, or will another database do?
SSMA targets SQL Server and Azure SQL, which makes it the shortest path out of Access. Other databases are entirely workable, but the move then happens through manual export and import, and you lose the linked-table step that lets Access screens keep working during the interim.
Keep reading
Related service
Access & Excel Rescue
Stabilise the file the business runs on, then fix it or move it to the web.
About the author
Yehonatan Saadia
Freelance automation, web & MVP developer
I'm Yehonatan Saadia, a senior developer who builds business automation, custom websites, and MVPs for small and mid-sized companies across the US, Europe, and Israel. These guides come from real client work, not theory.
Work with meHave a project like this?
Tell me what you're trying to automate or build. I reply within 24 business hours with a few targeted questions, then we walk through it on a free 30-minute call, with no commitment. You come away with a scope, a timeline and a fixed price - or a straight answer that it isn't worth building.