Goodbye legacy Access Database, Hello online Business Application
How we replaced a single-file Microsoft Access legacy system with a secure, multi-user web application.
The problem: a single file bottleneck
Before this project, our client, the Royal Yachting Association (RYA), managed all of its customer records for issuing compliance certificates in a Microsoft Access database. Not a shared, server-hosted database, but a single file, emailed or copied between members of the admin team whenever someone needed to make an update.
That approach creates real, practical limits for any team:
- No remote access. Your database only exists wherever the file currently is. Anyone working from home, or away from the office, is stuck.
- No audit trail. You’ve no reliable record of who changed what, or when.
- A single point of failure. Lose the file, corrupt the file, or lock yourself out, and your business loses its precious data.
- No concurrent editing. Only one person can safely update the file at a time. Two people editing copies at once risks overwriting each other's work, or having to fix clashes manually.
- Vendor lock-in. You’re stuck with the supplier’s idea of how the system should look & behave. Plus high license costs, forced system updates, and your business depends on some enterprise with no stake in your success.
Any of those sound familiar? They're the natural ceiling of any Access, spreadsheet, or file-based system. Instead, the RYA needed a proper, secure, multi-user business application. Our ‘boat-load’ of experience in the following came to the fore:
- Analysing business needs, building shared understanding
- Working within regulatory frameworks
- Tailoring content management systems
- Planning, testing and shipping critical data migrations (and puns! ⛴️)
- Flexibility
Our solution
We provided a mature, secure, extensible product that the operations team can lean on:
- Secure authentication, carefully considered workflows with granular access control & audit trails
- A bespoke and extensible data model, taken beyond what Microsoft Access could provide, with deeper modelling of compliance cycles and built-in data validation
On-demand custom reports, exportable to Excel using the Views Data Export tool we started over a decade ago, and continue to maintain for over 100,000 websites
- Personalised dashboards for boat surveyors and internal admin team members
Automated notifications for customers of compliance events
PDFs automatically generated from customer data
- Off-the-shelf components where appropriate, to save on cost
All built on a professionally-tested open source platform that can be deployed in any sensible hosting environment.
The new application is also ready for extension, to match the unlocked capacity of the admin team who previously had to waste time passing the database file around.
“ComputerMinds' guidance was invaluable in ensuring a smooth transition.
The end result will have a significant operational impact enabling us to issue digital certificates, reducing our reliance on printed materials, and consolidating three separate systems into a single, streamlined database solution.”
Adam Parry
Technical Manager at the Royal Yachting Association
Data migration from Access
We completed an in-depth audit of the Access database, tracking what had to be brought across to the new system, what could be consolidated, and what could be left behind. Working with those closest to the data meant we could build a rich understanding of what it meant, not just its form, so that the new content model would be exactly fit for purpose.
Then we scripted the migration itself using Drupal’s beautifully robust declarative format, so that it could run with full confidence. Repeated test runs and iterative migrations away from the production environment allowed the migration to be crafted to perfection. So the final migration was plain sailing, taking a matter of minutes to complete. Completely uneventful - just the way it should be with thousands of sensitive data records.
Public-facing trust: certificate verification by QR code
The one part of the site the public sees is a certificate verification lookup. The RYA team issues certificates confirming compliance with regulations for small commercial vessels. Think of it as the equivalent of the UK’s MOT test for cars, but for boats which are big enough for commercial use. When such a boat comes into port, a harbourmaster must be able to check the vessel meets safety & maintenance standards, by verifying the certification is authentic and current.
We automatically generate each certificate as a PDF, complete with a QR code. Scan the code and you can instantly verify that a certificate is genuine and current, without needing to trust a printed copy or PDF alone. It's a small feature, but an important one, providing a fast, tamper-resistant solution for everyone involved in compliance:
- RYA operatives & managers (data entry is easier & more reliable, plus notifications to customers are by email instead of having to post everything - reducing cost too)
- Vessel owners (less paperwork to keep track of, and keep dry on their boats!)
- Surveyors (inspection details can now be submitted securely through the application as they are granted access to their own segment of the application)
- Regulators (fast & trustworthy verification)
Why this matters to you
If your organisation still runs its core records through an Access database, a spreadsheet, or any single file passed between people, you have almost certainly hit - or will hit - the same ceiling our client did: no remote access, no concurrent editing, and a growing risk every time that file changes hands.
You don't need a costly new license or subscription, or a new website to fix that. You need a seriously-considered, secure, multi-user business application. If you’re interested in replacing a legacy Access database, spreadsheet, or file-based system, get in touch.