TechRepublic Tutorial: Use Access as an alternative for departmental-level applications

Benefits of using Microsoft Access as a development platform

In most organizations, there’s a constant pressure to do more with less. This is the age of the stripped-down, streamlined, agile company—which in practice usually means trying to do the same amount of work with fewer employees. So it isn't unusual for any given department to have half a dozen processes that are in need of automation. Unfortunately, IT is also being asked to do more with less, and it’s easy for business demands to overwhelm the corporate developers. Departmental applications can get postponed indefinitely so that resources may be devoted to core enterprise processes. How can you as an IT consultant help break this logjam?

One answer is to suggest Microsoft Access as the development platform for smaller, departmental-level applications. In some organizations, corporate policies have forbidden such development, reasoning that centralized development in some programming language, with data stored on the corporate SQL Servers, will make sure IT remains in control. But with pressure and backlogs looming, maybe it’s time for IT to give up some of that control.

The IT perspective
End users want their applications finished now, regardless of the IT department's lack of resources. You don’t want to sow the seeds of a future disaster. How can joint application development with Access help a client here? The simple answer is Access gives them a robust, proven departmental-level database engine that does not lock a client into specific technology if the application needs to grow in the future.

If your clients have been worrying about whether SQL Server, DB2, or Oracle is the best place to store their 10-billion-row data warehouse, they may have simply dismissed Access as an alternative out of hand. And certainly Access isn't an alternative for enterprise-level applications. But departmental applications have different requirements. Often users need to track just a few thousand rows of data spread across half a dozen tables. Access won’t even break a sweat with that much data.

But nothing ever stays static in IT, or so it seems. What if the one-off departmental application suddenly becomes a part of the gargantuan mission-critical application? The answer is simple: Access data is accessible through a variety of standard interfaces. Using OLE DB, ODBC, commercial upsizing tools, or even Access’ own export facilities, you help clients move the data to the enterprise server of their choice if and when the need arises.

To make this strategy work, it’s important that IT be involved in the project from the start. Don’t just throw the database back to the department and say “it’s your problem”—because it might just become your own problem in the future. Take the time to sit down and help architect the application and design the data model. Make it clear that you’ll help out (if resources allow) in case of disaster. Then you can leave the department’s workers to design the user interface to fit their own needs.

The departmental perspective
Your client's job is to get the work done, not build computer programs. Access provides an easy way to get started writing database applications with a user interface that will be familiar to anyone who’s used Microsoft Office. If the client has some repetitive manual process—filling out work orders, collating returned items with RMA numbers, scheduling service calls—it’s likely that a database application can help you get the work done with less effort. But it does take some time to develop such an application, and the IT department may have trouble scheduling that time. That’s when a joint development project becomes attractive.

A solution both sides can agree on
If approached correctly from both sides of the backlog, you may find that both IT and individual departments benefit from using Access. First, training for Access is inexpensive, and most people catch on quickly. Even if there's no one available in IT to actually train personnel, there are a number of third-party training solutions readily available, such as videos, CDs, books, and even classroom training. You client may even have an Access guru who would gladly undertake this venture with a small amount of guidance from you. The best news is that most users can be brought up to speed on Access relatively quickly. Teaching non-IT employees how to use Access to store and manipulate their department-specific data is a reasonable and obtainable goal.

The second positive in this arrangement is the cost. Your client's company probably owns several copies of Access, and if it doesn't, expending the necessary dollars to purchase the software and licenses is just a few drops in the IT budget bucket. Also consider that individual departments may be willing to pick up the costs.

Don't let them get you for nonsupport
The one easy trap you'll want to avoid is support. If IT doesn't build it, don't agree to support it. Remember to emphasize that using Access at the department level is a 50/50 deal—you get projects out of your hair, and the departments get the technical solutions they need. But it comes at a cost, and everyone needs to be clear on the impact of that cost.

You can help by providing easy-to-follow instructions for backing up data and running the compact and repair utilities. You shouldn't just throw nondevelopers to the wolves. After all, their attempt to fill their own technological needs helps you out—if they're successful. Help them be successful in that endeavor.

On the other hand, departmental users should be responsible for their own work—if they're unable to build what they need, even after a little training. They may have to make some hard decisions. Either they can wait for IT, or they can pony up some department money to hire you to develop it.

Living happily ever after
Realistically, none of us is going to live happily ever after when today's budget and personnel cuts are biting us all in the backside. However, you can coexist more peacefully and securely with your clients if both sides are looking for workable solutions. Fortunately, Access may be one of those solutions. Be the IT consultant who helps solves these new company challenges—it certainly can't hurt your reputation.

Editor's Picks