To Access or not to Access …
When to use Microsoft Access, and when to invest in something more.

By Matt Hopkins

• It’s easily available at a reasonable price.
• It’s easy to use, so you don’t have to be a database developer to create a solution.
• It doesn’t require a dedicated server or staff, so you control maintenance costs.

But Microsoft Access was designed to perform as a personal database.  If you need a database to track something yourself, it’s probably the perfect tool.  However, once you introduce multiple users and a changing business environment, you quickly encounter situations where it does not work.  Here are four examples:

      1.  You can’t risk data corruption.

Because of the way it is structured, every user on an Access database has complete read/write access, so users can modify anything; or even copy the entire database to their local machine, modify it, and then copy it back on top of other user changes.  You can attempt to control this risk using business processes, but if you really can’t risk a mistake with the data, Access is not the tool for you.

In addition to the potential for data corruption mentioned above, multiple users on an Access database will slow it down tremendously.  Because of its underlying, single-file design, Access only permits one of the users to modify the database at a time.  That means that other users must wait until the database is free to perform their actions, and that means lower productivity.

It is also fairly common for an access collision or network issue to cause Access to lock all the users out.  When this occurs, all users need to exit out of Access to reset their connections and get things rolling again.

      3.  You may need to change the structure of your database over time.

Because of the way Access stores information about the database’s structure, it does not replace old structure information with the new information when you make changes.  Instead, it appends the new information on to the end of its structure definition.  That means that any change you make (such as changing a field definition) quickly increases the complexity and maintenance difficulties of your database.  This can introduce more speed and corruption issues.

      4.  You want to include business logic.

This is a more advanced requirement, but sometimes you want to include some functionality to help keep your data clean (e.g., populating city and state fields from the info entered in the zip code field to prevent random abbreviations or misspellings.)  Microsoft Access enables some logic to be added to its forms; but if you implement more than one view to your database, you need to add that same logic to every view, and the redundant code quickly increases the physical size and the maintenance difficulties of your database.

Is Microsoft Access right for you?  If you intend to use it for a single user or a very small group where the individual people won’t be using it at the same time, Access can be an excellent solution.  If you intend to share the data among many users, if your database design will change frequently, if you have large amounts of data, or if you need to implement significant business logic, Access will start to sputter and you should consider a heavier duty relational database such as Microsoft SQL Server, Oracle, or the freely available MySQL.

Matt Hopkins works with growing businesses to evaluate and implement new solutions that give them more room to grow.  His experience has spanned everything from evaluation and proof-of-concept LMS systems to development and integration of a web-based resource management application.  Matt is also the co-creator of nofouls.com, a social networking site for pick-up basketball players.