A family-run manufacturing company relied on a Microsoft Access database that had been developed and extended over approximately 20 years. The system had become central to the operation of the business, managing customer records, quotations, order processing, stock control and operational reporting.
The Challenge
The original developer had left the company several years earlier, leaving nobody with the knowledge needed to maintain or enhance the system. Urgent bug fixes had begun to accumulate, while the company had little confidence in making changes to an application containing more than 100,000 lines of VBA code.
An earlier attempt to convert the system to .NET had stalled because of the complexity of the application. The company was therefore left heavily dependent on a business-critical system that nobody internally fully understood or could safely develop.
The Solution
Rather than attempting another costly replacement project, I focused on rescuing and modernising the existing system while preserving the functionality and workflows the business depended upon.
Multiple Access back-end databases were consolidated into a single on-premises SQL Server database, simplifying the architecture and providing a much more stable and scalable platform.
The existing Access front-ends were retained and adapted to work efficiently with SQL Server. Queries, VBA and forms were modified where necessary to maintain good performance and eliminate errors.
I also removed the dependency on obsolete third-party OCX controls and plugins that were preventing the application from running with modern 64-bit versions of Microsoft Office. This made installation on new PCs significantly easier and removed the company’s dependency on ageing 32-bit Office installations.
The application’s integration with Sage was also upgraded to maintain reliable access to the financial information used within the database.
Overcoming 20 Years of Legacy Development
One of the biggest challenges was understanding the underlying codebase.
The application contained more than 100,000 lines of VBA, accumulated over approximately two decades. Some areas were heavily over-engineered, meaning apparently simple processes could involve complex chains of code that were difficult to trace and debug.
Taking over the system therefore required considerably more than Access development skills. It involved systematically understanding an unfamiliar legacy application, identifying dependencies and making changes without disrupting the business processes that had evolved around it.
The Result
The company now has a fully supported Access application running on a stable SQL Server platform, without having to abandon a system developed around its business over the previous 20 years.
Performance and reliability have improved, and the file corruption and associated data-loss problems experienced with the old Access back-ends have been eliminated.
The application can now be deployed on modern PCs using 64-bit Office, without relying on obsolete third-party components.
Most importantly, the business is no longer dependent on an unsupported legacy system that nobody can safely change. The database is actively supported and can continue to be adapted as the company’s requirements evolve.
