Concerning Microsoft Access

I’ve been wanting for some time now to write about Microsoft Access. There’s a lot I could say — Access and I go back a long way — but for the sake of brevity and hopefully staying out of trouble, I’ll just focus on…

What’s that you say? Staying out of trouble? What kind of trouble? Well, let me tell you. Or maybe I could show you.

Open up Stack Overflow (the hugely popular programming Q&A site) and pick any random topic. C#, for example, or Java, or C++, or SQL. You’ll see a lot of questions and a lot of answers. Some of the questions will be thoughtful, well-presented, even insightful. Others will be a little lacking. Some of the answers will be correct, positive, and generally helpful. A few answers will be wrong. There will likely be downvotes and comments explaining why. There will be some snark, some needless sarcasm, some ugliness, but for the most part nobody’s day will have been ruined.

Now look at the ms-access tag. Programmers have engaged in holy wars since well before my time. I can respect the struggle, for sure. Spaces are better than tabs (four is the correct number), the brace should go on the next line, vim is definitely better than emacs, and as it turned out, Microsoft actually was an evil empire back in the 90s (before Google took their place). But the discussions about Access, especially about its suitability for production use — I don’t know, they just seem to be a little more intense, a little more defensive, even a little personal.

It’s just my opinion, probably more of an impression, or maybe I just assume, that the people who are touchiest on the subject are most likely to be the ones whose primary focus is Access. Actually, I‘ll go so far as to proffer a general rule: developers who are familiar with multiple languages, paradigms, techniques, etc., tend to be less dogmatic about one particular approach but to focus, instead, on the best tool for the particular task at hand. If all you have is a hammer, everything looks like a nail, and even if it doesn’t, you may decide to bash it anyway.

In that same vein, and with the goal of being as inoffensive and uncontroversial as possible—although, as Dijkstra said, sometimes we end up telling truths that might hurt — I’d like to discuss Access, not from the perspective of good vs. bad, but more in terms of suitability for specific situations. In other words, how can I decide whether Access is the best option for the specific situation I’m facing?

Create Your Own Business Application

Microsoft’s information page on Access makes a big statement:

Access is an easy-to-use tool for creating business applications, from templates or from scratch. With its rich and intuitive design tools, Access can help you create appealing and highly functional applications in a minimal amount of time.

“Business application” is a very, very broad term. Peoplesoft, SAP, and Salesforce are all business applications, but, of course, Microsoft isn’t suggesting you could build those with Access. In fact, Microsoft has always been a little cagey about exactly how big of an application we were meant to try to build with Access. But for the sake of a meaningful evaluation, let’s focus on what most people use Access for.

Say I’m the general manager of a small manufacturing firm. I have an IT guy that comes in and takes care of our Internet and printers and stuff, but I don’t have any kind of technical people on staff. I’ve been keeping track of my customers and orders in Excel spreadsheets, but the more we put in there, the messier it gets, and I’m afraid I’m going to lose something. And my business manager is having to work overtime creating, printing, and sending manual invoices, RMAs, POs, etc.

Or maybe I’m a department manager within a larger company. We’ve got corporate IT but they’re hard to work with and take forever, so I’m basically on my own. We moved to a paperless workflow several years ago when IT came through with the big imaging initiative, but our processes haven’t improved any. Actually we’ve gotten slower because we can’t do the same things with the imaging system that we could do with paper. We need a way to track our work.

These are the types of situations where Access always seems to pop up.

There’s a meeting, and the team is talking about the problem, and nobody seems to know what to do, and some scruffy young guy (who is he, anyway?) speaks up and says, “you know, I could build you an Access database for that. Wouldn’t take more than a couple of weeks.” And so it begins.

Or somebody Googles and comes up with a development firm that specializes in Access applications. Discussions are held, contracts are signed, plans are made, and an engagement commences.

Just as an aside, not to criticize in any way those firms that are doing quality work in Microsoft Access, but the very fact that there are so many professional Access developers does sort of beg the question whether Access is really a tool that most business people can use to “create appealing and highly functional applications.” Remember, Cobol, originally an acronym for Common Business-Oriented Language, was intended to be used by business people, not programmers. We saw how that turned out. Even the BASIC language (Beginners’ All-Purpose Symbolic Instruction Code), from which evolved Visual Basic for Applications, which provides most of the advanced functionality within Access applications (more on that later), while it inspired a number of beginning programmers, never really proved suitable for general business people.

So how is our general manager of a manufacturing firm, or our department manager in a larger company, to decide? Should they let the scruffy kid go ahead and build the application (and can he really get it done within a couple of weeks)? Should they sign the contract with the development firm?

Factors to Consider

Managers make business decisions all the time. Whether and how to invest in information technology is a business decision like any other. It may feel less intuitive than the other decisions a manager must make, because the subject matter is less intrinsic to the nature of the business, but it’s a business decision nonetheless. So it should be approached using the same methodology.

Key Concepts

Here a few concepts that are often applied in business decisions, that very much apply to information technology decisions as well.

Strategic vs. Tactical: Strategic decisions are typically those that have a broader and longer-term impact, and are therefore made at a higher level of authority and with a more rigorous deliberative process. Tactical decisions relate more to day-to-day operational concerns. Investing in information technology is almost always a strategic decision, and a very consequential one at that.

ROI: Return on Investment is, in simple terms, the value obtained from an investment as compared to what was invested. ROI can be negative or positive at any given time. IT projects are often evaluated in terms of the amount of time it will take to achieve positive ROI.

TCO: Total Cost of Ownership is a measurement of the cost of an asset over its lifetime (as opposed to simply the upfront cost). TCO for any kind of software is often many multiples of the initial investment.

Technical Debt: Software analysts use the term “technical debt” as an apt metaphor for what happens when a business investing in a software asset prioritizes quick delivery over robustness and maintainability. Just like with actual debt, taking on technical debt is sometimes a good business decision. But technical debt can be just as limiting as actual debt, and can be harder to pay down because it’s not always as easy to identify or to explain to stakeholders.

This is just a quick overview of each of these concepts. But they’re all essential to the discussion that follows, so you may find it valuable to dig a little deeper into any that you’re less familiar with.

Maintainability

The scruffy guy says he can build the Access database in a couple of weeks. First off, we should be clear from the outset that it’ll actually be at least a month, probably two. He’s a real programmer (even if he doesn’t know it yet), and real programmers always underestimate things.

The professional development firm proposes one week for requirements and design, three weeks for development, one week for user-acceptance testing and defect resolution, and one week for implementation and training. They’re probably right. Did you realize you were getting into a complicated project?

In either case, the initial implementation won’t be exactly what you want.

The scruffy guy will build something, you’ll tell him it won’t work that way, he’ll make some changes, you’ll say it really needs to do some other thing he hadn’t thought of, he’ll work on it some more, he’ll tell you his manager is getting on his case about spending so much time on this (I failed to mention he’s not even on your team), he’ll work a weekend to get it done, and you’ll put it out there on the file server for the team to use.

Or the consultants (that’s what they’ll call themselves) you’ve hired will show up in their blue jackets and tie up your schedule for a week, then disappear for three weeks — they’ll send you status reports, but it won’t help — then they’ll show up with the finished product ready to test. It will be wrong in several key ways. There’ll be hand-wringing, discussion of change requests, delays in the project, then they’ll get it done anyway, but it’ll still be sort of incomplete.

It’ll never be as good as you’d hoped, but it’ll reach a point where you can (or have to) stop working on it and just start using it. Your TCO now includes the initial investment, but the meter has really just started running. Your ROI can’t possibly be positive yet. You probably have more technical debt than you can imagine.

Six months from now, you’ll want to make some changes to the application. There’s a new regulation or corporate policy and you have to track some new piece of information, or store it encrypted, or only allow certain people to work with it. How much will that cost? How long will it take? How will it impact your current operations, which have come to rely on the application?

A year and a half from now, another team wants to start using your application. Will it support the other team? What changes will need to be made? How much will that cost?

Three years from now, you need to make some IT infrastructure changes because of a merger or buyout or corporate reshuffling or something. What will it cost to migrate the application to a new infrastructure? Is that even possible?

Twelve years from now, you’re still running the application. The scruffy guy is now a director at another company. The consultants have retired or gotten real jobs (so they can eventually retire). Most of your team members are now using Macs or BYODs, but one or two business-critical functions still use the original application, so you’re paying big bucks each month to provide some kind of virtual desktop thingy to get to it. There’s still no real plan to sunset it.

Right now in the present, when we’re making these big IT decisions, we have no way to know for sure what the future will bring. That’s why it’s so important to think about TCO and technical debt up front. And why it’s so important to view any kind of IT investment as a major strategic decision, no matter how acutely tactical it feels in the moment.

Risk

Our IT investments can be among our biggest competitive advantages. But they can also be one of the biggest sources of risk. Any new component, application, or system that we install could have potential risks to our operations and even to the viability of our business.

Have you ever been hacked, even in a small way? It’s not a good feeling. Especially if you’re responsible for managing sensitive information, information that could be used to harm you or people and organizations you care about.

And there are other risks that are less deliberate but no less scary. Where is the data stored? Is it backed up? Even if it’s backed up, is there a practical way to recover it? How recent was the backup? How long will it take to restore service? How will that impact our business commitments and our customers?

As businesspeople, we have a responsibility, often fiduciary or legal, but always ethical, to protect the information entrusted to us. Our IT investment decisions have to reflect that.

Value

A lot could be said (and has) about measuring business value. When we’re facing an acute operational need, we may tend to focus on solving only the immediate problem. On the other hand, we may the opposite inclination, to try to find a way to solve all of our problems with a single solution. As with most things… many things, anyway… or at least some things… the truth is often right there in the middle. In other words, while we should always consult our friend YAGNI and listen carefully to what she says, there often is some real value in thinking ahead about how we might be able to leverage today’s investment to solve other problems or to create competitive advantages tomorrow. And even if we can’t really see those benefits right now, it just makes sense, all other things being equal, to opt for a solution that provides a foundation to build on.

So now we have some key concepts, and some criteria for evaluation. How do we use this information to make a good decision?

General considerations for comparison

We’ll compare Access to a few other common options below. Before we get into those specifics, I’ll offer some general thoughts on how our evaluation criteria apply to Access applications. If I’m going to get in trouble, here’s where it’s going to happen.

Maintainability

Access applications have often proved challenging to maintain over time. Here are a few reasons:

Version Control. There’s no reasonable (in my opinion) way to version-control the database schema, queries, macros, reports, or even the VBA code. This not only makes it harder for more than one person to work on the application at a time, it also makes it much harder to keep up with changes over time. It’s very common to see file server directories filled with backup after backup, each with a different way of renaming the file to make it clear that this isn’t the current version, it’s a backup we saved before we made any changes so we wouldn’t break anything. Other tools have much better options for maintaining version control.

Separation of Concerns. An important principle in modern application design (that is, from the 80s or so onward at least, if you count from the widespread adoption of object-oriented programming) is separation of concerns (SOC), that is, separating presentation logic from business logic from persistence logic from whatever other specialized form of logic may be needed. SOC is hugely important to maintainability. It allows you to isolate changes in one aspect of the system — moving to a different database, or changing a custom calculation, or rebranding the UI — from affecting the rest of the system. Access doesn’t naturally provide much in the way of SOC. It’s possible, with a lot of VBA, but it’s not easy, and from what I’ve seen, most people don’t do it.

Environment. Access is a Windows-specific tool. I’m sure it’s possible to use it on other platforms in some way, but it’ll never be the same experience. Even five years ago that might not have been a big deal. Now it is. Microsoft still has a strong hold on the corporate desktop, but it no longer seems reasonable to assume that it will always be that way.

Expertise. A crucial consideration for any kind of technology investment is whether the skills to maintain that investment will be highly available over its entire lifetime. There are currently a huge number of people who are skilled with the graphical aspects of Access: queries, macros, etc. But as we said before, to really do anything even remotely complicated, you have to have VBA skills. Let’s just be honest: VBA is a dinosaur. There haven’t been any significant enhancements to the language in over 20 years. Seriously, Cobol is evolving faster than VBA. Young programmers aren’t learning VBA.

It’s also worth noting that database developers who are experienced with large-scale commercial database platforms, such as SQL Server, Oracle, or DB2, often have difficulty working with Access. Access uses some of the same techniques as the big guys — SQL, ODBC, relational terminology — but in a way that’s different enough to be a problem for people who don’t have specialized experience with Access itself.

Risk

Access applications present risks that, while not being unique to Access, are generally harder to manage in Access. Here are a few of those risks.

Version Control (Again). Besides being a maintainability issue, the lack of practical version control is a significant risk for Access applications. One bad change, and the application can become completely and permanently broken, with no way to recover it. Good engineering and backup practices can mitigate this risk, but it’s exceedingly difficult to control for the human factor if you’re not able to use a version control tool.

Granularity of Data Access. Most other tools provide fine-grained control over who can access what data. It’s probably possible to mostly emulate that with Access, but it’s exceedingly difficult, and there are almost always ways around things like that in Access.

Corruption. Access databases have always struggled with corruption in multi-user environments. There are lots of reasons why, and the experts sometimes tend to blame the users (or developers), which is true but beside the point. Regardless of the reasons why, it’s just a lot easier to inadvertently wreck a database with Access than it is with other tools. Database corruption in a production application should be almost completely and easily avoidable, and, in fact, it usually is, but not with Access.

Value

Access often provides quick ROI, and is well-suited to solving specific problems. But its long-term value can be very limited.

Extensibility. Access provides ODBC connectivity, which allows you to connect to it from other applications. But modern applications don’t interact at the database level, and haven’t for a long time. Modern applications expose APIs. It’s not impossible to do that using VBA, but there’s really not much in the way of tool support for it.

Scalability. Access databases have much smaller file-size limits than other comparable platforms. It’s possible to split the database into a front-end/back-end configuration, and even to use a different tool, such as SQL Server, for the back-end, but in the process you lose a lot of the simplicity that makes Access worthwhile.

Actually, most of the things I’ve said here about Access are probably only 98% true. There’s almost always a way to “do that with Access.” But let’s not lose sight of what we’re trying to accomplish here. The main value proposition for Access is its ease of use. If it’s actually much harder to do everyday things, like work in source control, or expand the database from 2GB to 2.1GB, or separate your business logic from your presentation logic, aren’t we losing a lot of that value?

Access vs. ?

So the scruffy guy (who we should’ve been nicer to I guess), or the Access professionals, are recommending we build an application in Access to track our orders or our customers or our documents or whatever. But what are the other options?

Integrate with existing systems

“You sound just like our corporate IT guys!” Yeah, yeah, I know. I mean, it may be a long shot, and it may not even be practical, but if you’ve got an existing application that you can integrate with, that’s at least worth considering.

A lot of ERP systems and other, similar, types of enterprise-y software tools have lots of extension and integration points. It really may be possible to persuade corporate IT, or to engage outside help if appropriate (or you don’t have corporate IT), to find those extension points — the vendor will be able to provide documentation — and try to come up with a solution to leverage that.

How does this option compare with Access?

Maintainability is probably somewhat better. It depends on the specifics, but most likely you’ve got version control, and some ability to do SOC. But you may or may not be tied to the same environment as the core system, and you are relying at least to some extent on being able to maintain expertise with that system. But you had those dependencies anyway, so overall this is a better option.

Risk is almost certainly at least a little better. In many cases, your data persistence layer (where you store the data) will be managed by the core application, so it’ll have whatever protections are already in place. Even if that’s not the case, you can choose a storage option that will provide better risk characteristics.

Value is probably significantly better. Rather than create a new platform to possibly integrate with in the future, you’re leveraging an existing platform. You may not be creating new potential opportunities, but you’re also not creating new risks.

On-premise desktop application

Back in the old days (circa 1999), VB6 + SQL Server provided a kind of “next level up” alternative to Access. After .NET came along, you could do VB.NET with WinForms + SQL Server, or switch to C#. Then WPF came along and killed WinForms, except it didn’t. Actually all of those things (except VB6 for the most part) still work in Windows 10 and are still reasonable choices. And the licensing model for SQL Server now provides a couple of pretty good options that are actually less expensive than Access.

And then there’s Electron, which lets you use super-widely-available web development technologies (HTML, CSS, JavaScript) to build a cross-platform desktop application. Microsoft has its own cross-platform desktop stuff within .NET, but they actually built one of their most awesome products, Visual Studio Code, using Electron. I’ve personally used VS Code on Windows, Mac, and several Linux distros, and it feels native yet consistent on all three.

