Chapter 3

Preparing for Development

We've all heard that "Most people don't plan to fail, they fail to plan." As an Access developer or advanced user, this quote is certainly pertinent to your work. It's likely that you learned to work with Access through trial and error, so you probably develop applications in much the same way. Most developers have not been trained in the value and process of planning, and many dislike it as a result. However, developers who do not plan regularly find themselves embroiled in development projects that never seem to end, as feature enhancement requests and problem reports continue to pour in.

This chapter describes how to engineer an effective application planning process, and includes:

Moving the Window

When contractors were building the current house that my wife, Joan, and I designed, we visited every day to see their progress and to check the work against the plans we had drawn. On one visit, we noted that the sun was setting right where we had located the fireplace, and Joan commented "We should have put the window there instead." So, the next day, I met the framing contractor at the site and talked through our options for swapping the fireplace and the window next to it. Because the house was only roughly framed, the cost and time impact were minimal; so we made the change and ended up with a better design.

Sometimes when I'm sitting in my family room looking out the relocated window, I try to imagine living in the house the way we had first framed it, and then suddenly deciding to move the fireplace and the original window. Two complete walls would come down, wires and pipes would move, and the deck would be modified. The cost would be astronomical (at least 100 times as much as the original revamp) and the chaos would be exasperating.

There are many parallels between this tale and the software development process. First, our vigilance saved us money and disappointment-we would have certainly enjoyed our house less without the relocated window. Similarly, users of software applications that discover design shortfalls equally early in a process can initiate a change, but would never be able to afford that same change if they caught it later in the development phase.

Secondly, the cost differential of changing a feature of a house early, versus after completion is about the same as that of changing a software program feature. It can cost as much as 100 times more to change a software feature at the end of development as in the design phase. So, the correct time to make sure you are delivering to your users what they want is in the framing stage of the application, not after they've moved in.

Given that your habit is probably to take a few report and form sketches produced by your users and begin coding, how can I convince you to create plans and designs for your database applications before you start coding? I'll employ three techniques in the next two chapters that will help make my argument more persuasive:

Even if you already create detailed project plans, you should still find enough interesting tips, techniques, and tools in this chapter and the next one to help you rethink or refine your process.

The information in this chapter is relevant to all parties in a development process, from management and development to users. However, the chapter generally speaks from the perspective of the development manager or primary developer.

Planning Your Project

A database application project quickly takes on a life of its own and can grow in scope like a weed on steroids: quickly and in all directions. Careful planning can provide a control and budgeting mechanism for such growth.

Planning a business application is more like detective work than science. The design team begins with an incomplete set of facts about how a process works and how it could be improved, then embarks on a discovery phase to fill in the missing information. As Figure 3.1 implies, the process is complicated by the fact that users don't always invest the time and mental energy necessary in application design to provide the following essential information:

Instead, users normally provide brief descriptions of their current workflow and a list of their objectives for an automated solution. The users' lack of technical skills often means that their descriptions are inaccurate or incomplete; thus, the design detectives must fill in the gaps between what users say and what they really mean. The design team then must propose extensions and alternatives to the users' wish lists, based upon its members' greater knowledge of business processes and technology.

Fig. 3.1

During the application design process, this information must be discovered and detailed.

When projects are spearheaded by developers, as is often the case in flat organizations with few management layers, caution must be exercised that the users and managers do not get the impression that they need not be involved in the project beyond creating an initial wish list. In fact, the involvement of users and managers throughout the project is critical to its success. I try to get my clients to realize the following two points about Access projects before we get started:

If you can communicate the two points above to those who benefit from the development project, you can start the design process off with the correct team mindset.

A Pisa the Puzzle

Try as we might to indoctrinate our clients with the mindset described in this section, they nevertheless regularly surprise us with significant enhancement requests late in a project development cycle. Sid, one of my staff members, tries to handle such change requests firmly with the following analogy:

"Assume that your software project is a ten story building, and I'm your builder. At this point in the project, I've completed the majority of your building-frame, wiring, plumbing, windows, and walls. What you're asking me to do now is to stop and add five more stories to your building. That means you want me to rip out all of the wiring, plumbing, and framing on the tenth floor and connect the five new stories to it.

"Not only were the wiring, plumbing, and structure of the building designed for ten stories, not fifteen, making it very hard for me to safely add the additional footage, but the foundation of the building was poured with ten stories in mind. If I add five more stories without rebuilding the foundation, your building could begin to sink and tip. You don't really want me to build you another Leaning Tower of Pisa, do you?"

While slightly whimsical, Sid's analogy is appropriate and paints the correct picture-adding last-minute features to an application without backing up to the beginning and fitting the features in correctly may undermine the entire integrity of the solution. It is the responsibility of developers/designers to elicit from the users enough information to determine accurately what the feature set of the application should be, and it is incumbent on the users to be clear and comprehensive about their long-term needs early in a project's design process.

Some business processes cry out for automation and are easy to computerize. Others are not so linear and can challenge developers and their tools. Of course, the more complex the project, the more intensive is the planning effort.

Describing the Concept

One of the three following circumstances usually initiates discussion of an application development project:

Whether initiated by one of these forces or others, early discussions of an application usually involve workers and managers within a single department. A workflow issue becomes a frequent point of discussion at departmental meetings, or even the water cooler, until someone determines that the problem can be overcome with a customized software solution.

