Microsoft Access may not be right for all situations, but it does have its place -- at least according to database expert Susan Harkins. Here's her take on some of the more common complaints she's encountered from IT pros who like to kick Access around.
Until Vista came along, Access was easily the most maligned and misunderstood offering in the Microsoft Windows family. While Vista has earned its reputation, Access hasn't. It's true that Access can be a problem child, but with proper discipline, Access performs well and has its place in every organization. Still, professionals tend to spit on the floor when someone suggests Access as a possible database solution.
Note: This information is also available as a PDF download.
#1: It grew!
It's common to use Access for small, noncritical tasks even if a larger, more robust system is available. Occasionally, an Access database grows beyond its original purpose. After a year or two of use, an Access database can become vital to the organization. It doesn't happen often, but it does happen. At this point, the IT professional faces a challenge -- revamp the Access database or upgrade, and both require a lot of work.
Some IT professionals grumble... the original developer should've predicted the future and used a more sophisticated platform. That attitude is counter productive. Successful databases evolve over time. It is difficult to predict, with any certainty, the future use of a database that begins in the lower levels of an organization. Most never move up. They continue to serve their original purpose or users cast them aside.
If an Access database works its way up to the department or enterprise level, celebrate its triumph instead of trashing its creator. After all, if the database evolved and grew, your organization is growing, and that's good news for everyone.
#2: I have to support what?
Non-programmers create most Access databases. Administrators, managers, assistants, and clerks use Access to store and analyze data, without any help from IT. While the database might get the job done, it's usually inefficient and difficult to maintain. Invariably, these innovative folks end up calling IT to help resolve a bug or add a new feature. Some IT professionals simply refuse to help, and understandably so. That kind of problem-solving and debugging is difficult and time-consuming.
If the database comes from a company honcho, the IT department might have no choice but to support the database. In an effort to be proactive, some IT departments have banned Access from their organization. Unfortunately, that drastic step doesn't benefit end users. They'll use Excel, or worse, they'll call IT when they need a database. The real solution is to understand how Access fits into your organization.
#3: Access isn't a professional database
Access has the reputation of being a toy and not a real database. It's undeserved, but a lot of IT professionals simply won't use it.
Many Access developers came from a non-programming background. They found a hole and they filled it. Unfortunately, some professional programmers look down their noses at these enterprising folks, and thereby at Access. Despite the snub, Access is the most popular desktop database on the market.
#4: VBA isn't a pure language
Access uses Visual Basic for Applications (VBA) as its development language. Technically, VBA is a subset of Visual Basic (VB), so many IT pros believe Access has less value than VB. It's cockeyed thinking because one has nothing to do with the other. Professionals have many tools and they apply them efficiently, if they're smart. A subset of VB, used appropriately, is no less valuable that VB itself.
#5: It's too easy
The most ridiculous reason for avoiding Access is that it's just too easy to use -- bul...I mean, rubbish! That's exactly why the smart professional uses it. Don't hamstring your organization, support it. If Access can solve a problem quickly, use it, even if the database is just a temporary fix that frees you up to flex your robust system muscles with a real database.
#6: If you build it, something will corrupt it
Admittedly, corruption is one area where Access does drop the ball. Regardless of the source, corruption is a real problem. For some IT professionals, it's ammunition.
But the truth is, used correctly, Access rarely falls victim to corruption. Unfortunately, the majority of Access users don't always use it correctly. The debate goes on, but many IT pros simply won't support Access because of its vulnerability to corruption. To learn how to avoid Access corruption, see 10 ways to prevent Access database corruption.
#7: Money -- 'nuff said
Access applications are cheaper to build and maintain than the more sophisticated productions of SQL Server and Oracle. If the Access developer promises a $5,000 project and the SQL Server developer wants $50,000 just to get started, who's more likely to get the job? That doesn't mean that Access is the best database for the project, but the SQL Server developer must convince the client that a high-end system and ongoing support are necessary. Otherwise, Access just might rule the day.
#8: Access isn't an enterprise solution
Most organizations have different levels of responsibility. That level often determines how IT fulfills a need (or if at all). For instance, most databases have one purpose and often, just one user. The database sits on the user's system and no one else sees it, uses it, or even knows it exists. For these users, Access is a flexible and quick solution. At the top of the organization are the enterprise needs, which are more critical and require more sophisticated and powerful tools. The organization depends on these solutions and they're usually complex and expensive to develop and maintain.
No one demands that an organization use Access at the enterprise level (although I have seen it used, and expertly so, at this level). So why insist that lower levels use an enterprise database system? It's an unreasonable demand. Use the right tool for the job, no more, no less.
#9: File-server applications are inferior
Technically, Access is a file-server application and not a client-server application. That means that Access does all its processing on one server. Client-server applications process on both sides of the network. While that arrangement is more flexible, it also comes with overhead.
File-server applications aren't inferior; but they are different. Used appropriately, they're efficient and capable of outperforming client-server applications.
#10: Access is hard to deploy
Most Access applications need Access, just as an Excel spreadsheet requires Excel. That complicates deployment somewhat. First, Access is a huge application. Second, there are several versions. (I have more versions of Access than pairs of shoes in my closet!) However, if your organization uses Microsoft Office, most of your systems probably have Access installed. On the other hand, upgrades can also be a nuisance, if not outright challenging.
Still, you're going to run into these problems with any application, not just Access. In addition, if you want to maintain Access and avoid deployment issues, consider turning your databases into Web-based applications. For more on this, see 10 reasons to turn your Access applications into Web-based applications.
#11: Poor security
While most Access developers swear by its security model, the truth is, Access security simply isn't as robust as you might need. You can password-protect and even encrypt data, but Access doesn't offer the same level of security as SQL Server. (Unfortunately, the security model isn't even available in Access 2007.)
A compromised database quickly becomes a problem for IT to solve. You didn't create the mess, but you'll get to clean it up. At best, educate users not to use Access for confidential or sensitive data. (Well... you can try.)
Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.