Over the years from the mid-1990s until the present, I have developed many Microsoft (MS) Access database applications for clients and for in-house projects.  Most of these databases are still in use at client sites and others are still used within Hallam-ICS on projects.

Notice:  Since this blog post was first published in 2018, the evolution of software tools for databases now warrants a change in our position on Microsoft Access databases.  We no longer support development of new projects in Access, except as a user interface to a back-end database such as SQL Server.   We encourage using alternatives to Microsoft Access.  Please contact Hallam-ICS Manufacturing Intelligence group for details.

The Microsoft Access database application has been around since version 1.0 in 1992. At the time, it was considered an innovative application, since most relational databases were either expensive database servers or DOS applications.  At $99, MS Access was a great deal. 

Today powerful database server software is available as open source and can be downloaded for free.  Microsoft has SQL Server Express Edition, also a free download, which can be used for small projects.  More powerful versions of SQL Server are still within reach for most projects budgets and a licensed SQL Server may already be installed on site and be accessible.  

So why use MS Access these days?

The Business Case for Access

The reason for using Access is that it quickly fulfills business needs for many types of small-scale database solutions.  It is a highly productive tool; therefore useful results can quickly be produced that help your business.

Many of our clients like using Access.  They feel comfortable with the product, project budget, and control of the application. 

Unlike most other databases, Access is also a GUI toolkit.  It comes with everything you need to build databases that are suitable for a small business, department, workgroup, or series of projects. 

• MS Access includes:
• the database
• table builder
• query builder
• form builder
• report builder
• macro writer
• VBA programming language

An MS Access database solution does not require special hardware, server operating system, or custom application software environments.  Although sometimes thought of a single user database, it actually works well with multiple concurrent users (but it is best to keep it to < 10).   Access can also be used as a front-end to a more powerful database, such as SQL Server or other ODBC databases.  The result is that even a complex application can be built within reach of most application project budgets, and simple databases can be put together as an add-on to a control systems project.

Portable and Flexible

One feature that many users like is the ease of portability.  No, not portability as in it can be used on a Mac or in Linux, but in the sense it is easy to move or copy, much like an Excel file.  Since an Access application can be in one file (but does not to have to be) it is easy to copy the database for other uses, such as a new project or to replicate to other locations.  IT personnel may wince at this capability, but users generally like this feature since it allows for great flexibility.

Many users appreciate that once they get to work with Access, they can understand it, and is very flexible.  It is relatively easy to make changes to adjust it to their changing business needs.

Microsoft Northwind Traders example database

Longevity

MS Access has been around for over 25 years.  Although Microsoft has not been improving the product much over the past 10 years, predictions of its demise have been greatly exaggerated.  According to db-engines.com it remains the sixth most popular relational database.   I continue to support many installed Access databases at customer sites and they continue to function for their intended purpose. 

Here is a summary of the some of the MS Access applications that have been developed and deployed by Hallam-ICS.  These databases are used in various industries and continue to perform well:

The Caveats

Although Access is a useful database, there are good reasons not to use Access on database projects.  Do not use Access for:

• Databases with sensitive and regulated data
• Databases that require user and role-based security
• Large to medium databases
• Applications with many concurrent users
• High-performance or large scale data logging
• Databases with a web-based or a custom-programmed front end

If you have an existing MS Access application you may want to consider migrating the back end databases and tables to a product such as Microsoft SQL Server.  There are many advantages to this, including:  much better security, handling future database growth, faster performance, scheduled operations and data integration, and to scale-out to more users

About the Author

Steve has retired from Hallam-ICS, but his contributions to the company continue to be valued.

Read  My Hallam Story  

About Hallam-ICS

Hallam-ICS is an engineering and automation company that designs MEP systems for facilities and plants, engineers control and automation solutions, and ensures safety and regulatory compliance through arc flash studies, commissioning, and validation. Our offices are located in Massachusetts, Connecticut, New York, Vermont and North Carolina and our projects take us world-wide.