Before you start
- ✓A copy of the Access database (never the live file)
- ✓A Windows machine with Microsoft Access installed
- ✓Agreement on where extracted data may be stored, since it contains personal data
- ✓A version-controlled place to keep the extracted queries and code
- ✓Time with the people who built and use the system
Work from a copy
An Access system that has run a business for years holds a lot of hard-won knowledge. We capture it from a copy, never the live file, on a Windows machine with Access installed.
Read the structure and queries
First we read every table, any relationships between them, and every saved query (one database had 349), flagging the queries that change data rather than just look things up.
Read the screens, reports and code
Forms and reports can only be read through Access itself. For each one we record where it gets its data, every field and drop-down on it, and what code runs when it's used. Drop-down lists are telling: they show which tables the business treats as fixed lists. Then we read out the VBA code, 10,941 lines in one database, because that is usually where the real business rules live.
Keep the output searchable
Everything becomes ordinary files that can be searched and compared, with a summary of the counts. If one item won't open, we log it and carry on.
Treat the data as personal data
Exported tables, ready for the data migration, hold real customers' details. They stay out of version control, are stored securely and are deleted afterwards.
Keep a baseline and check it again
A working system keeps changing while its replacement is built, so we keep the extract (minus the data) as a baseline and repeat it before cutover. For Slanj Kilts, comparing a February extract with an August one turned up a price rise, three new jacket styles and four code changes we would otherwise have missed.
Write down how the business really works
Finally, we trace each business rule to the query or code behind it: pricing, availability, how parties are handled, what each report counts. That becomes the specification for the new system. How far each step goes depends on the size of the system and how much of it the business still relies on.
What usually goes wrong
From our own projects, not the manual.
Neither database defined a single relationship
Both Access databases had no formal links between tables. The links were kept by the forms, the code and naming habits instead. We rebuilt them from the forms' drop-down lists and the code, and made them explicit in the new database.
The written procedure didn't match the code
The documented rule said kilts within plus or minus four inches of the waist would match, but the query only ever matched smaller ones. Document rules from what the query or code actually does, not from memory.
The system changed while we were building its replacement
The live database was still being edited. Comparing a February extract with an August one found a price rise, three new styles and four code changes. Keep a baseline and extract again before cutover.
One awkward item shouldn't stop the run
Some forms and reports won't open automatically. Log the failure against that item, carry on, and deal with the few that failed by hand.
The data export is personal data
Table exports contain real customers' names and contact details. Keep them out of version control, store them securely, and delete them after the migration.
Do it yourself, or get help?
If you have a developer who is comfortable automating Microsoft Office, a basic export of tables and queries takes a day or two; forms, reports and VBA take longer. If the system is large, business-critical, or the person who built it is short on time, get help: the extraction is only useful once it's turned into documentation the new system can be built from.
Get a developer on it