In the nearly thirty years I’ve worked in the IT field, I’ve lost count of how many times executives, engineers, architects, administrators, technicians and developers have complained about some of the ramifications of Microsoft Access which cause them great pains. All sorts of colorful language and catchy phrases, which boil down to a strong dislike for that one lonesome part of the Office family. However, in many cases, the precise “root cause analysis” is not there. So, why is that? What exactly is the reason so many IT “professionals” seem to dislike this product?
Let’s paint a small picture of how it compares with it’s bigger, stronger, richer, better-looking cousin: SQL Server.
Access vs. SQL Server
Access, being part of the Microsoft Office suite, is intended for use as a client-side resource. Like the other apps (Word, Excel, etc.) it was designed to install and run on one computer, and create documents to share with others.
SQL Server, like other “enterprise” class relational databases such as Oracle, DB2, Informix and SyBase, are intended for server-side use. Everything about it’s installation and operation is intended for many users at one time, from other (remote) computers.
Another way to state this difference is that Access is intended for fewer users, and SQL Server is intended for many more (concurrent) users.
But there’s more.
Access is more than a relational database tool. It’s actually, believe it or not, a bundle. That’s right, a bundle. Among the additional pieces, it comes with a report authoring feature, a forms authoring feature, and a programming language (VBA and VSA) as well.
SQL Server, by contrast, offers more robust alternatives for each of these, BUT, as soon as you start talking about development (forms, applications, etc.) you’re going to consider Visual Studio. You’ll consider something else for reporting as well, even with SSRS, you’ll be looking at SharePoint and Visual Studio and SQL Report Builder, at the very least.
In other words, Access is a cordless powertool kit with the cute plastic carry case. SQL Server is a Home Depot or Lowe’s store.
But, back to the first paragraph above: SQL Server is intended for heavier concurrent use than Access is. Ask any developer, even those who would sacrifice their mortal lives before admitting Access has any faults, and they’ll admit that it doesn’t hold up very well when the number of concurrent users hits 1,000 or 10,000. The architecture on which it was designed is simply not capable of isolating and managing that sort of demand without bottlenecks occurring. SQL Server is designed just for that however.
To put it another way, when Access tries to handle 10,000 concurrent users from a single shared instance, it responds like how my childhood dog Curly would respond in the waiting room at the Vet’s office, when a much-bigger dog showed up: He’d shit a path all the way across the room. What a mess.
The Crux of the Pain
So, what’s the big deal? The big deal is how the programmatic and forms aspects are valued as an easy on-ramp to developing custom applications. That in and of itself isn’t really an issue. It’s arguably a benefit, in the short-run. But in the long-run, it means that API version dependencies suddenly matter a great deal. You build a bunch of forms and reports and glue it together with a bunch of custom code. Now you want to pick all of it up and drop it on another version of Access, but then you discover it doesn’t work.
If it was just a database, it would (should) work just fine. Data is data. Tables, queries (views), and functions are easily ported between database platforms, even non-Microsoft products. But the third-arm (custom applications) is where the model becomes non-resilient.
Put another way, think of what happens when you build a custom console for your favorite car. It fits perfectly, and really adds new capabilities, like hidden compartments for your stash of drugs and money, firearms, passports, and secret agent disguises. Then you trade that car in and get a different make and model. Now your cool console doesn’t fit at all.
Cases in Point
This is the single most common scenario/situation I’ve encountered:
Company “A” wants to upgrade all 150,000 of their desktop users from Office 2007 to Office 2013. They run some inventory analysis and discover roughly 400 (that’s a conservative number actually) “custom Access applications” in the environment. Every single one of them has to be copied to a test machine which has Office 2013, in order to test it.
Problems are found during testing? No problem, contact the developer for help. What’s that? The developer is long gone and nobody has any idea how the code works? Ouch! Well, the Sales department, who reported the test failure, says “since IT is demanding we upgrade, then IT should foot the bill to adapt the code. Not our problem.” Effectively sliding the bill back across the table to you to pay.
Oh, and did I mention that they also found 234 third-party (retail) add-on products which only work with Access 2007? Good luck with those (and the potential upgrade costs).
Now the entire project hits a brick wall until executives make the critical decision:
- Acquiesce and pay to allocate some Access developers to make the app(s) work.
- Allow the Sales department to stay on Office 2007 until a solution is found “someday”.
- Deploy the Access 2007 Runtime and run in a split-mode environment with Office 2013 installed. A similar option is to host the older Access version in a VDI or remote application service (Citrix, Xen, RemoteApp, etc.). Same result, different pathways.
- Tell the Sales department to suck it and tell them to find their own solution that works with the new platform.
- Kidnap the developer from his house trailer, bring him back to the warehouse in a white van, strap him to a chair and attach electrodes to his sack and apply enough current to convince him to cancel his retirement plans.
Obviously, each of these has its own unique problems to consider. Let’s review each of these…
- This can be expensive and very time consuming. And since you’re introducing developers into the process which have zero subject matter expertise, expect it to be a painful, costly ride. Careful attention must be maintained by management to insure both sides don’t lose their grip on emotional maturity as well. And worse: You’ll have to retain all of that experience and knowledge for the next Office upgrade project. Awesome! It’s like walking out of surgery and hearing the doctor say “I’ll see you in a month to do this again!“
- Now you have to support two platforms. You’ll also have to deal with the gaps when Microsoft cuts off support for the old platform (if not already). And what happens if the VDI solution you choose will no longer work with an OS which is compatible with the out-of-date Office version? And don’t forget all the 32-bit and 64-bit stuff.
- Again, a split-platform environment adds complexity, potential problems, more complicated troubleshooting processes, and more complicated imaging and configuration management processes.
- This defeats the customer-centric SLA mindset that the ITIL kool-aid drinkers are aiming for. It can also lead to a short career. A common example of this is the Finance department saying that their particular Access app and/or third-party add-on is critical to tax and payroll processing. (queue the record-scratch sound effect here)
- This would be most entertaining, but bad for an IT career plan. I have known managers that seriously considered this option however.
The real problem is the blurring of the distinction between a “database” and an “application development platform”. When decoupled, they are much more flexible, adaptable and portable. When combined, they are like a drowning person pulling a rescue swimmer down into the water.
What to Do?
As painful as it can be, the only solution that I’ve seen that “works” is to assist the customer (department) with porting their Access applications out of Access. The process usually goes something like this:
- Copy the data, tables, views, etc. to a SQL Server host
- This may require periodic or scheduled syncs to keep the data fresh
- Work out a desired platform model:
- desktop forms, web forms, etc.
- desired devices where it will be used
- internal only, external only, or both
- Migrate the interfaces (mock-up)
- Migrate the logic
- Turn over the UI/UX and logic (code) to the customer to maintain
- Turn over the DB to the DBA/DBD team
Concerns about Costs
One of the most common worries is “how much?” It’s easy for developers to lose their minds in this situation. I’ve seen it. They react like hearing a strange sound outside the tent in the woods at night. Is it a frog or a pack of hungry bears? Oh no! Grab the guns and start shooting! Remember that scene in “My Cousin Vinny”? Hold on.
Since Access apps are almost always aimed at relatively small numbers of users, a great option is SQL Server Express, which is “free”. Read the licensing terms and technical limitations obviously, but rarely have I seen a case where it wasn’t perfect for the challenge. If you’re not beholden to Microsoft, MySQL or Postgres are also fine.
As for the forms and reports side of the house, you can almost always get away just fine using Visual Studio Express, which is also “free”. I’ve seen more than enough cases where all the work put into Access forms was easier to do in simple ASP or PHP with a text editor. Remember to say out loud, “we’re not reinventing Amazon.com here” when it seems really scarry. (unless, of course, you are trying to reinvent Amazon.com)
Aside from funding concerns, there’s often hesitation on the part of the developers to let go of Access. It’s a comfort issue. I get it. Like the scene where Tom Hanks is letting go of Wilson (Castaway reference, sorry). But I’ve yet to see an Access developer, who reads up on what SQL Server provides them, and not watch them have an accident in their pants. Hand them a book on SQL Server development, followed by a roll of paper towel and a cold drink. They’re going to need all of them. And, by the way, a rejuvenated, re-energized developer is often more productive than they were before. Extra bonus.
I look back on the time I spent inside of MS Access, and how my good friend Andy showethed me the lighteth of thy SQL Server. My world was changed forever, and that was on 6.5. Now on SQL 2014 I hate looking back even two versions. I often think of a line from the movie Beerfest, where the big German guy says “I remember my first beer!” but I substitute “database” for beer (even though I still like beer).
The rest of Office doesn’t run into the same severity or scale of problems that Access introduces. The most common issues reported with Word, PowerPoint and Excel are with formatting and layout. Those are usually easy to adjust and fix. However, when an application is involved, it often impacts business processing and productivity. The sooner you reduce or eliminate your dependence on Access as a business-critical tool the better.
Technically speaking, you’re mitigating a focal point of potential failure. Rather than one “thing” failing and impacting an entire operation, you split it into two or three parts and now you have fewer chances for all of them to fail at once. But more importantly, you end up with a more flexible and less constricting situation.