Next, the tone of the discussions usually shifts quickly from problem-oriented to solution-oriented, with users contributing ideas and feedback about improving the current situation or process. Often at this point, hard copy documentation of the problem or solution crops up, such as reports with manual notations showing additional desired data.

The original discovery phase should produce the following twofold result:

  1. A detailed project wish list that captures all relevant user inputs
  2. A simple project proposal paragraph stating the problem and the contemplated solution

With the beginning of a project proposal at hand, discussions can begin in earnest about the feasibility of the project.

Determining the Scope

Before discussing a project's specifics, you should help its users and managers to understand the phases that an application goes during its lifetime. People who have not been through the complete application life cycle may be surprised to learn how many total stages there are to an automated solution. Each of the phases is described in the section "Understanding an Application's Life Cycle" later in this chapter. Try to get all parties in the project to think past the first release and to consider the total life of the project when doing the design work.

My company is still supporting two DOS-based database applications we wrote in 1986. Do not underestimate the useful life of a good application.

I like to ask my clients in the first design meeting what their five-year plan for the application is. The first reaction is normally something like this: "We have to upgrade Access every eighteen months and our hardware at the same time, so we will rethink the application in eighteen months as well. For now, let's just get something out there that works." Clearly, the amount of time and energy they expect to devote to the project is limited by their short expectations of its useful life.

After further prodding, however, I can usually get such clients to admit more about their long-term needs, often something like the following:

With information at hand about the users' long-range plans, the nature of the project often changes dramatically. In the example statements above, each one introduces a slightly different wrinkle into your approach to the project:

If your users can come to see the project as a five-year project instead of one with a shorter horizon, their interest and willingness to invest time in the project increases, and your chances for a successful long-term result improve. You can also effect long-term cost savings in a project by building it with room to grow from the start, making future modifications easier. See the section "Planning for Growth" later in this chapter for more information.

Selecting the Players

Career Moves

"When we visit with our customers, it always amazes me to hear about the background of the people developing applications with our product. For many companies, the first people that were able to requisition and purchase personal computers became the most computer-literate and eventually inherited enough software support or development responsibilities that they were edged out of their original job and into a de-facto Information Technology role. Thus, if you dig into the background of many corporate Access gurus, you'll find that it wasn't long ago that they were the company travel agent, bookkeeper, or shipping clerk!"

Tod Nielsen, General Manager, Microsoft Access Business Unit

People power is the fuel of application design and development. Depending on the scope of a project, there are many key players on the team. No project is solely dependent on the skills of the developer; the design effort is affected by the capabilities of each member of the team, and how well they interact. A solution project team may include a mixture of people with the following responsibilities:

Figure 3.2 shows the relationships of project team members to each other. Within the user pyramid, power flows from both ends to the middle; the user representatives are empowered by the ultimate users and the users to ensure that their needs are met by the application. In the development pyramid, power flows downward through the various management levels to development.

Fig. 3.2

The application development process involves many team players.

A large development project may have different people filling each role described here. In a smaller project, the team is usually smaller as well. The program manager and project manager positions often fall on the ultimate user who initiates the project. The development manager and the developers might be the same (one) person, and all the users may be part of the user representatives group. If no IT liaison is present and the developer provides support, the smallest reasonable project group consists of manager, developer, and users.

In the past, application development teams usually had a position called Systems Analyst, a person that was technically sophisticated but was also qualified to research and design new systems. I believe that every good PC application developer should strive to have the skills of a Systems Analyst.

In the design team that I define here, the development manager performs the systems analysis role, or all of the developers share the role if they are qualified to do so.

When assembling a team of developers, bear in mind that there are dangers in using both too few and too many developers for an application. On the skinny side, one developer doing a 500 or 1,000 hour project is likely to become a little bored or overwhelmed toward the end. A project of this size may be complex enough to benefit from a mix of skills and experience rather than the talents of a single person.

At the opposite end of the spectrum, the coordination and communication layers that accrue when multiple developers write an application can slow down a small project. The addition of source code management in Access 97 provides more control in a multi-developer environment than was previously available, but smaller teams still tend to be more successful with Access development than larger ones. (I discuss multi-developer issues in Chapter 20, "Applying Development Shortcuts.")

For my development projects, I try to target a ratio of no more than one developer for every 200 hours of project scope and no less than one developer for every 500 hours.

Answering Key Questions

The project design process usually starts with a feasibility meeting where users and managers discuss the current process and how it can be improved. Developers can often add value to such a meeting, and you should try to attend them. The discussion at this point should be focused on the following five overview questions:

If a project concept survives this feasibility meeting, a project manager is formally appointed by the program manager to coordinate users and developers and to see the application through development to its delivery. An initial design meeting is scheduled to bring user representatives together with the development manager and selected developers to discuss the application's features and usage. It is important in the initial meeting to establish the scope of the project and to build the good team chemistry necessary for an enjoyable project.

An Initial Meeting

One of my development leads, Sid, and I recently attended an initial design meeting with a project manager and user representatives to begin designing a complex client application. As development manager for the project, I was leading the meeting; so I asked Sid to record his observations about the flow of the gathering in the hopes that it would provide a case study for this chapter. The following are his comments and my annotations:

Depending on the number of attendees and the project complexity, an average application development project can consume twenty to a hundred or more hours of design meetings. I employ the following two techniques to try to limit the total time spent in meetings:

Providing user committee members with homework assignments between meetings gives them a sense of involvement in the design and development process, which fosters team spirit and commitment to the project. It also reduces the probability of boredom with the process, which can lead to design errors or omissions.

Figure 3.3 shows the cycle of design meetings and how it is mixed with hands-on research into existing processes.

Fig. 3.3

Most application design processes follow a flow similar to this.

Diagrams are the lifeblood of application design meetings; thus a meeting room without a white-board is mostly useless to a design team.

The first homework item for the project team comes between the feasibility meeting and the first design meeting. After the project is approved for a design phase, I send a survey document we have developed to all attendees expected at the first design meeting. The recipients are expected to complete the questions pertinent to them before the meeting.

The main sections on the survey are described below to help you understand project pre-planning. The complete survey document itself has more than forty questions. It is on the CD-ROM as PRE-PLAN.DOC for your use and is included at the end of this book as Appendix A, "Database Software Project Pre-Planning Survey."

Don't I Get to Vote?

Over the years, I've noticed a trend regarding the delegation of computer responsibilities in midsize companies. When we do multi-user database projects in such firms, we often coordinate deployment issues with the person we call the net admin (network administrator). All too often, I ask the net admin how he or she inherited responsibility for the computer infrastructure only to hear this answer: "I wasn't here the day they voted on the position, so I guess everyone voted for me." In some firms, managing computer hardware or development vendors is not seen as an enviable position because neither have a reputation for reliability; so someone who does not want the job often gets stuck with it due to company politics.

This statistic serves to point up the fact that, in the early phase of a development project, people by nature often try to shift responsibilities from themselves onto others. Thus, the moral is: Be alert and in attendance at company meetings, or you may find yourself elected in absentia to a position you don't want!

Some of these survey items provide the framework information that helps to shape the design process. For example, having a constrained delivery date at the outset often limits the final feature set. Other survey items map to items on the final specification and provide discussion points for the design meetings.

Understanding an Application's Life Cycle

Most database applications stay in use for several years, so initial design and development is only part of the total scope. Enhancements, a widening of the user base, performance tuning, archiving aged data, and other upkeep consume ongoing resources. In the early stages of a project, it is important to communicate these facts to the project team.

My experience with database projects indicates that up to two thirds of an application's total cost and labor still lie ahead after the initial version is delivered. Companies rarely budget time or money properly for these ongoing efforts.

You should also make sure the system's designers and users understand the complex dynamics involved in developing, deploying, and maintaining a database application. Figure 3.4 diagrams a system's life cycle.

Fig. 3.4

The life cycle of a non-trivial database application may be several years.

At each stage in an application's life cycle, there are different project characteristics and development requirements.


The process of designing an application has two primary components, as indicated by the two chapters on the subject in this book (this chapter and Chapter 4, "Creating Design Specifications"). They are as follows:

While all of the following phases of a project are important, I devote this entire chapter to the subject of design because it is the most critical to both the short term and long term success of the project.

The design phase rarely takes less than a month and can run into several months, even for a small project. The time frame is not always a reflection of the complexity of the project; often it is a reflection of the availability of the design team for meetings or the difficulty of the research effort.

Designing Slowly

While I noted previously that an application design process may take several months, my company actually has two client projects that have each been in the design phase for over a year. One of the projects is moving slowly due to the unavailability of design team members and the complex nature of the project (financial services). The other is in a state of constant redesign, as its industry (health care) is changing rapidly.

The most dangerous part of very long design phases is that project team members can get bored after several months, and the quality of the discussions and design effort may decrease.

At the end of the design phase, a preliminary design document exists as described in Chapter 4, "Creating Design Specifications." Costs are then applied to the development time estimates to arrive at a total project cost. This cost is measured against budgets to see if features must be deferred or removed.


With a design in hand, too many developers begin immediately coding in earnest. In my life cycle model, however, the application development phase is a step removed from the design phase. I believe that, before you code, it is prudent to do a sanity check to determine if what the designers say they want is, in fact, what they need.

Software development studies I have read indicate, as does my own experience, that at least half of the problems and deficiencies in a software project are attributable to poor design as opposed to poor coding. A prototype can help you catch design deficiencies early.

One of the biggest mistakes a project team can make is to believe that, at the end of the design phase, their design is 100 percent accurate and coding can begin. In such an environment, a missing feature invariably comes to light well into the development cycle, with negative consequences. To steer clear of such adversity, I try to introduce two reviews at the beginning of the development phase, as shown in Figure 3.5.

Fig. 3.5

An application's design can be validated by having users review prototypes.

You'll notice in Figure 3.5 that the design is reviewed twice. The first review is the prototype phase, where designers review form mockups in an attempt to discover missing or erroneous tables and fields. The second review is what I call the working model, which I'll talk about in a moment and whose purpose is to verify the design of the forms.

Two forces are at play in the prototyping phase. First, giving designers additional reviews of the design increases the odds that it will be correct when development starts. Secondly, if designers are allowed to change their minds now, it's hard to stop them from doing so throughout the project. The dual reviews that we build into our projects are an attempt to balance these two polarized forces; designers get two extra trips to the wishing well, then they are cut off.

Because Access provides you with great leverage through its online assistants-its wizards-you can usually prototype even a complicated project fairly quickly. The two most important components in a prototype are the following:

Prototypes do not need to have navigation, validation, or any other code components-usually just a copy of each data bound form. Your goal for each of the forms is to make it comprehensive, but stop short of completion. Comprehensive means that all the controls are included, the layout or location of the controls is accurate (though not necessarily precise), and all databound forms in the user interface are reflected. Interface design guidelines for the project should be reflected in the forms, but only enough for the purposes of the prototype. The forms have no code written for them yet.

You can give users a tour of the prototype in a design meeting, or they can be given a reviewer's guide that tells them how to open each form from the Database window, what to look for during their review, and what kind of feedback is expected. Reviewers need to be alert for missing features, but a few minor change requests made at this point in the project can usually also be accommodated with a minimal impact on the budget and timeline.

The real purpose of prototyping has nothing to do with forms, which are reviewed again later in a second review. The primary purpose at this point is to confirm the schema. You create form prototypes here to give the non-technical person windows to view the data structure through. In the prototype, the user is looking at the schema by reviewing the controls placed on the forms.

The entire design team should review the prototype. Although the team normally includes a representative sample of the users of the application, it is ideal to gather feedback on the prototype from as many users as possible, sometimes even beyond the design team.

After feedback from the prototype is collected, create a list showing all of the relevant inputs received. The design team should meet to debate which inputs from the list become part of the project plan and which are discarded or deferred. Any schema changes that are approved are trickled into the schema and the prototype forms.

It is important to stress to reviewers that this is their final opportunity for input with respect to objects missing from the core database structure. After reviewing your sample screens, they may not have another opportunity to change tables and fields until the next major release of the application, which may be a year or more in the future.

After the prototype review, take the prototype forms and add enough event code to enable users to navigate through the application. Your objective is to create a working model that is an expanded prototype for the purpose of reviewing the forms and the application flow they enforce. The following are the components that I include in a working model:

The working model build has none of the following features:

The need for the working model arises from the fact that at least 50 percent of an average application's development budget and time is spent on forms. Because forms are so expensive, the team is highly motivated to try to prevent building and coding each form or form feature more than once. The energy spent creating and reviewing the working model is almost always less than the energy consumed by skipping this step and getting your first user feedback regarding forms at the alpha release stage, much later in the development cycle.

Users should be looking for the following items in their review of a working model:

Capturing User Feedback

When we distribute a working model to reviewers, we sometimes employ audio tape to capture their feedback. We have reviewers turn on a pocket (hand-held) dictation machine and make verbal comments as they move through the model. This method is highly accurate when capturing user opinion, and also very time-efficient. The technique captures more information about the application than a reviewer commonly writes down or keys in when reviewing a form. Also, the reviewer can move freely though the application, unencumbered by pauses to manually note issues and questions, which speeds up the review process. The tapes are then reviewed by the development or design team.

Microsoft employs a similar technique as they design products. Early models of a product's interface are given to the corporate usability lab, which brings in users from the Seattle area and videotapes their attempts to use and navigate through the product model. These video tapes are later reviewed by product managers and developers, who can witness prospective users' attempts to comprehend the product's interface.

When the prototype and working model have been reviewed, the development team should have sufficient feedback to consider the design phase complete. Depending on the feedback received, application objects or processes may need to be redesigned, and the modified design may need to be reviewed again.

After incorporating the final feedback into the specification document, the design should be considered closed for purposes of the first release. The next chapter details what the design document itself should look like at this point. The project manager is responsible for minimizing user enhancement requests after this moment.

Based on the redefined feature set, the project manager and development manager revise and finalize the project timeline and budget, complete with development milestones and schedules for testing and deployment.

Application Development

The development phase of a project is totally dependent on the design phases that came before it. Without a comprehensive design spec, developers have to guess about how the designers intended a feature to work. Also, if the design is vague, the timelines established for development will be in error, and the developers may find themselves out of time and money before the work is done.

The primary challenges that arise during the development process are:

Chapter 9, "Completing and Deploying a Solution," further describes how to execute and manage the development process, and Part II of this book, "Expert Solutions Techniques," includes detailed tips and techniques for use during the development stage.


At the completion of initial development, the testing cycle begins. The simplest testing process involves the following two stages:

The testing process, and the importance of user involvement in it, is discussed in greater detail in Chapter 9, "Completing and Deploying a Solution."

Initial Deployment

This phase of the project is the most exciting for both developers and users. After months of work by all parties, the application is launched. Whether it reaches orbit or crashes is determined by the following factors:

At the end of successful deployment of the application's first release, the project is usually considered complete with respect to the initial design, timeline, and budget. The project team may or may not be relieved of their responsibilities, and the ongoing support may shift from the developers to a support staff. However, this does not mean the application has matured, only that it has reached its first plateau. More growth and change lie ahead.

The life cycle phases I have detailed up to this point have been at a microcosmic (step-by-step) level. From here forward, the phases I list are macrocosms, which are meant to include by inference each of the steps above. In other words, the steps from design through deployment that I have just described were for release Version 1.0. For each future release version, as detailed in the following life cycle stages, each of the steps taken to create Version 1.0 may be repeated again.

Product Refinement

