Be prepared for a slightly ranty post, but with a twist (i.e this time not about infographics).
I’ve been developing applications using Microsoft Access for over ten years now, and I still find it an extremely easy to use, versatile and powerful application development platform. I still regularly build systems with it today, although my time is now somewhat split between Access, Excel and ASP.
About a year ago I met with a colleague from another organisation to discuss a regular annual return we send to report on our organisations progress. In these talks we discussed data quality and how we as an organisation store and analyse our data for export. The bulk of the data I access for reporting either comes from information stored in an old and somewhat clunky Access DB, and the rest from a number of linked SQL server views containing information from our primary MIS. It works well, and the queries/forms I’ve created provide all the information and reporting that we need.
This ‘data expert’ spent a great deal of time explaining how we shouldn’t use Access for systems development as it is ‘clunky’, ‘unsecure’ and ‘unreliable’ as a platform. At the time this bugged me somewhat but I let it go.
Earlier this week at a conference in London I bumped into the expert again, and the first thing she asked was whether we were still using Access to develop some of our business tools. I confirmed and I got a ‘look’. The ‘look’ said ‘you need to move with the times, mate’. We went our own separate ways and I started thinking about things..
Over the past couple of days I’ve come to the conclusion that she is wrong, and has probably only ever seen the ‘bad’ side of Access usage (and believe me, I’ve seen some really bad Access databases in my time).
I do agree that, at enterprise-level it’s certainly not the best platform as a standalone DBMS – you’d be much better served to have your data stored in one of the enterprise-level DBMS systems, such as SQL server. That’s pretty much a given, especially from a multi-user perspective.
Where I disagree with her most is in terms of using Access to develop a reasonably sophisticated front-end, with data stored in a separate and scalable database – in fact this in particular is one of the areas in which I think Access still holds its own. With the use of careful planning, a decent, reliable, secure and versatile application can easily be put together, but I think only as long as the application is developed properly and sensibly.
What do I mean by properly? Here’s a brief list:-
- Controls and forms should be unbound. Creating, deleting and updating records should be carried out using VBA to ensure reliability and properly defined control. Access wizards and bound controls don’t provide enough data validation or error-checking. Any decisions on how to deal with data shouldn’t be left entirely to the user, and the system should have adequate business logic inbuilt to make those decisions and act accordingly. With a decent grasp of VBA it’s actually very easy to implement such logic seamlessly and transparently to your application.
- Data shouldn’t be stored locally in an MDB file if possible – any data used by the application should be stored in separate tables, linked for example using ODBC and whatever necessary authentication.
- Access applications should be locally installed for each user, and not stored on shared network areas, where files are at risk of corruption or accidental deletion.
- Security needs to be implemented properly.
I’m sure there are more examples of ‘proper’ development I could highlight, but I’m tired, and my main point is that I don’t feel that there’s anything wrong with using Access as long as the application is developed properly, securely and using VBA and robust programing practices rather than macros, bound controls or wizards.
Before anybody feels the need to respond negatively to my comments, I want to clarify that I’m aware that there are many better systems to develop in than Access. I merely find it irritating when people dismiss it out of hand, especially if they aren’t aware of what the tool is capable of if used sensibly.
Another similar view on this can be found here (I even felt the need to use the same post image!)
Any thoughts or comments? Keep them constructive, please.