Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

K. Brian Kelley - Databases, Infrastructure, and Security

IT Security, MySQL, Perl, SQL Server, and Windows technologies.

Why I Don't Like Microsoft Access

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.

 

Comments

Posted by Dugi on 15 February 2010

First of all, as I know too many DBAs or Developers started with Access, the reason was that DB and GUI are in one place. Using some VBA codes you have possibility to do some miracles with MS Access. But I agree with you, if you are going to work with large data including security, we would use SQL or Oracle in this case. MS Access has one point where you can continue with GUI as adp project file connecting in SQL (for Access lovers). But finally if you want to migrate from Access in more high database technology like SQL or Oracle, be sure that you need also a good developer for GUI (win or web application) and one DBA for database (SQL or Ora).

Yes, the winner is SQL! - so goodbye MS Access, but now I need programmer for my GUI(forms, reports, functions etc)!  

Posted by Jason Brimhall on 15 February 2010

Even in a Mom and Pop there are better solutions than Access that are not expensive.  Even Excel would be better ;)

If it is a cost thing - I suggest SQL Express in lieu of Access.

Posted by Anonymous on 16 February 2010

Pingback from  Top 10 Reasons Why Access Still Doesn’t Rock | Brent Ozar - Too Much Information

Posted by Karen Lopez on 16 February 2010

Speaking of security: years and years ago, you could view a MS Access db by creating a local Access db, creating a user with the same name as a user on the other others (think "Administrator") and give it your favourite password.  Then all you had to do was open the target db in your Access client app.  It would check your local credentials, then say "okay, is there a user on that target db with the same name?  Yeah? Ok, let him in"

But these days the real issue is still the fact that the Access database is a FILE, not a database. And it's usually a file that end users can do stuff to.  Nuff said.

Posted by Lord Jahganattha on 29 May 2010

Other thing to consider about MS Acces... These files are prone to corruption.

Leave a Comment

Please register or log in to leave a comment.