The first year or so of life for an application usually is not quiet, but neither is it dramatic. As users become more productive with the application, they discover problems with the software or initiate ideas for enhancing it. Also, much can happen in a company in one year, often necessitating some changes in the application to keep it in step with business process changes.

At some point, the To Do list for the application becomes significant enough to warrant another release, usually Version 1.1. The process for creating this release varies depending on several factors, including the following:

For a complicated application, it can easily take five or even ten times as long for a new developer to modify a feature as it would take the original developer. Use of standardized and documented development conventions can smooth the transition of a project from the original developers to maintenance developers. Good development standards are exemplified by the naming conventions described in Chapter 5, "Creating Naming Conventions," and Chapter 6, "Leszynski Naming Conventions for Access," and by the coding style defined in Chapter 11, "Expert Approaches to VBA."

Because the refinement phase is the time to work out the kinks, as well as to keep the application current with the users' needs, more than one application version may be released during this phase. This phase usually goes on for a year or more after initial deployment. Since each release version is a redeployment, it requires the same design effort, prototyping, testing, and other tasks as were required by the initial release; the only difference is one of scope. Each release also requires a project team, although it may include fewer people than the original release.

Expanded Deployment

A successful application often gains momentum as its feature set is improved and balanced to match the users' needs. As the users become more comfortable with it, they become reliant on it as well and the importance of the application within the workgroup increases. From this point, through the first or second year of life, it is not uncommon for one of the following three things to happen:

Whatever the reason, the application is suddenly facing a dramatic bump in the number of total users and possibly in concurrent users as well. If the original design team expected this, the project plan may include some plans for handling the growth. The data schema and application may even have been created with hooks to enable additional users and features to be added easily, or with extra fields required by the expanded user base.

In rare cases, a well-built application can be deployed to additional users with no coding changes. However, to accommodate growth, the application is usually modified in this phase. As with product refinement, a new release requires design, prototyping, development, and testing processes. However, this expanded deployment phase adds an increased burden on resources beyond that required during refinement.

The refinement builds of the product likely implemented features requested by existing users, so a minimum additional training and support burden was generated. In this expansion phase however, a body of completely new users is added- users who require hand-holding during deployment to their machines and training on the application from scratch. Further, if the user base increases, any ongoing support costs and workloads suddenly increase as well.


With the application deployed to a greater number of users, it is likely that Access is now being taxed at or near the limits of its comfort zone. Any further expansion of the user base requires the discussion about moving the application from the Access shared MDB file metaphor to a client/server metaphor. If the initial deployment was quite wide, Access was strained from the start; so this phase sometimes comes in place of the expanded deployment phase instead of after it.

The client/server model provides the application with the following two tangible improvements:

When the application goes to client/server, does this take Access out of the loop? The answer varies and is treated more fully in Chapter 19, "Exploring Client/Server Issues." Regardless, the phase always requires additional programming and the full gamut of release version steps from design through deployment, as seen in previous builds.

Many applications never achieve the user base to support this phase, and so their users continue to work quite contentedly in Access/Jet throughout the life cycle of the project.

Product Maturity

Somewhere during the second or third year of an application, the application is probably considered mature. Multiple minor revamps have been done and usually at least one major expansion, and the application has been useful enough to the company to have paid back its cost.

Throughout the maturity phase, the primary requirements of the application are as follows:

A dangerous trap lurks at this phase because the application has become stable, which to some users and developers can translate into antiquated. In other words, some people believe that when an application is no longer being changed, it is non-dynamic and outmoded. Such people look for ways to rework or replace the system, to keep life interesting or to provide themselves with make work projects and job security. Often, reality is quite the opposite of their perception; an application that needs no major changes does its job perfectly, and thus is producing a superior return on investment for its company and a stable environment for its users.

How long can people use the same Access application? Nobody can be sure at this point because Access has only been available for three years. However, I am comfortable projecting that many applications we have created in the past three years will be in use five years from now.

Obsolescence and Death

I noted previously that application planners should work with at least a five year horizon in mind. Nevertheless, most applications eventually reach a point where they should be scrapped.

The reason for scrapping an application is not usually that the application does not perform its function well, but rather that either the function has changed or new tools have come to light. The pace of business competition and technological change almost guarantees that one of these situations will occur.

For example, a sales prospecting package written in Access may provide several years of outstanding performance and value to the company. However, at some point in the future, one of the following three things will happen:

Regardless of the disposition of the application, data is virtually never thrown away. The Access data needs to be ported, at obsolescence, to a new platform or kept online for analysis because it has historical value. Chapter 7, "Understanding Application Architecture," discusses managing warehouses of such historical data.

Researching Automated Solutions

Having completed our detour through an application's life cycle, let's return to the design process. At this point, the design team has completed its survey and debated the results in one or more initial design meetings. At each meeting, issues are discussed that cannot be resolved without field research, making it prudent for developers to perform hands-on investigation between the meetings.

Doing on-site research involves meeting with the users, dissecting existing processes, and keeping good notes and audit trails that feed the design effort. Doing this research well involves a balance of the following skills:

In this section I detail my three approaches for digging out the business facts necessary to create a solid application design. The approaches are not mutually exclusive; each is valid alone or in conjunction with the others. Figure 3.6 shows the relationship between the flow of information in an organization and the three research approaches I use.

Fig. 3.6

I use some combination of these three approaches when researching the database needs of an organization.

Workflow Analysis

