, 6 comments , ,

Access: Why not?

I blogged a few times recently about ways to make Acess databases do kind of what you want when you’re programming with .NET. There was this one about multiple JOIN syntax. Then this one about “parameter” errors. And finally this one about weird column names. Yuck!

Besides these weird little nuances, Access can be a pain to work with because:

  • It’s more of a file-based, structured data store rather than a true database;
  • There is essentially no security;
  • It doesn’t have much in the way of referential integrity features;
  • It lacks stored procedures and triggers;
  • It doesn’t have constraints as we know them;
  • It can’t handle large volumes of data;
  • Performance tuning is meaningless;
  • Its data types are kind of a mess; and
  • It’s weirdly not-quite-SQL syntax is hard to adapt to.

There’s this one guy following me on Twitter who, every time I posted a link to one of these items, replied with something to the effect of “Best Access tip: don’t use it.” That’s not a crazy thought, but it’s wrong.

Here’s why

I have one particular application running in the field, on a pen-tablet kind of PC. There’s  a central database on the customer’s main office server, and the pen-tabs (one or more of them) keep coming home to hook up to the network and push new data back to that server.

My tablet PCs don’t have wireless access to any network, and obtaining that would be prohibitively expensive considering how the application is deployed.  I could have used a single-user database engine, maybe the scaled-down versions of Oracle or SQL Server, but those things honestly do have some admin overhead. Access really doesn’t.

I could have built the central database, likewise, on a “real” engine, but the client already works with Access for many existing tasks. We’re not managing that much data, perhaps ten megabytes at the high end. There might possibly be three or four users on the system at once.

You know what?

In that scenario, I don’t see an urgent reason to push the client off something that they know, that is easy, and that works for them. Performance in terms of transactions per second is poor, in a technical sense, but it’s still much faster than they can type or even read. We don’t get deadlocks. Security is not a priority. The client’s staff can create and run reports right out of Access if they like.

It’s simple, simple, simple. Best tool for the job. It’s also–considering almost everyone already has Microsoft Office–essentially free of cost. Notably, if there are problems with the Access installation, you as the developer probably won’t have to deal with them.

I don’t suppose Access is ever the only solution to a problem. But in my experience it’s frequently a reasonable one and it spares a lot of drama over acquiring and installing a database engine.