What Microsoft Access is Good For

February 8, 2013 in Microsoft Access

A lot of my posts here will be about how best to use Microsoft Access, from programming in VBA and Jet SQL to indexing and data application architecture.

For a lot of IT professionals the question this immediately raises is “Why?”, as in “Why the heck would you ever use Access?” Searching for “Hate” and “Microsoft Access” yields plenty of results (of course “hate oracle” yields even more). From my first experience as a finance intern hacking VBA to get stuff done to later years both using and analyzing all the big boys of enterprise database development I went through my own cycle of disdain for Access. And yet I have come to firmly believe that Access has its place in the corporate IT landscape.

The most common defenses of Access (see Further reading below) revolve around low cost, quick development times, and ease of use for non-developers. These are all reasonable points and I leave it to the linked references to argue them. But two of the points that I think are most important are rarely highlighted, so I present them here.

1. Access is a file-based relational database

Most database platforms are service based. The running program that parses queries, fetches data, and does most other database tasks is running all the time, and it’s running on one computer (Or a cluster of computers in fancy high-end systems). If a user wants some data they need to ask the server for the data. The server does all the work of reading data from disk, collating, sorting and processing, and hands back the final result set. In a file-based database there is no brain on the other end of the line from the client. If a user wants data the file system just gives the user the file; it is up the user’s computer to do all the reading, processing, collating, etc.

File based databases versus service based databases
Service based File based
What the client needs
  • A connection string
  • A live network connection to the server
  • Just enough brains to parse the results
  • Direct access to the source file(s)
  • ALL the brains to interpret SQL, read and write data, and process queries
What the server does Just about everything Nothing

There are tons of file-based databases. A simple CSV file can be considered a file-based database, as can those good old mainframe VSAM and flat files that still basically run the world. But there are very few file-based relational databases, complete with a full SQL parser and well defined API. In fact, I only know of three with any substantial distribution:

What this is good for
A. You don’t need a server

Most important is the one thing a file-based database doesn’t require: a server! While lots of IT folks complain bitterly about the atrocious design of many Access database (true) I have yet to encounter an IT department in a big company that is willing to a) Give business users their own server or b) Give business users free reign on an enterprise database instance. File-based databases are imminently portable. And if you really screw it up, you’re not going to take down a production server.

The flip side of not having a server is that all the programs that constitute the data server in an abstract sense must be installed on every single client computer. Thus in order to use a Microsoft Access database you need either a full version of Access or a copy of the free-to-use Access Database Engine; for SQL Server CE you need the .NET framework and the SQL Server Compact assemblies (see also How to deploy SQL Server Compact Edition 4.0?, while SQLite requires the platform-appropriate SQLite component.

B. Disk access is very fast

If your datasets are of a reasonable size it can be much faster to process lots of queries against an Access database stored on your local computer because data pipe speed is limited only by your disk read speed rather than the network speed, and you don’t have to wait in line for your query to get processed by a server.

This does not apply so well when your source datasets are really, really big compared to your results sets. For example, selecting a few thousand rows of transactions out of a several billion record table in a data warehouse. In that case the massive amounts of RAM and parallelization that enterprise database servers can offer far outstrip the ability for your little desktop or laptop to process data in an Access database, even when that data is indexed well.

2. Custom programmability is directly integrated into Access SQL

In other words, you can write your own function in VBA and call it directly from a query in Microsoft Access. And the integration is very, very fast. This is really powerful when combined with the fact that VBA projects have direct access to COM objects, making it easy to do things like integrate regular expressions processing into an Access query via the VBScript regular expressions engine.

This is something that all the enterprise platforms provide as well (Java for Oracle, DB2, and Teradata; .NET languages for SQL Server), but it can be mighty complicated to link up all the parts and build the necessary wrappers. In Access it’s really easy: just write a function in a standard module and then reference it in your SQL.

Conclusion

There are good reasons to use Microsoft Access for a “real” data driven application. Access is a file-based relational database, making it a unique product well-suited for certain situations. In addition, the tight integration of VBA (essentially VB6) with the database engine make it very easy to extend Access with custom programmability.

Further reading: