Microsoft Access gets a lot of bad press, especially from the professional developer community, who see it as a bit of an amateur solution.
This, to my mind, misses the point and potentially loses opportunities for clients and businesses to take advantage of using the right tool at the right time.
Spreadsheet versus database
A spreadsheet is a flat file of rows and columns – you can link to other sheets and even have dropdown options, and things of that nature, but it is designed as a tool for the analysis of data, and not for the storage of it. Once you find yourself opening a spreadsheet to look something up – a phone number, a due date for something, or some other similar detail, then you are ready for a database.
A database is a related series of tables holding individual items of data that can be reused and “assembled” in a variety of ways. Think of it like a child’s building blocks, which can be put together to build a multitude of different structures. Except with a database you never run out of the blue bricks – just use them again and again in as many different models as you like!
Microsoft Access is a surprisingly good user interface development environment
That’s not what you hear it being sold as, though, but that’s really what it is. One of the reasons that the developers I mentioned at the start don’t like it is because they don’t feel that it’s a real database. The point is, it doesn’t need to be – you can use any number of ‘real’ databases to store the data, and just use Access as the front end. This is the approach I normally take, and I can think of few development options that will get a database application up and running faster than Access.
Access does come with its own internal database, and that’s fine for building the solution, but once you start to share it between a number of users, and need to split the user interfaces from the centralised data, then you can have the types of problem (usually corruptions) that give the solution a bad name. So you work around it – it’s not a reason to discount Microsoft Access as a powerful business tool, and in fact the workaround is not really a fudge, but a way of doing it better in the first place.
You can use SQL Server, MySQL or many other database platforms as the back-end to a Microsoft Access solution, so there is no reason for it to be an issue.
Proof-of-concept to implemented solution
Because of the speed with which Microsoft Access can be developed, it is ideal for testing concepts, or for a final line-of-business system. If security is a big concern, then Access isn’t going to be top of the list as a solution – it’s a desktop application, and difficult to protect when set against a fully-fledged application, but for the majority of businesses I have worked with, this has not proved to be an issue, and many have stuck with the original implementation rather than rebuilding it again.
You don’t even need to have Access installed in order to run an application – you can download the free Microsoft Access Runtime, which allows databases to be used, but not designed or built. This, of course, is a good way of upping the security as well, since the ability to get into the design part of the solution is not available to anyone without the full version of Access.
Horses for courses
There is a caveat, of course, as there always is! Recently, I was with a client having this exact conversation about whether to carry on using Excel or to change to a database solution. They have a network of consultants who are responsible for using and updating these spreadsheets and producing reports for their clients, and they are all well-versed in Excel, and well used to working with it.
In a case like this, it’s best to carry on with the method that everyone is comfortable with – it never pays to impose a solution onto a business, as it will not get the buy-in from the team, and consequently it won’t be used properly and will fail to deliver the maximum business benefit.
So I won’t come in and force you to move to a database, but I will point out where you might be able to do something, and what the benefit (and drawbacks) might be. Then we can take a joint decision, and off we go!
Photo by Marvin Meyer on Unsplash