About 20 years ago one of our local councils created a fantastic job, time and project management system using Microsoft Access 97
At the time this was state of the art and connected to a SQL Server backend database and several third-party add-ins to provide functionality that was at the time not natively available in Microsoft Access it performed wonderfully.
This included functionality for creating PDF files emailing documents creating Excel spreadsheets and Word documents as well as sophisticated screen handling.
The system provided unparalleled facilities for the council to monitor costs at multiple levels across many different cost codes and across various geographical areas. Additionally, the labour timesheets met all the needs of the council’s unique flexitime rules.
Another developer created the system and with Microsoft Access 97 the system ran well on Microsoft XP and Microsoft SQL Server 2008. At the time this was the best option available as packages to provide this detailed level of functionality simply did not exist.
After a number of years, the council had to upgrade the operating system and also the SQL Server database platform and this caused a number of issues. In addition, the third-party add-ins became unsupported and unable to run in the newer technology.
Other things were happening as well such as a gradual change in the way we name our files, the system was built to accommodate short folder names and short simple file names. There was now a need to use URLs on both network servers and the cloud to reference documents. Additionally, the system screens were designed to use the small low resolution monitors of the day (1024 x 768) and now the system sits in a small cramped window cramped in a large desktop of available spare screen real estate
The system was successfully upgraded by the council sometime ago to access 2007 and this provided many more new years of life to the system. However, when they introduced the Windows 7 operating systems, and 64 bit technologies as well as thin clients it was required to update the system to at least Microsoft Access 2010. This caused a specific problem as that version of Access no longer supported of the use of Microsoft SQL server Stored Procedures using the data providers built into the System.
Furthermore, as the system was originally built the “On Error Resume” setting the system would simply pass over runtime errors. Skipping critical process points and providing variable results.
With the system becoming less reliable the council started creating spreadsheets to replace some of the functions and reports. This resulted in more staff time being wasted having to make up for the shortfalls in the system.
Just like the popular program The Repair Shop on the BBC we were called in to see what we could do to renovate this old system. We carefully worked through every screen and report and replaced all the original third-party routines with native functionality now available in the latest version of Microsoft Access.
We replaced the calls to all the store procedures using the views and hardcoded SQL. We updated the connectivity. We embedded libraries of the current Microsoft Office suite So we could automate Microsoft Office methods to create process and manipulate both Microsoft Word and Microsoft Excel documents. We also re-wrote all the file handling subroutines so they could adapt to new file naming conventions.
To improve general performance and reliability we provided additional error-checking to ensure simple things like before you for going to open a file let’s see if it exists first and if it doesn’t let’s put out a sensible meaningful error message rather than just doing nothing.
Just like in the BBC TV programme The Repair Shop, we came to the point where we showed our clients the newly renovated system. They were absolutely delighted with it and thrilled to see a faster, fresher looking system with smarter screens virtually everything working first time and for the first time in many years reports coming out of the system automatically without the need to create spreadsheets saving a lot of time and stress.
After showing the clients the renovated legacy system that day I sat down at home and watched the an expert in The Repair Shop renovate an old penny arcade game taking it all to bits applying a lot of love and care to every single part and then rebuilding it all bit by bit. It’s at that point I realised we just did that in software
PS we also create new state of the art database systems for running on PC’s, mobile devices and browsers.