Modernizing Microsoft Access

Microsoft Access is still popular.

Microsoft Access was the first database engine built specifically for Microsoft Windows, back in 1993. With this it became the main database engine in the late 90’s and was widely popular in the early 2000’s.

One of the things that made Access very popular was that it shipped with Visual Basic for Applications (VBA) and this made the creation of basic applications very easy. That, combined with the prevalence of Microsoft Office made it a very good option for single user applications.

Outgrowing the platform

As it often happens with software, many of these applications grew much more than planned or became critical for the business. Maybe they started as small tools, probably replacing some manual process, but over the years turned into hard-to-maintain, complex systems.

Examples abound:

  • A truck manufacturing company that exported their catalog from their AS/400 mainframe as an Access file, built a few forms to create orders and then shared this file across all the dealerships that ordered stuff from them (probably a handful at the beginning but hundreds of them in recent years).
  • A government agency that started using a small Access DB to record measurements from a few rain stations and ended up storing data from hundreds of sites with rain amounts, river and lake levels, hours of sunshine, temperatures and others, along with their corresponding reports and charts. In the end the app was the main tool used on a day-to-day basis by a dozen employees.
  • The solo coder who created a few models to predict the scores of NFL games, started selling the weekly data and continued by using similar models for NBA, MLB and NFL.

What exactly is the problem with these apps?

Well, if you’re in this page you probably already know some of the issues with these apps. But here are some of the most common nonetheless.

Having an Access database or application is not necessarily a bad thing. As of this writing, Access is still supported, as well as VBA. On that side, you should be able to continue using this platform for the foreseeable future.

The problems typically come from the lack of options once these apps go beyond the single-user or intranet application. At some point you’ll also need to worry about the size (the maximum size for an Access DB is 2GB).

For applications that rely on VBA there’s also the problem of maintaining the business logic. First, it’s hard to find VBA developers (or developers that think adding VBA to their resume is a good idea). Second, VBA doesn’t make it easy to integrate with newer technologies (like consuming RESTful APIs or using gRPC).

The other (and probably most common) issue, is when the business needs change and multiple users need to access the database.
Access has a shared mode, but it has limitations and still assumes users have direct access to the database. This might also add some security concerns. Accessing the application via Internet or in multiple offices just makes things worse.

In the end, the problem is not Access, it’s just that it’s being used for something it wasn’t designed for.

How do I fix it?

Now, some of these applications can be easily replaced by a new application, or maybe even some COTS offerings. In other cases the business logic might be too complex or the app too custom to be easily replaced and needs to be maintained, re-written or migrated.

Nowadays there are many alternatives to Access, both for storing data and to build basic line-of-business applications. Some of them free, some of them built into other popular offerings and most of them very accessible and easy to integrate.

Some of these options include:

In some other cases it might make more sense to use a fully fledged development platform (.NET or Java) and use a full-blown database engine. That’s always an option.

In any case, you’ll need to prepare a good plan for it, including:

  • How to migrate existing data?
  • Will the users require re-training?
  • How will the new platform be tested to verify all the business logic is there?
  • Will the new platform enable new business models?
  • Are there any limitations of the new platform (e.g. will all users require permanent Internet connectivity to use it)?
  • Which features from the new platform do you want to incorporate as part of the modernization?
  • Will my existing developers be able to continue maintaining the applications?

As you can see, all these are important questions with no single right answer. It will all depend on what your existing Access application or database does, who your users are and what are the long term business goals.
What’s important is that we can help you answer them and create a full modernization plan that ensures you can continue focusing on your business instead of the limitations of your technology.

Let us help you, starting with a modernization portfolio, and let us bring your company to the present.

Published by Will Vasquez

Technologist, developer and entrepreneur.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s