Is Microsoft Access Any Good OR is it Dead?

Image from https://developer.microsoft.com/en-us/fabric#/styles/web/office-brand-icons

If you work in the database sector, then this is a question and half and will split a lot of people. Is Microsoft Access any good? Is it dead? It was first released in 1992, why do we still have it? Who the hell still uses it if it’s rubbish??

Two Opinions

So, there are people whose point of view is that no, Access is a big old set of pants and needs to end. And, quite rightly, they are entitled to their opinion.

Conversely, there are people whose point of view is that yes, Access is an amazing tool. And, quite rightly, they are entitled to their opinion as well.

Unfortunately, that doesn’t get us very far in answering the question, does it? And the whole point of this article is to try and give you an answer (I already know the answer by the way, and your’e not going to like it)!

Access is Awful!

So, let’s have a look at the negative side of life first. There are lots of reasons that people like me will give you as to why Access is a huge pile of steaming software excrement and it should be banned. Their arguments range from the reasonable to the reasonably ridiculous, but we are going to stick with a reasoned debate here:

  • Only has a maximum DB size of 2GB, which is terrible
  • Doesn’t do well with concurrent users, which is terrible
  • Jet database is terrible
  • Uses VBA, which is a terrible language
  • Limited SQL language, which is terrible
  • Uses and ODBC connection, which is terrible
  • It’s temperamental and inconsistent, which is terrible
  • If you wan to learn databases, Access is terrible – use SQL
  • It can be slow, which is terrible
  • Because it’s terrible (last one is not reasonable I admit!)

So, there are a few good reasons not to like Access. It’s relatively small database size (in today’s world) can be an issue for some people. You can only have a maximum of around 200 concurrent users before it really throws a major fit, throws it’s dummy out of the pram and starts crying, VBA is, I admit, terrible when there are much better things to use. It’s lack of full SQL is frustrating to say the least.

SQL?

There is a solid base that says if you want to learn databases, then don’t use Access. Use SQL as it is more prevalent in business, has a better language that can perform much better and SQL is much faster than Access. I hate to say it, but all of these are very valid points.

So, it’s pretty much a damning case against Access then yes? Well, not quite. Before you run off and get all SQL (or NOSQL) on me, just hang on a moment. That’s one side of the argument. There is another side…

Access is Awesome!

So, there is a more positive side of the argument, and that is that Access is excellent and should be celebrated! Access is a a great starter package, a brilliant entry to the world of databases and it should be lauded for the amazing package it is. So, in the same way, lets take look at some reasons:

  • Cheap! – bundled with Office
  • Easy to learn – it has an interface like most MS Office programs so is much easier to come to grips with
  • Wealth of free information for Access that is easy to understand – just look at YouTube!
  • Stand alone product – if you want SQL you end up having SQL Server, Management Studio and…
  • Small enterprises CAN make their own database to suit THEM easily and cheaply
  • Some large companies still have legacy access databases (!)
  • It’s a great way to get into the land of databases
  • Education use Access to teach the foundations of database knowledge
  • It comes with reasonable looking reports and forms – no need for extra packages

So, Good or Bad?

As we can see, there are some valid points for and against Access. It has some great features, is easy to use, is a good way a dipping into the world of databases and is cheap. Unfortunately, it can be slow, clunky, has size and connection restrictions and because most large companies have an SQL implementation, learn that instead and be more useful.

Image from https://hackr.io/tutorials/sql/logo-sql.svg?ver=1555309685

I could go on all day listing good and bad, I really could. Let me give you a real life scenario I was involved with:

Scenario

A small company had an Access database that was created by one of the directors when the company had 3 people. This person didn’t know databases, they just sat at a computer and worked it out. It contained all their clients and what equipment they had. The engineers diary was an Excel spreadsheet.

The company ran successfully on this platform for many years (around 15 years to be fair). Eventually, they needed something their engineers could reliably access from the field from a tablet device. Something that told them their diary for the day, and history of the equipment they went to service. As well as doing their paperwork electronically from the field.

They bought a package off the shelf for their industry that could do this, and had it modified to do exactly what they needed it to do. The data migration was a nightmare as there were no primary keys and “unique” customer references were duplicated. In addition, the customer reference in Access did not match at all to the finance package customer references.

The new package is based on SQL and because the company is still relatively small, it runs on a free SQL version.

  • It handles all the engineers diaries
  • Can be accessed by everyone in the company
  • Holds all the history of clients and their equipment
  • Is able to provide planned maintenance schedules and even allows pictures or files to be attached to customers
  • Reports are handled via Crystal Reports
  • An engineer can fill in a field report, get the customer to sign it, and email them a copy before they have left site.

The new package took 4 months to install and get going from signing on the dotted line to fully up and running with all data migrated.

Question:

If the company started out trying to implement an expensive ( think 15 – 20 years ago pricing) database solution, would they have been able to grow and succeed as well as they did? Or, did the “free” Access database provide a low cost growing platform that could be used until the company needed something better, by which time it was cheaper?

Conclusion

So here it is, the conclusion, the bit where I wrap all this up nice and neatly, put a bow on it and give it to you. Where I say THIS is better than THAT. Only, I am not exactly going to do that.

You see, Access has it’s place both in education and business, but, it depends on what you are trying to do and how large you are and how much data growth you are expecting.

If you are a small business with small requirements from your DB, then Access is probably the right solution. It’s cheap and relatively easy to use and scales well in a small business. So use Access.

If, however, You are a dynamic company generating lots of data and need to have many people concurrently accessing your DB, then maybe Access isn’t for you. If you need to start accessing via tablets and web apps (Access can do this by the way), and your data is growing, you need to start looking at SQL or other things.

I was once told by a senior DB engineer, as a rule of thumb, if a company is big enough to have it’s own IT department, it needs SQL. Whilst I am not sure that still holds today, it does still hold a certain value.

It’s like this: When you go out to eat in a restaurant, do you order a starter or dessert? The answer – which one fits your requirements best… I’m a dessert person by the way 😉

Image from https://media-cdn.tripadvisor.com/media/photo-s/0d/c1/fb/ca/starter-or-dessert.jpg

Leave a Reply