Before you start
- ✓The tables extracted from Access as files (see the extraction guide)
- ✓The new database design, with the links between tables defined
- ✓A list of the old system's statuses, flags and codes, and what each one means
- ✓A test database you can wipe and reload freely
- ✓A decision on how customers are identified and duplicates merged
Build a migration you can rehearse
A one-off import gives you one chance. We build a small tool that clears the new database and reloads it from the extracted tables, so the move can be rehearsed repeatedly and rerun at cutover. It asks for the database name before clearing anything, and fails unless validation passes.
Load in a sensible order
Records others depend on go first: reference data, lists found in the data (styles, tartans, categories), staff, stock, customers, groups and parties, orders, then outstanding requests and anything in progress. Validation runs last.
Split all-in-one rows into proper records
The hire table we migrated for a kilt hire business had around 90 columns, packing customer, party, dates, branches and every item into one row. Each became one order with up to 13 items, plus a payment where a deposit was taken, with status worked out from the flags staff actually set (returned, collected, stock allocated).
Merge duplicate customers carefully
The same person appears across years of bookings with slightly different details. We match from most certain to least: email and name, then name and phone (seven digits or more), then name and address, then name within the same party, taking contact details from the latest booking. The right rules depend on how the business recorded people: here, email alone wasn't safe.
Link every new record to its original
Recording each old id against its new one lets anyone trace a record back and compare the systems order by order. When we consolidated two older booking systems into one, it also let each rerun skip rows already done.
Tidy the messy fields, and protect real emails
Branches typed as "SE", "St. Enoch" or "DS" become one name, postcodes are tidied ("ml15qn" becomes "ML1 5QN"), and anything unmatched is logged. A single address line gives up its postcode, but not a reliable town or county. Customer emails are swapped for safe addresses on every rehearsal; real ones return only on the final run.
Validate, then check against the original
Some problems block cutover: totals that don't match, bookings in the next 14 days with no stock, groups with no lead contact. Others just need a look. Finally, a small tool compares the Access file with SQL Server directly, order by order.
What usually goes wrong
From our own projects, not the manual.
De-duplicating on email merged whole wedding parties
Party members often shared the lead contact's email, so matching on email alone collapsed a whole party into one customer. Match on email and name together.
Placeholder 'Unknown Unknown' customers appeared
46 rows (0.35%) had genuinely empty names, not just blank text, and our comparison tool confirmed it. Skip those rows with a warning rather than inventing a customer.
Validation reported 1,346 problems, and only one was real
Future bookings without stock allocated are normal, because stock is assigned close to the date. Separate problems that block cutover from ones that just need a look, with a 14-day window for 'imminent'.
Duplicate stock rows clashed
The source held the same style and number twice, which the new database wouldn't accept. Skip the duplicate, log it, and let a person decide which is right.
When two old systems share no common reference
Consolidating two previous booking systems for another client, the only thing they had in common was email, and records without one had to be skipped. Writing the new ids back against the old records let re-runs skip rows already done.
Do it yourself, or get help?
If the database is small and the tables are already fairly clean, a careful developer can script this in days. If there are years of history, one wide table holding everything, or people and bookings recorded inconsistently over time, get help: the rules for merging duplicates and checking the result are where migrations quietly go wrong.
Get a developer on it