Giving Microsoft Access a Seat at the Table

2

September 2, 2009 by Mike Hillwig

Ask any DBA what they think of Microsoft Access, and you’ll rarely see a smile. Perhaps you’ll get a laugh, but most DBAs will turn up their nose. What I’m about to say is probably blashphemy in the DBA community, but when used properly, Access can have a place in some environments. I pray that none of the developers with whom I’ve worked ever read this.

Let me preface this discussion by reminding you that most of my experience as a DBA is in smaller environments. If you’re in a large enterprise, Access shouldn’t even be an option. Most of the DBA bloggers deal with terabytes of data and thousands of user connections. I’d never tolerate an Access database in those environments, either.

But lets say we have a small application for a workgroup that only has a few hundred megabytes of data with no more than a hundred users. Access can be entirely practical, provided one simple rule is followed–no local Access tables. When you pair Access as a front end with a SQL database backend, this can be an incredibly powerful combination.

In this combination, you can leverage the power of the SQL DBMS with the ease of use of an Access database. Better yet, this combination allows you to enforce security, constraints, and backups on the back end, where they should be. One of the biggest complaints about Access is that it’s security features don’t measure up to SQL Server. Instead, we can leverage the power of SQL Server’s security features.

Access backups are restricted to that one file that holds everything. SQL Server handles backups much better. The best part of this is that if the Access database gets corrupted, we can recover from ANY recent backup without data loss. As the DBA, I’m responsible for backing up the data, and nothing in this scenario changes that.

Don’t get me wrong. I rarely advocate the creation of an Access database. In fact, I’ll be the first one to point the user to Sharepoint for small applications. Still, Access should have a seat at some tables, even mine.

2 thoughts on “Giving Microsoft Access a Seat at the Table

  1. […] Giving Microsoft Access a seat at the table – but only if you put a whoopie cushion down first. (Ha ha ho ho.) […]

  2. Ben Bolte says:

    Especially for DBA-level maintenance, Access Projects (.adp files) are an excellent way to quickly get into the data in a specific db. I use it often to edit control tables that I have no UI for. One can build nice little forms in short order to assist in either viewing or editing data. Yes, Access can be heinous plague upon any SQL Server, but “projects” utilize the backend much differently so they have a considerably lower footprint. (It’s really not much different from SSMS so far as I can tell.)

Leave a Reply

Your email address will not be published. Required fields are marked *