Microsoft Access gets a lot of bad press, especially from the professional developer community, who see it as a ‘noddy’ 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.
Microsoft Excel is an analytical tool, not a data storage system
Most people have Excel on their desktops, and many use it daily. However, very often it is used to maintain lists, or to record activity. Mailing lists, order summaries, inventory tracking, price lists – all of these and more are commonly found in spreadsheets.
This is fine, and many businesses survive very well using Excel in this way, but it can be a volume game. Once you get to the point where you start to duplicate too much information, or you have to go digging too deeply to find it, or you are searching for things that you need to remember (renewal dates for certifications, or vehicle MOTs and so on) then you might want to think about a move to a database.
Microsoft Access is a superb 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.
From 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! Just this week, 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!