Microsoft Access: The Swiss Army Knife


Thinking of using Microsoft Access to manage business data? Be careful and think it through, first. Access is like a Swiss Army Knife: lots of blades, many of them useful, but you would not use a Swiss Army knife to build a house.Swiss Army Knife

Access is a database engine, a user interface framework and a report writer, all rolled into a single tool. It is relatively easy to learn and if all you need is a single user tool to manage personal data, Access can be a great choice. On the other hand, if you build something useful to your business, other people may want to use it, and that is where you start running into the limitations of Microsoft Access.

The most significant limitation of Access is its database. Access is fine for small single user applications, but as you add more users and your database grows, performance degrades. Simple example: one of our clients used a simple Access database to capture order shipment information. As the database grew, it could take as much as 5-7 minutes of searching to find a single record.

Fortunately, there is a simple work-around to the limitations of using the Access database engine: use Microsoft SQL Server instead. There is a version that is free and it can be relatively straight forward to convert an Access database to a SQL Server database if (and only if) you have not developed any “bad habits” in the way you coded your application. Remember that order shipment database I mentioned in the last paragraph? When we converted it to use SQL Server, the search time dropped to less than one second.

What about Access as a user interface development tool? It’s OK, but it is not even in the same ball park as Microsoft Visual Studio which is much more powerful, but also harder to learn. Although Access can be used to develop web based applications, they are a bit of a kludge. Access applications execute on the user’s desktop, so the program has to be distributed to every user. As use grows, support and maintenance challenges grow too.

Finally, Access is a report writer. It’s actually pretty good as a report writer, but not as powerful as Crystal Reports or SQL Reporting Services, and Access reports cannot be embedded in web-based applications.

I have been developing data intensive applications that run business processes for over forty years, and have learned that the one-tool-fits-all model seldom does a good job. I have an old Shopsmith multi-function tool sitting in a corner of my garage. It is a table saw, a joiner, band-saw, drill press, lathe and disc sander all in a single tool. I loved my old Shopsmith and still use it for small projects. But if I want to build anything serious, I borrow my son’s DeWalt and Bosch tools. They are so much better than my one tool that does anything poorly.

Print Friendly