Microsoft Access is an established, solid tool for use in certain environments. Many business applications begin their life here. Over time, however, as its use expands beyond the initial purpose, the software can become fragile and unreliable, especially as a tool to run a business on. And unfortunately, companies frequently end up in this “all in” situation and don’t realize it.
Here are common scenarios where Access is no longer a good fit and some form of upgrade should be considered:
1. More than a few people access the application at the same time.
Access’ performance declines when used by more than a few people concurrently. Also, it doesn’t natively support transactional record handling, which means when two people make changes to the same record at the same time, only the last one will be saved. The other person’s change will be lost.
2. Remote access to the application is needed.
Access was not designed to be used with web-based applications accessed via a browser. Instead, remote access software such as MS Remote Desktop needs to be installed on a user’s machine in order to connect to the server which front ends Access.
3. Strong data integrity is required.
Access provides only limited ability to ensure data records are complete, unique and adhere to a consistent format. Because of these limitations, there is some risk that calculations within the application won’t function properly and reports won’t be completely reliable. Also, because of its file design, there is a good chance your data will become corrupted over time.
4. Sensitive information needs to be adequately protected.
The Access database is simply one file. Because of this, it can easily be emailed or sent externally via file transfer. Unless strong security controls are in place, it is typically not difficult for someone with knowledge and malicious intent to compromise an Access database.
5. The amount of data in Access keeps increasing.
Access’ performance and stability will degrade appreciably when the amount of data starts to exceed 1 gig, even though Microsoft advertises 2 gig as the threshold. A large number of records will also result in performance and stability problems.
If an application has grown organically from limited initial use to where it is now running the whole company or houses a critical data store, the time may be right to upgrade. One best practice for doing this involves moving to Microsoft SQL Server and leveraging web technologies to gain easier access to the data, and to achieve scalability that isn’t possible through Access.
Also, as the number of concurrent users grows and the need for added functionality increases, a custom-built application can become a more viable option. Concurrency will no longer be an issue, security can be strengthened considerably, more functionality can be added over time as requirements arise, and remote access software costs will be eliminated by designing the application for access via a web browser.