Microsoft Access, the use and abuse thereof

What’s it good for?

Microsoft Office enjoys a nearly ubiquitous deployment in many US corporations. The management of data in today’s business world often drive the over-use of spreadsheets. Eventually someone makes a handy spreadsheet, then more people find out about it and want to use it, then multiple people want to edit at the same time, then you end up with copies spread across your network, and eventually, chaos ensues.

Knowing how to create databases in Microsoft Access can kill these unruly spreadsheets, help create user friendly data entry or analysis tools, and easily automate things saving time and therefore money.

Data Analysis

Microsoft Access and its built in Visual Basic capabilities can arguably be one of the best data pulling and analysis tools out there. It can connect to, query, and compare data sources from nearly any database type, spreadsheet format, or text files. Complex math and statistics can be done on the fly and the results can be zipped over to an Excel spreadsheet for integration into any formal document.

Easy User Interfaces

The build in Visual Basic for Applications tools allow you to create heavily automated, error checked, validated, and simple user interfaces. Provided you do some good programming, no database knowledge is required by the end user to utilize the database.

What’s it NOT good for?

Try mentioning to a professional DBA that you’re using Access for a critical multi user application… don’t be surprised when they laugh at or possibly scold you. There are limitations to Access’s power, but with some clever programming, you can still make a reliable and secure multi user database application.

Storing Large Data Sets

Microsoft Access tends to crap out and perform pretty slowly when the tables get large. Once your dealing with large data tables and you notice poor performance, it’s time to move the data itself off to a proper database server, such as SQL Server, Oracle… whatever.

Multi User Stability

Some care needs to be taken to allow a database to be stable when you’re expecting a group of users to use it at the same time. This is generally done by separating the data itself, from the interface by creating a front end and back end database files. The data is accessed by the front end by linking the tables to the back end. That way you can keep backups separate and control the revision of the user interface without jeopardizing the data itself.