Microsoft Access: The Database Software That Won’t Die

Microsoft Access: The Database Software That Won’t Die

What lessons can we learn from old code that becomes unkillable?

Image for postAll pictures Matthew MacDonald

Here?s a Halloween story to scare the average corporate programmer. Imagine you put your valuable business data in a friendly database program backed by corporate megalith Microsoft. Everything seems perfect ? at first. But you can?t shake the uncanny feeling of impending doom.

Then, the signs begin. The forms that work so well when three people use them get mysterious glitches when the whole company joins in. Your boss asks you to make a web interface so a remote office can use the database and you find? nothing. There?s just a gaping, soulless void where web support should be. You go looking for the other conventional database ingredients you expect. A security model? (No, every user can access everything.) Proper data integrity? (No, because there?s no log file recording database operations.) Open standards? (Don?t even ask.) Your blood runs cold.

Most chilling of all? It isn?t even free.

This is the story of Microsoft Access, an easy-to-use bit of database software that?s nearly thirty years old and started showing its age at least a decade ago. You probably assume that Access died a long time ago. But you?d be wrong. In fact, Access continues to shuffle along, zombie-like, its usage neither growing nor declining. Microsoft has made more than one attempt to terminate it, but the user community has successfully fought to keep Access alive ? even as other legacy products like FrontPage and Visual Basic 6 were left cold and buried in the dirt.

It?s easy to dismiss Access as nothing more than another legacy software nightmare. But the story of Access has lessons for every software designer. It gives us insights into what makes a product live, thrive, and then linger long past its expiration date.

How popular is Access (really)

Let?s get one thing clear immediately. Access is not dead, nor is it about to be dead. This may seem like a violation of all that is sound and decent in the world of database software, but it is the grim truth.

Data research companies consistently find a small but loyal number of companies using Access. HG Insights counts 140,000 companies currently using Access, which is half the number that use the much more professional SQL Server. Infoclutch records a similar tally. And DB-Engines, which ranks database software based on how often it appears in searches, social media, and sites like Stack Overflow, deems Access the world?s ninth most popular database:

Access squeezes into the top ten in the DB-Engines ranking, alongside many more capable databases

These statistics almost certainly overstate the popularity of Access. Many businesses use more than one type of database software, and the applications they run with Access almost certainly have smaller scope, deal with less data, and ? unless the CEO has a death-wish ? aren?t governing a mission-critical part of daily operations. It?s also true that companies use technologies they don?t realize they?re using. For example, every business with a WordPress website is also relying on MySQL, even if someone else is hosting it for them.

I have my own experience with the hidden popularity of Access. In 2009, I wrote a book about Access on a whim. My reasons for writing the book were simple. I?d been using Access for years for quick, ad hoc data solutions ? things like keeping track of a collection of books, or managing invoices and payments from my consulting work. All of these scenarios need more structure and data-editing control than Excel provides, but they can easily live with the restrictions of the Access environment. A few tables, some relationships enforced with constraints, a small set of queries, a report or two?in an afternoon, the job was done.

That said, I?ve never advised anyone to put an Access database behind their ecommerce storefront. If you did that, it?s on you.

I wrote my book to capture the tricks and pitfalls I?d learned, sure that it would disappear quickly into obscurity. To my surprise, it became one of my most popular. Sales still trickle in, at a rate of roughly a copy sold every day or two. Clearly, there are people still interested in Access, even if it?s only because they?re trying to untangle the mess left for them by a previous generation of hobbyist programmer.

How to attempt to kill a program (and fail)

Everyone who?s lived in tech has seen a favorite piece of software meet an untimely end. There?s an entire graveyard of abandoned Google projects. Microsoft is also notorious for killing its children ? sometimes even several at a time, like when it retired Expression Studio, an entire family of web, design, and media-encoding tools that were meant to compete with Adobe.

But for some reason, when Microsoft came after Access, they blinked.

Image for post

The first mistake was deciding not to execute Access in one step, like they had with Silverlight, PhotoDraw, Minecraft, and so many others. Instead, Microsoft tried to gently encourage Access into irrelevance. First, they tried to sideline Access by pointedly ignoring it. A few months before the Office 2013 release, Microsoft still had no official answer as to whether there?d even be an Access 2013.

Then they began hacking away at its features, removing old and sometimes still-popular standbys. Gone was the ability to import old formats like dBASE. Gone was the ability to create pivot tables. Gone was the ability to create an Access front end for a SQL Server database ? and, with it, the upsizing wizard for migrating Access databases to SQL Server. It was like a nightmare from the Hostel franchise. Every release, Microsoft hacked off another piece of Access anatomy, but still the program remained.

