Should You Keep Using Microsoft Access?

Andrew Comeau   November 7, 2019   Comments Off on Should You Keep Using Microsoft Access?

Full Disclosure – I Owe My Career to Access

I’ve been working with Microsoft Access off and on since 1997, just a few years after it was first introduced in ’92. I can realistically say that learning Access made my career in I.T.. I’d had some previous programming and database experience with Borland Paradox and an older DOS title so I had some idea of what I was doing when I decided to automate some of my work with the copy of Access 97 that I.T. had installed on my machine with Office.

I spent the next few years creating Access apps and learning about things like normalization and report design. I started out using Access macros, tentatively started playing with Visual Basic for Applications and then started creating sophisticated Access / VBA apps for other departments and facilities within the company. I started a website in 2000, wrote about Access and offered some samples for download. A few pages of introduction to Access that I wrote for a friend became a series of articles titled “Microsoft Access for Beginners” which brought a lot of web traffic and then became a book that didn’t sell very well but was appreciated by those who read it. In 2003, I took on my first Access client as an independent programmer. In 2005, the expertise I demonstrated with Access helped me get my first full-time programming job with Access and other languages. It’s continued from there.

This story is far from unique. Microsoft Access has been the introduction to database design and programming for many other developers and even the basis of careers and development companies. For others, it’s been a quick data management solution that integrates well with other Office apps when more sophisticated solutions weren’t available. Sometimes, it’s a cost-effective alternative to much pricier software. On one project back in the late 90s, I was asked to price some alternatives for a software solution and, when I mentioned the minimum price tag of $8,500 I’d found, the manager asked “So, what can you do for us in Access?”. The resulting app was in use years after I left the company.

Microsoft Access remains a great tool for rapid application development and ad hoc solutions. The fact is that it just works for people who have little to no development experience, even if it’s just for the purpose of prototyping a new app. The software includes a wealth of tools that simply aren’t accessible to the average user through any other single software, despite Microsoft’s repeated efforts. In the right hands with some conscientious effort, it can do amazing things.

Discovering The Dark Side of Access

For all its potential, Access does have its limitations that are highlighted by its wide use and, sometimes, misuse. The flip side of the quick development of inexpensive apps that I mentioned above is that many aren’t that well designed. Then, when the power users who understand them leave the company for greener pastures, there’s nobody left to support them. The poor design that Access allows can lead to inconsistent or missing data or data that’s hard to reassemble into usable reports.

Finding someone new to support that Access database is its own challenge. Finding a database specialist / programmer who is familiar and willing to work with this aging technology will continue to get more difficult. You’re more likely to find a programmer who urges you to upgrade to their favorite tools because “It’s 2019, dude …” or an Office power user who has a basic understanding of Access but not the intricacies of maintaining a multi-user database application full of company data. Either solution is likely to be expensive and time-consuming.

Then there is the complete lack of real security, data auditing or any support for cloud operations. These things are possible, to a degree, for someone who is really knowledgeable about database operations and programming but, again, those people are elsewhere, happily enhancing their resumes and earning potential with more modern tools. The lack of security is compounded by the desktop file format that compresses nicely into a ZIP file that can be easily e-mailed out of your company at any time. I saw this demonstrated when someone who had found me through my website e-mailed her entire client database, complete with social security numbers, asking for my help with an issue.

Then there’s the day that power user in the Accounting department discovers how easy it is for Access to view and edit the company data in SQL Server …

I discovered all of this in the years after I started working formal I.T. jobs. I learned other languages and tried to get away from Access, I really did, but my clients still use it, I support it and there are good reasons for that.

(Article continues below.)

The Middle Way for Access

Recently, I responded to a consultant on Facebook who was asking if he should migrate the Access app he’d designed for a client years ago to something more modern. I told him that if the data needed to be secured or shared between locations, then SQL Server was a better solution at least for the back-end. Otherwise, a well maintained Access application was perfectly fine.

The fact is that migrating data and users to a new application quickly becomes expensive and is more than many companies have the resources to deal with unless there’s a really good reason for it. The return on investment better be there because, otherwise, the in-house development of even a small app with the planning, inevitable bugs and business disruption can be more trouble than it’s worth.

As a consultant, one of the things I like about inheriting an Access app is that all the code is right there in two of the easiest languages to work with that you’ll ever find, SQL and VBA. Unless the developer has specifically locked down the design features, accessing the code is as simple as using Alt-F11 and the design environment for all objects is just a couple of clicks away. Also, virtually any answer about a specific function or how to accomplish a task is just a minute away via Google thanks to the large user community.

Compare that with the calls I’ve gotten about some custom app written in an unspecified language with an obscure database format where the missing developer has retained the source code and the program just isn’t working right anymore.

There are limitations on what Access should be used for.

  • It should not be used for confidential or otherwise sensitive data, not even temporarily.
  • If you really want a cloud-based or web interface, you’ll need to go with SQL Server or MySQL. Microsoft tried web forms for Access but they never caught on and are long gone.
  • Access still has a 2 GB size limit on the files. You can get around this by linking files but that’s going to be a pain after awhile. If you have that much data, you really should go with a more scalable database.
  • Once your app has more than 50 users or so, you should start doing periodic reviews to determine if it needs to be moved to SQL Server, at least for the back-end. Access can still be used as the front-end for SQL Server although it doesn’t have the same support for this that it used to.
  • If your company is using a network database software such as SQL Server, strict policies and oversight should be maintained for any access to the network databases by Microsoft Access apps, especially those developed outside of I.T..

To sum it up, Microsoft Access is still a valuable tool for quick development of small and medium sized apps. I resist giving up on a technology just because its been around for a specific length of time or there’s something else new and flashy out there. Software development is not a fashion show. As long as its limitations are kept in mind and its use is managed, Access has its place in the development world. Access also has a loyal community of users and stores a lot of data in a lot of places which means that it will likely be around for years to come.

Another perspective – Microsoft Access: The Database Software That Won’t Die by Matthew MacDonald

Categories

About Andrew Comeau

Organizer of the Ocala I.T. Professionals. Software developer and consultant based in Ocala, Florida.