According to iDataLabs, there are over 65,000 companies that use Microsoft Access. Most of these companies are small to medium-sized businesses who choose Access because it's bundled with Office and it allows employees to quickly create desktop databases and build forms to interface with the data.
But just as a business outgrows its office space as it hires more staff, it eventually outgrows the Access database as well.
In this article, we'll point out some of the weaknesses and limitations of Access databases as a business tool for managing data.
Data Storage Limits
The max size for an Access database file is 2GB which hasn't changed since Access 2000. This limits the utility of the database for running day to day business operations.
Common workarounds include splitting the database into multiple files and deleting/archiving older data.
Many Access databases experience slow performance and stability issues when the size reaches 1GB.
Access always processes data on the end user's machine. A query that returns 1 record out of a set of 1,000,000 will transfer the entire record set from the backend over the network and then apply the SQL condition to return the single record. This can consume a lot of bandwidth.
In addition, the database may suffer from duplicated data and missing relationships between tables because the person who created the database wasn't aware of database design best practices.
Support for Multiple Users
Running Access in an environment with more than 10 concurrent users leads to issues because it was originally designed to support a handful of end users.
Access databases are known for being susceptible to data corruption due to unexpected shutdowns of computers, incorrect database design, or other software issues.
Data corruption can lead to downtime or lost data that costs time and money.
The security mechanisms provided in Access are weak and unreliable. Security involves setting a password on the database or using Windows permissions to control who can read the file.
Businesses that store sensitive data in Access expose themselves to enormous risk because they must comply with HIPPA or PCI regulations.
In 2016, a hacker was able to obtain and sell 48,000 patient records from an Access database used by a healthcare organization in the midwest (link).
No Web Frontend
Access databases are not designed to be on the web. There are workarounds available for remote connectivity but these offer poor performance and cost money to install and maintain.
Microsoft has no future plans on making Access accessible to the web as evidenced by their retirement of Access Services in 2017.