Software

Microsoft Access could be the missing piece in your database solution

Despite IT's general opposition to it, Access can be an excellent alternative to the larger database systems. Read up on some of the facts about Access, and find out why it may be the perfect choice for your database needs.

By Susan Sales Harkins and Martin W. P. Reid

Recently, a colleague asked for help when her company’s IT department decided to stop supporting her department’s LAN and announced that all department-level databases would be moved to the company-wide network. Unfortunately, many of her department-level applications were in Microsoft Access, and IT refused to allow them on the network. The department was forced to reallocate funds toward rewriting existing—and working—Access applications in SQL Server, a measure that resulted in personnel layoffs in the department. Sadly, my friend’s dilemma isn’t the unique situation that it might seem. IT personnel often reject Access because they don’t consider Access a “real” database.

Access fills a niche and can actually be an asset—if you know how and when to use it. Access, when used correctly, provides an excellent alternative to the larger database systems. IT personnel (and even freelancers) who are concerned with providing the best and most efficient solutions should give Access a serious look. Access can help you meet your company’s development needs and budget. Here’s a look at why Access belongs on your list of “real” solutions.

What's wrong with Access?
Access is often ignored as a serious tool for database development for various reasons, including:
  • Access slows down network traffic. This can be a valid IT concern, but the addition of a few Access applications that support a handful of people shouldn’t be a threat to any network’s overall performance.
  • Data is too easily corrupted. A corrupted Access database doesn’t have to be an issue as long as IT isn't expected to fix the database. It’s one thing to allow an Access database to reside on the server, but IT support for that database is another thing entirely. I’ve developed Access database applications running on networks supporting from two to more than 40 users and have yet to have the network crash because of the database. In contrast, I have seen the database crash because of the network, but that’s a story for another day.
  • Jet can’t handle lots of traffic. It’s true: You shouldn’t deploy an Access database to support hundreds of users. You do occasionally hear of the odd Access database that performs well despite heavy use, but it’s the exception to the rule. Access can handle up to 20 users with no apparent drop in performance. Microsoft contends that Access can handle up to 250 concurrent users, but I would advise you not to make that a serious goal for an Access application. An application’s success depends on the developer's knowledge of multiuser programming more than anything else, but even a moderately skilled developer can produce an Access application that performs well with up to 20 users.
  • Access doesn’t support transaction processing. Actually, Access does support transaction processing, but enabling it will require some programming expertise in Visual Basic for Applications (VBA). Even then, if someone pulls the plug on your system, you may lose data; but as long as the Access database is programmed correctly, you should only lose very little. In fact, if processing takes place on the server end, you may lose nothing.

The truth about Access
Access is the most popular desktop relational database system on the market. Even facing large amounts of data, Access performs well and has a number of analysis and reporting tools that are easy to use and yield quick results. It wasn’t designed to be a database server, but it can and does handle client/server environments whether on a LAN, a company-wide network, or the Internet (or an intranet). You won’t be using it to connect hundreds of users to data in any environment, but there are plenty of places you can use it to your advantage.

Access fills the gap between a full-blown server database such as Oracle, DB2, and SQL Server and a workgroup solution. Often, Access is exploited to fill needs best met by one of the larger, more powerful systems, and that is Access’ only failing—if you can call that a failing. Problems occur when Access is pushed beyond its limits. The key to successful development in Access is knowing when to stop. A good Access developer knows its weaknesses and how to avoid them. The truth is, Access is reliable and stable when used correctly.

The biggest factor—the budget
Budgets drive most decisions, and the larger database systems cost a lot—even if you already have the software and sufficient licenses—because every database application requires additional administrative and maintenance support. In any case, Access can be a cost-effective alternative because:
  • It does not require the management and staffing that the larger systems do. That’s not to say that Access doesn’t need managing, because it does. You have to pay particular attention to frequent backups, particularly in a multiuser setup, but you can easily train someone to compact, repair, and then back up the system on a regular basis.
  • You may have to contract with an Access developer to create the application you need, but those costs should be relatively small—anywhere from $2,000 to $5,000. You can barely purchase a SQL Server license for that, and SQL Server costs go up depending on the number of users you need to license.
  • Training costs for Access are lower than those for larger systems. Even advanced Access development courses cost relatively little when compared to similar courses for SQL Server and Oracle. Perhaps more important, you can bring someone up to par on Access much quicker than you can on one of the larger systems.
  • You probably already have Access. If you have Microsoft Office Professional, you have Access. That means your users are (most likely) already familiar with the Office interface and will learn how to use Access or a customized Access application quickly.

Rapid application development
The next time the boss asks you to quickly put together a report on your data, whip out Access. It’s the best tool out there for rapid application development (RAD). That’s because Access is a relational database system and a development tool—it has everything you need to store, manage, manipulate, and display all that data.

Access can also be used as the front end to SQL Server. In fact, it can link to any relational database management system (RDBMS) as long as you have the appropriate ODBC driver. You can replace Visual Basic as your front-end development tool because many VB tools that require code—for example, list boxes—are standard ready-to-go features in Access. Why spend time programming VB when you can accomplish the same thing in Access with a few clicks? Using Access as your front end can substantially reduce your development time and costs. Other built-in features include record locking and one of the best reporting tools on the market.

Odds and ends
It’s likely that many of your users are already using other components of Microsoft Office. Access is very tightly integrated with these other components—especially Excel and Word. For that reason, Access is the perfect choice if your application must interact with other Office components.

Access has good security built right into the product—it’s very similar to Windows Authentication. Of course, the developer must enable security, but once that has been done, you can establish a security model unique to each business.

 

 

About

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.

0 comments

Editor's Picks