Workflow analysis is the process of watching people do their jobs or even doing the work with them. This process is basically like walking a mile in someone's shoes in order to gain insight about how to improve their workday. You are looking to discover the human side of a business process.

To do workflow analysis, spend time with potential users of the new system and note the processes they use throughout the workday that are relevant to the proposed application. Observe the flow of information across each person's desk or workbench, paying close attention to the following areas:

In visiting with the potential users of the new system, make note of habits or repetitive behavior as they do their work. Many people who have mastered their jobs are not even aware of some of the things they actually do each day; some actions become motor memory and are done without thinking. You need to be observant to catch work habits as they are manifested, and ask enough questions to understand how the habit might become automated through software.

Ask workers what parts of their current workflow they think are the most efficient. If these processes are manual, try to discover how they might be computerized.

Delve into inefficiencies in existing systems as well. Ask people for their top gripes about their workflow and suggestions for improvement. If any processes are automated, review the existing applications and make notes about the good and bad points.

When doing on-site research, I try to have lunch with the workers in their normal environment at least once. Meals and other work breaks provide a relaxed, social forum where people can be encouraged to talk about the work environment without distraction. Also, the interplay between multiple workers often produces better quality ideas.

The Data Chase

The essence of the data chase is to identify which data items are of primary importance in the new system and to follow those data items through their life cycle in the company. An individual data item (a datum) can take many paths through a company, change along the way, and be utilized by more than one process. Whereas a workflow analysis exposes you to only the processes that are to be automated, the data chase may involve following data items through workflows and processes outside the scope of the current application in order to understand the data better.

To start this procedure, identify which data items are important enough to warrant a data chase. The best way to make this identification is to ask the ultimate users of the new system to highlight items in the application's output that they will be using to guide their critical decision making. The accuracy of information that is used for management decisions should be a top priority of the new application. Performing a data chase on such items helps insure that they are handled correctly as they travel through the organization.

After identifying critical data items, work backwards from the reports or other output of the current system that produces these outputs (see the sidebar). Trace the data items through each process that uses them all the way back to their original source. After mapping how the data items flow within the organization, contrast how they will flow when the application you are designing is implemented. Also note which fail safes are required to insure their accuracy.

Tracing a Data Item

Here is an example of a data chase from my recent experience. The ultimate user of one of our newly designed systems-an accounting controller to be exact-required that the new application compute the average weighted cost per pound paid for goods entering the manufacturing process. The data chase on cost and weight information coming from the factory floor to accounting led me backwards through the following objects and locations:

Several things can happen to a datum during its life in the company:

As you run the data chase and observe data items going through one of these states listed, remember to ask the users whether what you observe is actually the desired result. This is especially true of transmuted data, which is sometimes transformed wrongly, but the error is not noticed until a data chase brings it to light.

Reports Analysis

The reports analysis provides a third research approach, and is often the only method many developers employ. Database projects are frequently initiated by managers who complain about existing reports or see room for improvement in them. These managers drive design meetings that are focused on improving the reports, and they keep the meetings oriented toward the outputs from the new system rather than other features.

Even in this supposedly paperless decade, most business decisions are still made from a hard copy, and one of the primary objectives of most database systems is still to produce reports. Thus, there is nothing inherently wrong with focusing on reports. Good reports analysis work includes analyzing not only the current and desired reports, but also existing ledgers, spreadsheets, and other printed materials that might be relevant to the targeted reports from the new system.

Of the three types of research, reports analysis is the least comprehensive and should usually be done in conjunction with a workflow analysis, a data chase, or both.

As you perform reports analysis, keep the following items in mind:

At the conclusion of reports analysis, you should have a stack of existing reports that need to be re-automated, and every item on each report should be annotated with your findings about the item's source and flow within the company. You should also have produced report mockups of the new reports to come from the new application. These mockups should show the source and flow of any data items that are new and not reflected on the existing reports.

I find that Excel is a very convenient tool for creating report mockups. Excel's grid layout makes creating columnar representations easy; you can create report mockups without building a data structure, enter sample data items, use formulas to show subtotals and sums, and you can make changes and reprint mockups quickly when creating printed review copies for meetings.

Sometimes, the reports coming from a system are its only reason for existence. In such an event, most of the design effort is invested in the reports, and the layout of screens and database structures is left to the developer. The reports become your primary design document in this case; so make sure you note on the report designs the standard schema information for each item: data type, maximum width, input mask, required validation, and so on. Also, make sure to have the design team discuss the sort order, selection criteria, subtotals, and totals required.

Planning for Growth

Your application may deploy smoothly and work as planned and yet find itself struggling in a year or so under a large data load or user volume. If the design team did not commit to long-range planning for the application or was simply wrong in its growth projections, your application may outlive its usefulness sooner than planned.

In one of the final group design meetings, make sure to cover the topic of the application's expected growth over time. In my experience, design teams usually underestimate the acceptance of an application and thus miscalculate the application's requirements for growth and flexibility. For example, assume that a prospect management system developed for marketing was projected to add 1,000 new prospects per month or around 24,000 records over the first two years. As the application becomes better known throughout the company, the expanded deployment phase that we discussed above kicks in. Suddenly salespeople begin using the system to track existing customers, and the Human Resources department jumps on board and tracks job candidates using the software. The difference between expected and actual load, both for data items and users, greatly exceeds expectations, as shown in Figure 3.7.