So there’s a big future in desktop apps. But how do these options — either using .NET or Electron — compare to Access?

Maintainability is vastly better, for all the reasons it’s a challenge with Access. These tools work natively with source control, especially with Git, which is the de facto standard. They almost force developers to use SOC. The skills are widely available and current. The only place where desktop doesn’t clearly outshine Access is that, depending on exactly what you need, it might not be any more cross-platform than Access. But even that’s changing as Microsoft shifts more and more of its attention to the inherently cross-platform .NET Core library.

Risk is 100% better, for the simple reason that SQL Server — there are other databases that would work here, including a couple of good options with PostgreSQL and MySQL, and the same applies to them — provides all of the controls and safety measures you need to completely manage the data risk.

Value is better in many respects. Desktop applications can’t really provide an API, but they can be designed for some measure of interoperability. And because they’re distributed to individual desktops, they can be made to scale pretty much as needed.

Web application in the cloud

The sort of most extreme alternative to Access is also, in many cases, the most compelling. Modern web frameworks provide a look and feel that matches or even exceeds that of desktop applications. Hosting the application in the cloud means a monthly expense, often relatively modest, but it also means no need for on-premise infrastructure. If you’re part of a big company, corporate IT is probably arguing with somebody right this minute, either for or against the cloud, so that option may not be available to you. But if it is, it’s worth considering.

Here’s how Access compares to a modern web application in the cloud.

Maintainability is decidedly better, but with a caveat. You get all the pros of a desktop application, but complete cross-platform-ability. But you need to be aware that web technologies can sometimes have a short lifecycle. Five or six years ago, everybody was using jQuery. Now, we’ve sort of moved on (though it’s still very relevant and widely used). A couple of years ago, AngularJS was all the rage, and a lot of companies adopted it. Then its parents decided on a rewrite, with some major changes that not everybody liked. It still has a huge following, but the winds of developer opinion seem to have changed and Angular is reputed to be fading. You run into that kind of thing with web apps. React is huge right now, and has a very large installed base, and won’t be going away anytime soon, but it will eventually be old tech.

So that’s something to be aware of. But by the same token, most applications evolve over time anyway, and all of these tools are well-suited to support that evolution. That, and the fact that they’re so completely huge now that they can’t just go away. Like Java (and C++, and C, and Cobol), they’ll be around forever, even after they’ve lost their shine.

Risk is 100% better for all the same reasons as desktop, but even more so, because you’ve got easy access to geographically redundant backups and a whole host of safety measures. Could you be hacked in the cloud? Sure. But if your database is on-premise and your network is connected to the Internet, guess what, your database is sort of on the cloud anyway, and you don’t have the resources of Amazon or Microsoft or Google to keep it safe.

You also have many more options for what kind of database to use on the cloud, including some very good NoSQL options.

Value is also 100% better because modern data-driven web apps are usually designed for extensibility right off the bat, with APIs built right into the core application. In fact, the client application doesn’t access the database directly; it uses the API. So your extensibility is infinite. And so is your scalability, in practical terms, if you’ve got the finances to pay for it (which, hopefully, if you need to scale, it’s because you’re making money).

So I shouldn’t be using Access?

Wait, no, I didn’t say that. I will say that there are a lot of applications built in Access that are experiencing the very problems I’ve described. In some cases, Access was always the wrong fit. In other cases, they outgrew it.

But there are lots of companies running Access applications in the right setting and getting along OK.

Remember, we’re not trying to say Access is good or bad. You have to evaluate it in the light of your specific needs. Hopefully the information I’ve offered above will help you to do that.

At the same time, I don’t want to be disingenuous about what I am saying here. In my personal opinion, based on my own experience, the number of cases where Access provides the best value for the money is actually pretty small. If you have a very narrow set of requirements, and especially if you have a very limited budget and already have Access, it can be a good option. But there are many better options that aren’t necessarily that much more expensive.

Remember, though, that regardless of your project, and regardless of your budget, software is always a strategic investment, one that carries a significant potential for value but equally significant risks. Be careful to avoid short-term tactical thinking. Be aware of the total cost of ownership. Research the options that are out there. Find a good partner in the software industry — many will offer an initial consultation for little or no money.

If Access is the right choice for your business, use it with confidence, but be aware of the risks, and always keep an eye toward the future.