After seeing Brent Ozar's Top 10 Reasons Why Access Doesn't Rock, and seeing all the comments it generated, I figured I would throw my two cents in. Now it's important to understand that I'm speaking here of the database portion itself, which I have seen used more frequently than it should have, for supposedly enterprise class solutions. And with regards to this blog post, I'm going to speak from my former role as an infrastructure and security architect, not from the perspective of a SQL Server DBA. I realize that for small organizations, Microsoft Access may be a viable solution. It, like every tool, has its place. But within the enterprise, I think there are better solutions. Here are some of the issues I've encountered having to support Microsoft Access.
The Locked Database
Access is a file. While there is a mechanism to allow multiple users to connect in, it doesn't always work so well. I can't tell you how many times either I or someone in a system administrator role had to bring up computer management, connect to the file server, and sever someone's connection manually because they had somehow managed to lock the Microsoft Access database that other users were trying to use. On a busy file server, this is a chore. It takes time to find the right connection before you kill it. Meanwhile, end users are screaming because they don't understand why the database is locked.
Now I realize that SQL Server can get locked up to the same effect on the end user. That's one reason there's now a Dedicated Administrator Connection. But in my experience, that has been a very rare occurrence. I will say the same about Oracle. They are designed from the ground up as multiple concurrent user systems and they tend to work rather well at that. Microsoft Access doesn't always do so.
The Locked Out Database
This is similar to the locked database, but not quite. In this case, one user who has the permissions to do so (Full Control) decides to try and tighen down security on said Access database. Since the database is a file, he or she tries to really tighten it down, and tightens things down too much. Other users suddenly can't get in and they don't have any idea why. System administrators who investigate the problem can initially get in, and then it hits 'em: someone has messed with the permissions. So then it's a matter of trying to figure what the right permissions are supposed to be. And that can take forever, causing certain end users to be unproductive and tying up the time of a system administrator who could have avoided all of this had the solution been in a SQL Server back-end.
The Misplaced Database
If a user has the ability to write to the file, the user has the ability to move the file unless you decide to get pretty granular on your file permissions. Most folks don't. And it wasn't unusual to get a help desk ticket where an end user claimed the Microsoft Access database had been deleted. It wasn't. It was moved to a folder by accident. And unless you go poking around in all the child folders, you don't know this. This sort of thing doesn't happen with SQL Server. It can't. An end user can't drag and drop SQL Server into oblivion.
The Deleted Database
Just as the end user has the ability to misplace the database, the end user has the ability to delete the database. This is always bad news. If you've got a system where deleted files are held, even if the user can't see them, then it's not so bad. But then you've got to engage a system administrator from another task to recover said database. Even in cases where said system allows a user to recover the file for himself/herself, it still seems like most of the time we're still doing the recovery. With SQL Server an end user may be able to mess with the data, but even this can be controlled. They should certainly never be in a situation where they can blow away the whole database.
The Database Backup Not Taken
A Microsoft Access database is a file on a file system. As a result, it gets backed up when the rest of the file system gets backed up. This is usually once a day. So if the user mistakenly does something to lose or corrupt data, then there's an issue. You have to restore from the previous backup. And that may have been the one that ran at 3 AM. And that means unless the user thought to do so and manually made a copy, any work to that point will be lost. With SQL Server, backups can be taken more frequently, automatically, and without interrupting the user's work. In addition, with point-in-time recovery you can get back to right before the mistake was made.
The Insecure Database
Microsoft Access wasn't intended for the granular security that we find in RDBMS products like SQL Server or Oracle. And invariably when that IT auditor hear's data is being stored in an Microsoft Access database, they start asking all sorts of questions. That auditor is only doing his or her job. Basically it comes down to the case where a compensating control is required to add security to the Microsoft Access database. Usually these are additional procedures and we know that these don't work out so well. Sooner or later someone is going to be in a rush or someone is just flat out going to forget and the procedure won't be followed. And then you have a security issue. With SQL Server's granular security, we don't have this issue. We can generate who has rights and exactly what rights and if there's a problem in the IT auditor's eyes, we can adjust the security as necessary.
In Conclusion...
There's more, certainly, but those are enough to explain why avoidance of Microsoft Access as part of a solution for an enterprise. If you are support a mom-and-pop shop or a small business, it may be the right tool for you. But in the enterprise where more robust solutions are available, I say stay away from Microsoft Access. It may be easier on a particular developer, but you'll pay back any savings on the developer's part with the maintenance nightmare you create for the operations staff. And it won't be very long until the numbers flip and it actually costs you more in maintenance than you saved using Microsoft Access in the first place.