Fig. 3.7

An application's actual growth in record count and user count often exceeds the expected growth.

Without proper growth planning, the marketing application has three potential shortcomings:

It is very likely that the design team could have seen this situation coming and planned for it with better research. Discussions in design meetings about the hypothetical expansion of the system might have highlighted its potential for growth.

As you design an application, the following are some areas to include in your design discussions to enable the application to cope with growth:

Whose Project Is This, Anyway?

Twice in the past few months, I have been involved in design work for clients and found that the original project scope was really only the tip of the iceberg. By asking the right questions and doing on-site research, we were able to save the clients much extraneous effort.

Client A asked us to build a database to track people qualified as public relations contacts for their products. The original project team was created in one isolated product group, so we asked them if any other product groups in the company already had similar databases. The response was, "No, not to our knowledge." In doing the legwork of workflow analysis, however, we checked around the company and discovered several contact databases dispersed across other departments, and two more in the marketing department alone! What started as a small, isolated database became a company-wide project to coordinate the needs of many different groups of users, all wanting and trying to track PR contacts.

At Client B, an initial design meeting was called for sales department personnel to discuss a proposed application for managing resellers. In the meeting, I asked if they knew of any other departments in the company that would benefit from the database. One of the attendees spoke up: "I do some work in marketing, too, and they need a database just like this." All jaws dropped as it became apparent that nobody had talked to other departments in the company to see if efforts, costs, and code could be shared.

We create most client databases as if they will hold international data at some point in the future, even if the client tells us that they don't think the feature is required. Only a few changes are needed, including a wider state field to hold province names, the addition of a country field, rules that enable both alphabetic and numeric characters in ZIP Code (postal code) fields, and wider telephone number fields. If the application eventually needs to hold information from Canada or Mexico or even farther away, such a database requires no expensive modifications, but instead only minor user interface changes.

Note that the concept of an English-based database containing international data in its tables is not the same as distributing an application internationally into a non-English user base.

If an application has data that has an identifiable expiration date, check for that date during each login and display a reminder to the user that it is time to archive the data. For example, much data is reported on an annual (calendar) basis, and at the end of the year, annual reports are run (in January). In such an application, a reminder set for February 1, telling the user to archive the previous year's data, is appreciated.

If the data will be upsized to SQL Server or another platform in the future, several additional considerations enter the mix. Record indexing strategies, record fetching and locking in forms, and other areas of the application are approached differently when the back end will change from Jet to an ODBC data source. See Chapter 19, "Exploring Client/Server Issues," for more information.

Selecting the Tools

It is said that a crafts person is only as good as his or her tools. Certainly, this adage holds true in the application development trade. Using tools that are unstable or difficult to work with can radically delay a project's timetable, and nothing is more frustrating for a developer than trying to get a product to do a task that it's not suited to do.

While Access is obviously a capable platform for data-centric applications, the following are two questions each design team must ask as they select tools for their project:

Concluding that Access should be a project's platform tool must not be a capricious decision. The project team should make the determination by considering both questions, as I detail next.

Evaluating Access Against Other Tools

In asking if Access is the right tool for the job, the job must first be defined. In other words, too many project teams decide on Access at the feasibility meeting rather than completing the design and then talking about the toolset. While I'm a big fan of Access, neither it nor any other product is the perfect tool for every single job. Trying to make Access do a job it is not suited for ultimately produces negative sentiments among all parties: designers, developers, and users.

Comparing Access and Visual Basic

One of the most common questions asked about Access is "How do I choose whether to use Access or Visual Basic for my project?" Because both products have powerful, and similar, capabilities, the answer is not easy to come by. When determining which of these tools is appropriate for your project, consider each of the similarities and differences between the products.

In the latest release of each of these products, there are more features in common than different features with respect to application development. Some of the most important similarities between Access and Visual Basic are the following:

While the two products have much in common, Visual Basic has several key features that differentiate it from Access:

Access also has several unique features not found in Visual Basic:

Table 3.1 provides a decision matrix showing which product is stronger when you are considering specific application development benefits.

Table 3.1 Comparing Access and Visual Basic



Visual Basic

Fast execution

Very dependent on hardware for its performance

Compiled executable gives faster performance, especially on form loads

Rapid development

Wizards and powerful interface assist rapid development

Richer feature set and steeper learning curve add more development time

Application disk space

The smallest databound application, setup, and supporting files takes 18 megabytes

The smallest databound application, setup, and supporting files takes 4 megabytes

User interface

Centered around databound forms with Access-managed events

Complex, non-databound forms with compound ActiveX and subforms controls and control arrays

Learning curve

Access has fewer total

development features and is thus faster to learn

If you are new to object- centric development, VB can take longer to master than Access

Database orientation

Easy to build database objects and work with data

Slower for building database objects and viewing data

Development and maintenance costs

Access applications are cheaper to build and easier to change

A VB application costs more to build and must be recompiled after a change

In my experience, a Visual Basic application takes roughly twice as much money and time to produce as a similar Access application due to the factors shown in Table 3.1.

In reality, almost half of all our client projects involve both Access and Visual Basic together in some capacity, so the decision to use one or the other in a project is not mutually exclusive. Many projects that start with one of these tools end up using both within the first year after deployment. Figure 3.8 shows the relationship between these products in many of our installations.

