Blog

Posted by Steve Comeau

Microsoft Access Databases Are Still Popular

April 6, 2018

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.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. GUI Toolkit

  • 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 databaseTraders 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:

 

Industry

Application Description

Date in Service

Manufacturing

Database to group and organize trending tags for large system (over 20,000 tags). 

Late 90s

Manufacturing

Configuration backend database for SPC analysis tool.

Late 90s

Waste Water Treatment Facility

Laboratory data backend database

Late 90s

Food & Beverage

Backend database for batching application developed in Delphi. 

Late 90s

Systems Integration

In-house application used to configure and store PLC data patterns. 

Early 2000s

Waste Water Treatment Facility

Daily automated logging of equipment runtimes.  Automatic report printing.

2004

Manufacturing

Used as backend database for Control Room operator logging and snapshot of operations data.

2005

Metrology

In-house application used to manage and track metrology projects.  Used on multiple projects since late 2000s.

Late 2000s

University laboratory

Data logging solution used to log laboratory sensor data at periodic rates.

2013

Manufacturing

Complete MS Access application to manage and track changes to life safety equipment.

2013

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 Comeau is a Senior Business Intelligence Developer for Hallam-ICS. Steve has over 20 years in control systems, specializing in data historians, database programming, and report development. 

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. 

Contact Us

Topics: Manufacturing Intelligence, Process Control and Plant Automation

Steve Comeau

By Steve Comeau

Find me on:

April 6, 2018

Next Blog Post Previous Blog Post