Open source, the Access slayer

David M Williams
Wednesday, 06 February 2008 17:24

Opinion and Analysis

Forget vampires, there’s a world of evil in corporations across the globe infesting desktops everywhere. It’s the database product known as Microsoft Access but fear not: there is a chosen one, the [Silicon] Valley open source software revolution born with special strength and a destiny to fight Access.

Perhaps I’m overdramatic, but after being exasperated yet again by a corporate Access database which “needs a bit of fixing” I lamented “Access must die” on my personal, low-traffic, blog. I was surprised by the amount of mail I received from all corners of the earth which perhaps shows that “Access” is a popular search term – or possibly many others shared my sentiment, Googling “Access must die” themselves.

Let’s give it some credit: Access is accessible (pardon the pun.) It comes with professional versions of Microsoft Office. It’s on many desktops. It has wizards that let users make some quick tables, queries, forms and reports with nary an effort nor a skerrick of understanding about Codd’s 12 rules of relational database management systems.

Yet, it’s this same sheer accessibility which gets it into trouble. It’s more than ok for a home user to fire up Access and organise their DVD collection, by all means, or to run their scout group member list. But when a business opts to invoke Access and build a corporate database they’re opening up a world of pain – not just for them, but for the poor sap IT guy who ends up having to fix the inevitable problems.

Here’s what happens, and they’ve all happened to me. Someone will make an Access database and produce a bunch of forms. Then they’ll say “Can you put this on the web?” expecting it to magically run as is within a browser environment. Or before long, the one user running the database will turn into two or more, and before long the entire office needs it. Now, it’s great that the accountant or admin officer or some other subject matter expert produced a useful app but Access is not the environment to do it in.

Access is a file-based database; it’s not robust nor does it cater for heavy numbers of concurrent users. Before long, any Access database shared over the LAN will experience corruptions and problems necessitating use of its in-built Compact and Repair utility.

This is well-known to Access coders and the conventional wisdom is to split the database into two parts. All the forms, queries and reports go into a front-end database which can be copied onto each user’s computer. This then links to a back-end database which contains the raw tables themselves. This split minimises, but does not eradicate, the problems by reducing the amount of objects being shared.

However, move the back-end database or rename it and the front-end becomes useless until re-linking. Or, adjust the security on the file share where the database is housed and again it potentially becomes useless until rectified. This is especially true if the network doesn’t have a unified domain but has been developed as an ad-hoc collection of fileshares and workgroup accounts.

What to do? Please read on.

CONTINUED