Most dramatic of all were the attempts to provide an upgrade path out of the Access world. In quick succession, Microsoft created ? and then abandoned ? no less than three different frameworks for putting Access databases on the web. Two ? Access web databases (introduced in Access 2010) and Access web apps (introduced in Access 2013)?were built on SharePoint and SQL Server. Neither succeeded, and Access 2019 became the first version of the software in over a decade to have no web features at all.

Here was something we hadn?t seen with other bits of misfit software. We were watching Microsoft trying to provide an upgrade path out of Access, failing, and then giving up entirely. Like Frankenstein?s monster, the creator had abandoned the creation, but still wasn?t able to kill it.

Lessons from Access and its enduring life

It?s no surprise that old things linger in the world of technology. We still have COBOL, after all. But what?s unusual with Access is that it endures despite the not-so-benign neglect of the company that created it.

What makes Access so enduring despite its limits? There are a combination of reasons both cultural and practical, but three stand out.

1. The power user gap

The Access audience is a special crowd that?s rarely targeted these days: technical people who aren?t serious coders. They may know their way around an Office macro, but they don?t have a formal programming background. Not so long ago, we called this kind of a person a ?power user.?

Power users can be a dangerous group to help. With a little knowledge, you can make a very powerful weapon for shooting yourself in the foot. But there is a serious untapped potential here. Give a technical person a way to solve their problems that doesn?t involve writing pages of code, and they can make a difference ? automating small tasks, managing their own islands of data, and helping to keep their local environment organized and effective.

Today, there remains a hunger for codeless or code-light tools. Motivated people want to do their jobs without paying expensive professionals for every semicolon. But so far the only offerings we?ve given them are a VBA macro language from a generation ago and pricey tools like PowerApps that only work if your business signs up for a stack of Microsoft cloud products.

2. True power is empowering someone else

If there?s one secret to Access?s success, it?s this: Access succeeded because it made people feel powerful.

Here?s another example of Access at work. My partner tracks families, students, classes, and attendance for a small music school that?s had multiple locations. There?s no danger of multiple people editing the database at the same time, and there?s no need to open the data up to other platforms. Would a full-blown SQL Server application better? Yes, and I could even do it for free with SQL Server Express. But even though designing such an application is a straightforward task, it isn?t a done-in-a-day sort of affair. And there?s no easy way for the user of that application to enhance it with their own forms and reports, like they can with Access.

3. Never overestimate ?it just works?

Consider ? for a moment ? what?s involved setting up a professional database solution. To assemble the SQL Server Express example I just mentioned, you?d need (at a minimum) to complete these steps:

  1. Install SQL Sever Express. Make sure a number of configuration settings are correctly in place so that the database service starts up on command.
  2. Download SQL Server Management Studio so you don?t need to create databases at the command line, using SQL commands.
  3. Create the database, and its tables. (This part is almost as easy as Access!)
  4. Choose a programming language, database library, and development environment. Maybe you?ll pick something like Visual Studio Community, which will helpfully bundle these pieces together.
  5. Make a connection to your database in your code. Depending on your approach, you might be writing your own code or using a code-generation tool like Entity Framework.
  6. Here?s where the fun begins. In order to access the database, you need to grant the correct database permissions to the account running your code (which is often not your account). In my years of teaching programming, this has never failed to present a stumbling block for coding newbies.

Image for post

This sequence doesn?t actually make anything. It?s just what you need to prepare your environment. Compare it to the startup cost in Access. There is almost no way to fire up Access, create a database, and have it not work.

It?s difficult to put price on convenience, but the attraction of something that ?just works? has turned many ordinary technologies into overnight sensations.

Image for post

So the answer of why Access is still alive, a somewhat embarrassing zombie standing in the corner of a party where it definitely wasn?t invited, is simple. Access works for ordinary people. The fact that it encourages bad habits, and the fact that scaling it up invites near-certain catastrophe is beside the point. As long as no one wants to build a tool that gives ordinary people as much power with as little complexity, Access will shamble along, unwelcome and all-but-disowned by its creator, but still with a purpose to serve. The real question is do we dare build a database-for-everyone toolkit to replace it?

For a once-a-month email with our best tech stories, subscribe to the Young Coder newsletter.

12

No Responses

Write a response