It’s important to choose the right tool for the right job.

What about Microsoft Access? When is it the best or at least a good tool for the job? Or turning around the question, what are the best jobs to use Access for?

The first thing to remember is that Access is a relational database front end. It’s good at working with data in tables that is related to data in other tables. Parent child relationships. 

I say it’s a front end because that really is the case. It stores data internally by default using the ACE database engine which is a separate thing and can be used by other programs completely apart from Access. 

(Thank you to Richard Rost who reminded me of my brain freeze – that the JET database engine I originally referenced has not been the default Access database engine since 2003)

But in addition to the default JET file based database engine, Access can connect to any database that supports ODBC or OLE. This includes Microsoft SQL server, SQL Lite, MySQL, MariaDB, PostgreSQL, NoSQL, and many more.

So if Access isn’t really about the backend selection in terms of its distinguishing reason for choosing it as a tool, what is?

Here’s a few reasons off the top of my head:

• Integration with Microsoft Office products. If you are already using Microsoft Office as your main office productivity suite, Access fits in really well. It knows about and can utilize existing libraries to connect and communicate with those other apps. 
• Managing data from relational databases via interfaces for tables and queries. Visual query building can be much faster at times than writing SQL code directly. The query objects in Access allow you to do that to create different views of the data. Both the query and table data views have basic and advanced filtering that can be used to quickly view and drill down to the data you want to see. 
• Forms for on screen display and data entry. Forms offer a host of controls and very customized and controlled ability for rapid and accurate data entry, editing, and selection using the tables and queries as the source data. 
• Reports for on screen or printed output provide a host of display, grouping, and formatting options as well as many different output formats to save your table and query data views. 
• Macros- eh, I don’t really ever use them. I go straight to VBA
• Visual Basic for Applications (VBA) is an implementation of Visual Basic 6 and is very powerful in terms of automating tasks and creating new actions for users to perform. 
• Rapid application development framework. All of these features make Access a great tool for quickly building relational data driven applications and tools for Windows desktops and within the Microsoft Office Suite. 

How about things Access isn’t good at?  Well, a lot of people want to see their data from a remote location over their phone. Access sucks at that because there is no phone app. Also Access tends to have buggy and poor performance if the back end is not on a local network (although with a carefully planned application, you can use Access with WAN connected databases). That’s probably two of the most common complaints I see.

How about your perceptions, praises, or complaints you’ve heard about Access?