How it usually got here
Nobody sets out to run a business on Access. Somebody needed to keep track of something, Access was on their PC, and it worked. A form was added, then a report, then a few macros. Other people started using it. Ten years later it holds the customer list, the job history, the stock, or the compliance records, and the person who built it has long since moved on.
None of this is a criticism. Access is a good tool for exactly this. The question is only what to do now that the database matters more than it was ever designed to.
The symptoms that mean something needs to change
- It is slow with more than a few users. Access was built for a handful of people on a local network. Beyond that, performance and reliability fall away quickly, especially over a VPN or Wi-Fi.
- "The database is corrupt." If anyone has ever had to run Compact and Repair in a hurry, or restore from last night's backup, the file is being asked to do more than it should.
- Nobody dares change it. The queries, macros and VBA behind the forms are undocumented, and the last person who understood them has left.
- It cannot be reached from outside the office. Staff want it on a phone, on site, or from home, and Access files do not travel well.
- The data is wanted elsewhere. The accounts package, the CRM or a reporting tool needs what is in it, and the only way to get it out is an export somebody runs by hand.
Three options, not two
The conversation is usually framed as "keep it" versus "replace it". There is a third option in the middle, and it is the right answer more often than either of the others.
Option one: keep it, and tidy it
If the database has two or three users, lives on a reliable file server and only needs an occasional new report, the cheapest sensible move is to document what it does, fix the things that irritate people, and set up a proper backup. A day or two of work can buy several more years. The risk is key-person dependency, which documentation addresses.
Option two: move the data, keep the front end
Access has a split personality: the tables, and the forms and reports that sit on top of them. The tables can be moved to SQL Server (on a server you already have, or in Microsoft Azure) while the forms and reports carry on exactly as they are, linked to the new tables. This single step solves the performance problem, the corruption problem, the multi-user problem and most of the backup problem, and your staff see the same screens they always have.
Once the data is in SQL Server it also becomes available to everything else: a web front end for the people who need it on site, better reporting, an integration with the accounts package. These can be added one at a time, as they earn their place, without the Access forms needing to change until you choose to retire them.
This is almost always where we would start with a business-critical Access database, and it is usually a fixed-price piece of work measured in days rather than months.
Option three: replace it
Sometimes the forms and logic really are beyond saving, or the business has changed so much that the database no longer reflects how work is done. Even then, the data model built up over years of real use is the most valuable thing in it, and a replacement should be built around that data, migrated carefully, rather than starting from a blank page and asking people to re-key history.
If you are being quoted for a replacement, it is worth asking whether option two has been considered. A good adviser will tell you honestly which is appropriate.
Questions to ask before deciding
- How many people use it, and how many would if it were available from anywhere?
- What would it cost the business if it were unavailable for a day? For a week?
- Does anyone currently employed understand the VBA and queries behind it?
- Where does the data need to go that it cannot currently get to?
- Is there a working, tested backup, and when was it last tested?
The answers point at which of the three options fits, and at how urgent it is. In most cases the honest answer is "sooner than we had assumed, but less drastically".
Got an Access database that has outgrown itself? We'll look at your system for free and tell you your options in writing, without obligation.
Request a free system review →