The Scenario
A specialist engineering consultancy has used a Microsoft Access database as its project management and client record system for thirteen years. It was built by one of the founding partners, who had taught himself Access in the late 2000s and needed something better than the paper filing system the practice started with.
The database holds everything: seven thousand project records, twelve thousand client contacts, document references, fee proposals, timesheet summaries, and a set of custom queries that generate the monthly management accounts. It has forms for data entry, reports for printing, and a set of macros that automate recurring tasks like generating fee invoices and flagging overdue payments.
The database lives on a shared network drive. Three people access it simultaneously, which is the maximum Access can reliably handle. There are now nine people in the practice who need access to the data. Six of them rely on one of the three licence holders to look things up on their behalf, a process that involves walking to someone’s desk and waiting.
The Problem
The Access database has reached every limit the platform imposes. The file size is approaching the two-gigabyte ceiling. Performance has degraded noticeably — opening the main projects form takes twelve seconds, and running the monthly accounts query takes over a minute. The founding partner who built it retired eighteen months ago and is no longer available to make changes.
Nobody else in the practice understands the VBA code behind the macros. When something breaks, the office manager restarts the database and hopes for the best. Last quarter, a corruption event caused a full day of downtime. The IT support company recovered the database from a backup, but two days of timesheet entries were lost and had to be re-entered from memory.
Multi-user access is the most pressing daily frustration. The practice has grown, but the database cannot grow with it. An associate who needs to check a client’s project history has to ask someone with access to look it up. If all three concurrent slots are occupied, the request waits. In a consultancy where billable time is the product, waiting fifteen minutes for a client record is not a minor inconvenience — it is lost revenue.
The founding partner’s departure removed the only person who understood the database’s internal logic. Table relationships, calculated fields, macro sequencing — all undocumented and held in one person’s head. The practice is operating a system it depends on but does not fully understand.
The Approach
Digital Royalty begins with a full forensic audit of the Access database. Every table, relationship, query, form, report, and VBA module is documented. The goal is to reconstruct the institutional knowledge that left with the founding partner — to understand not just what the database stores, but how and why.
The audit reveals that roughly forty percent of the tables are actively used. The rest are artefacts of abandoned experiments, duplicated structures, and temporary tables that were never cleaned up. The active data is well-structured but lacks referential integrity constraints, meaning the relationships between records are maintained by convention rather than enforcement.
The migration target is a modern web-based system built on a relational database with proper constraints, role-based access, and no concurrent user limits. The schema is designed from the audit findings, preserving the logical structure that works while correcting the structural weaknesses that Access allowed.
Data migration follows a three-pass process. The first pass moves all records into the new schema and flags any that fail validation — orphaned references, missing required fields, inconsistent date formats. The second pass resolves every flagged record with input from the practice’s senior staff, who can provide context that no automated process can infer. The third pass runs a full reconciliation: record counts, financial totals, and sample spot-checks to confirm that nothing has been lost or distorted.
The custom queries that drive the monthly management accounts are rebuilt as saved reports in the new system. The macros are replaced with automated workflows. The forms become web interfaces accessible from any device on the network — or remotely, for staff visiting client sites.
The Outcome
The nine-person practice has full concurrent access for the first time. Nobody walks to another desk to request a lookup. Project records open instantly. The monthly accounts report generates in seconds rather than minutes.
The data is backed up automatically with point-in-time recovery. The corruption risk that kept the office manager anxious is eliminated. When a new associate joins, she receives login credentials and is productive the same day — no waiting for a concurrent slot to become available.
The founding partner’s knowledge is no longer missing. It has been extracted, documented, and encoded in a system that any competent developer can maintain. The practice is no longer dependent on a single person’s memory for its operational infrastructure.
Over the first six months, the practice estimates that eliminating lookup delays and manual workarounds recovers the equivalent of three billable hours per person per week across the team. For a consultancy, that translates directly to revenue.
Who This Applies To
This scenario is widespread among professional practices, small consultancies, membership organisations, and specialist firms that adopted Access in the 2000s as a step up from paper or spreadsheets. The database solved a real problem at the time and has served well, but it has now become the constraint rather than the enabler. If your Access database is approaching its file size limit, struggling with concurrent users, or maintained by someone who is no longer available, the migration described here addresses each of those pressures directly.
Your Data Deserves a System That Can Keep Up
An Access database migration is not about abandoning what works. It is about preserving everything valuable in the data and the processes while removing the limitations that hold the business back. The first step is understanding exactly what you have.