Fig. 3.8

Access, Visual Basic, Jet, and SQL Server are frequently mixed together in an integrated application.

I can summarize our approach to the decision between these two products in the following sentences:

Comparing Access and Visual FoxPro

There are about five million people that use a variation of the Xbase language, a catchall term that includes dBASE, CA-Clipper, and FoxPro. Microsoft's acquisition of FoxPro was designed to provide access to that large base of developers as much as it was to obtain a tool that fit a particular product strategy. As such, Visual FoxPro (VFP), while an excellent product, does not integrate as smoothly with Office as Access does.

Despite their very different origins, VFP has evolved from humble DOS roots and become a capable Windows development platform. Access and VFP now have several primary features in common:

Regardless of these similarities, at the core these products are world's apart where developers spend much of their time-in code. The FoxPro code model is built on an Xbase design, now more than a decade old, while Access shares common VBA engines and syntax with the rest of Microsoft Office. I believe that Access remains a better choice for most new projects that do not rely heavily on meeting one of the following three criteria:

VFP can share data in Access using ODBC, and Access can share data in VFP using installable ISAM technology, so it is possible for these two tools to coexist within a single project, as required.

Comparing Access and SQL Server

In Chapter 19, "Exploring Client/Server Issues," you will find detailed information about how Access and SQL Server can coexist within a project. SQL Server is only a back-end data storage and retrieval tool and does not provide a user interface for non-developers, so it can not replace Access as a tool for biulding the user interfaces in an application. For our purposes here, I presume that you have already decided to use Access for forms, reports, and code and are trying to decide whether to use Jet or SQL Server for your back-end data storage.

Both products provide you with the ability to create tables and queries (called views in SQL Server). Your application can use these objects in each platform in any of several fashions, but usually via table attachments or through Open Database Connectivity (ODBC) links. Because both products provide effective data repositories, the key difference lies in only one word: server.

When your application asks Jet (a non-server platform) to return records, the application interface makes the determination about how to find the records. As a result, the interface and the computer it resides on do the processing work to filter and sort the requested data. Conversely, when your application asks SQL Server for records, the server product makes the decisions involved in selecting and sorting records, then sends only the requested records back to your interface. The difference in these methods can be significant with the server returning results ten times faster or more depending on the question asked, the structure of the data, and the supporting hardware.

Here are a few areas where you may find that SQL Server makes a better back-end choice than Jet for your application:

Some applications include a mix of both Jet databases and SQL Server databases for a variety of reasons. For example, sometimes we clone server data in a Jet database nightly to give managers a database to run ad hoc queries against without encumbering the busy server or exposing its raw data. In other cases, we use Jet databases as a hub for data that we don't want to put on the server, such as data that must be imported, exported, or reprocessed frequently or perhaps connected via ISAM drivers to other sources like FoxPro. Finally, in some installations, the server stores transaction data and provides transaction control, while lookup values and data extractions are stored locally in Jet for performance or convenience reasons.

Comparing Access and Excel

Many projects that end up in a database begin in a spreadsheet. Users find that logging data into Excel is quick and easy due to the tabular layout. Often, the spreadsheet grows out of control as more and more data is entered, and the type of data includes records that force duplication of entry. In this scenario, Excel becomes inadequate as a data storage receptacle for many reasons with the following four as the most critical:

  1. Tabular layout. Placing all available data in one worksheet is not the appropriate option when the data involves records that have a parent-child relationship. The user either places all the data in one worksheet, which causes duplication of the parent record data, or creates two data worksheets or ranges, which solves the data duplication but does not enable the user to enforce the relationship (cascading deletes are not available in Excel, for example).
  2. Queries. Excel provides the ability to quickly sort the data on multiple columns and to search for specific data items, but does not approach the power of the Access query engine. Also, sorting data in Excel physically reorders the data, which may be cumbersome for the user.
  3. Validation. Excel provides a rudimentary forms technology, but cannot provide the rich event model, data binding, and form control properties necessary to aggressively validate items as they are keyed in.
  4. Reporting. Excel can print tabular data with headers, footers, and other conveniences, but does not easily provide the grouping, sorting, and aggregate mathematics that make Access reporting so powerful.

When the data load exceeds Excel's capabilities, you can use the Access Table Analyzer Wizard to move the data from Excel into one or more Access tables, then build an application atop the data. As part of the Access solution, users or developers may choose to continue to utilize Excel, but as a reporting and analysis tool rather than for data storage.

Extending the Reach of Access

After it is clear that Access is suited for the primary role in a project, the supporting parts must be cast. With Visual Basic for Applications residing in several Microsoft products, the acceptance of tools like Excel and Project as part of an application toolset has risen dramatically among developers. Also, the availability of more Automation server applications that can be invoked from Access, and the tools to build them, has increased the ways that a developer can extend Access, as has the continued expansion of the third-party add-in market.

The following three primary categories of tools effectively augment Access as a development platform:

With the variety of options for extending Access, few database applications you create will utilize Access alone, and even fewer will not use Access at all.

From Here...

In this chapter, we laid a firm foundation for the application development process. Without solid research and planning, the design of your application may be inaccurate or incomplete, and the project consumes more time and money than should be required.

1996, QUE Corporation, an imprint of Macmillan Publishing USA, a Simon and Schuster Company.