Whatever you're migrating an Access database to — SQL Server, Azure SQL, Dataverse, or a custom web app's database — the data itself has to move safely across, get cleaned up along the way, and land correctly in its new home. That process is ETL: Extract, Transform, Load.
What does each stage of ETL actually involve?
Extract pulls the raw data out of Access — every table, with its relationships mapped. Transform is where the real work happens: converting data types to match the new system, fixing inconsistent entries (the same customer name spelled three different ways across years of manual entry), removing genuine duplicates, and applying the normalization fixes from our companion guide if the old structure needs it. Load writes the cleaned, transformed data into the new database, ideally in a way that's re-runnable so you can test the process repeatedly before the real cutover.
Why is the "transform" stage usually the hardest part?
Because Access databases accumulate years of manual entry with no strict validation — different date formats, inconsistent capitalization, the same entity spelled multiple ways, and orphaned records referencing a customer or product that was deleted long ago. None of these show up as an obvious "error" in Access day-to-day, but they will surface the moment a stricter server database enforces real constraints. Finding and resolving these issues before load, not after, is what separates a clean migration from a messy one.
How do you migrate without losing or corrupting data?
Run the migration as a repeatable process against a test copy first, never live production data. Reconcile row counts and spot-check values between old and new after every test run — the numbers should match exactly, not "roughly." Keep the old Access database fully intact and untouched as your reference of record until the new system is verified and signed off. Only then does a final, verified migration run become the real cutover.
What tools handle Access-to-server ETL?
For straightforward migrations, SQL Server Integration Services (SSIS) or Access's own built-in export/import tools can handle basic table-to-table transfers. For migrations with real transformation logic — data cleanup, deduplication, restructuring — a custom migration script gives you the control to handle edge cases an off-the-shelf tool won't anticipate, and to make the whole process re-runnable for testing.
How do you know the migration actually succeeded?
Reconciliation, not assumption: matching row counts table by table, spot-checking a sample of records field by field against the original, and — where possible — running your existing reports against both the old and new data side by side to confirm they agree. A migration isn't "done" because the load step completed without an error; it's done once you've verified the new system's numbers match the old one's.
What's a realistic timeline for the ETL portion specifically?
It scales with how messy the source data is, not just how much of it there is. A clean, well-maintained database with a few thousand rows and no real data-quality issues can migrate in days. The same row count with years of inconsistent manual entry, orphaned records, and no clear structure can take considerably longer — the extract and load are usually fast; it's the transform stage's cleanup work that determines the real timeline.
Should you migrate everything, or leave some old data behind?
Not every historical record necessarily needs to move into the live, actively-used new system in the same form. Some teams migrate all actively-referenced data fully, while archiving genuinely inactive historical records into a simpler read-only export instead of fully modeling them in the new schema — a reasonable way to keep the migration scoped without losing anything. This is a judgment call specific to your data and reporting needs, not a one-size-fits-all rule.
What happens if you discover a data problem mid-migration?
Because a well-run migration works against a test copy first, discovering a problem there is exactly the point — it's cheaper to fix in transform logic before the real cutover than to discover it in the live new system afterward. Treat each test run as an opportunity to catch exactly this kind of issue, and don't schedule the real cutover until a full test run reconciles cleanly.
This article is part of our Microsoft Access modernization guide — the hub for our full Access modernization cluster, including migration targets, database architecture, and ongoing operations.
Comments
Be the first to comment on this post.
