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.