Mark Thomas is a CRM and Data Solutions Master. If you have any topic you would like to see addressed, feel free to make a suggestion.
Why Access is Still Around
In 1992 Microsoft introduced a new standalone, desktop database product, Microsoft Access. Over the years it has evolved along with other MS Office products and despite scathing critiques from learned Big Data database experts, it is still here.
So Why is it Still Around?
First, it is everywhere. Anyone or any company with Microsoft Office very likely has it as one of the Office components. Second, it allows itself to be deployed rapidly in an unpolished form: raw tables, primitive forms, rudimentary reports. But once it’s there, anyone with a little skill and some ambition can build on this and over time add sophisticated functions that closely follow the workflows of the operation. Later if more skill is needed than is available in-house, it is not difficult to find talented developers to bring in and really make it blossom.
The Biggest Complaints About Access
- It’s complicated to use
- It does not handle multi-user access well
- It’s not a REAL database and will fail under load
When you hear about problems from using Access, a little analysis is good so you can understand what the underlying source of the problems are.
1. It’s complicated to use
That is a relative viewpoint. If this is being compared to just slapping some data into an Excel worksheet, why, yes, it is more complicated. But if you’ve ever encountered an overblown, bloated Excel app that’s being used to track Accounts Receivable, Customer Contacts, Inventory, etc., etc., etc., you will observe huge problems from Excel’s LACK of sophistication (and the “complexity” required to prevent these problems).
There are excellent reasons why Access tightly controls date, money, numeric and text fields by only accepting date, money, numeric and text entries, the least of which is to prevent user’s data entry mistakes.
Then there is the uber-powerful ability to create RELATIONS between data tables, something that Excel or other flat-file solutions don’t simply fail at – there are no tools in existence that make up that failure without resorting to complicated schemes. A simple example of this a relation between a customer record and all orders for the customer.
2. It does not handle multi-user access well
This is a problem when one attempts to take a previously working Access application, put it on a shared drive, and allow any and all staff to connect to it.
This is easily handled, and once handled bypasses the problem entirely. In short, one must separate two major functions into the “front end” and “back end” components. The front end contains the entry forms, menus, controls and reports. The back end houses the data and takes care of read, write and delete functions as requested by the front end.
The separation is referred to as “splitting” the database from one into two separate files. The backend goes on whatever shared driver or server you’re using and the front end is moved to each user’s workstation.
Access provides a built-in database splitter to make this easy.
3. It’s not a REAL database and will fail under load
This is also a relative statement. Partly to blame for this assessment is the fact that earlier versions of MS Access used a very scaled down database engine (the underlying part of the database that contains and manipulates the data stored). Microsoft upgraded the engine in 1999 and then in 2015 switched up to MS SQL Server Express, a lighter version of Microsoft’s high-end enterprise-grade database engine, MS SQL Server.
It is safe to say that the possibility of running up against the limits of Express is unlikely in smaller businesses. If your operation is expanding or is likely to expand past these limits you are going to need dedicated IT staff anyway, and even then you still have the option of replacing Express with the unrestricted version of MS SQL Server while retaining the current Access application as a front end to the new database server.
Conclusion
For rapid analysis of large amounts of data, especially pulling together related data that won’t fit on a spreadsheet, few things are better than simply whipping out Access. For simple data entry, processing and reporting applications where you can’t make your data fit in a commericail application, few things are more convenient than putting together what you need with Access. The only ways you can go wrong with Access is (a) not getting design help when the relations get complicated and (b) not staying with currently supported versions. In any other case you can usually move forward no matter what new requirements turn up.