How do I Create an Application in Microsoft Access?

Application Development Process

I am often asked how I create an Access application. That’s a difficult question because each system has different business rules. However, there is a more or less standard process I go through when creating any database application. That's what I'll describe here.

Data Collection:
I start by collecting as much data as possible.

If the application is to replace an existing system (either paper or electronic) I will collect samples of all paper forms, data entry forms, printed reports, other outputs (like emails or documents generated), plus any documentation.

Basically, I want to collect all input and output from the current system. It's a pretty simple idea. All data elements on any input or output form must be represented somewhere in the database. But it's surprising how often data elements are missed simply because the developer didn't look at all the forms or reports.

Lastly, I'll interview everyone involved with the application. This includes both management and staff. Often, the administrative assistant who will be entering data into the application has insights that management (with its broader view) will miss.

If it is a brand new application, it's a little more difficult because I have to rely on what people think they need, which is often incomplete. This requires several rounds of interviews. I have seen too many bad applications which were based on a single client interview.

Database Design:
This is the single most important part of the process and should take nearly as long as the application development itself. If you get the design right in the beginning, the application development goes much more smoothly.

I follow an abbreviated version of Michael Hernandez's process from his book "Database Design for Mere Mortals". This book stresses process, rather than the academic niceties of Normalization. As Hernandez suggests, I start with an analysis of the entities and how they relate to each other. The final result is usually nearly normalized.

This process cannot be entirely divorced from the Implementation process (in my mind at least), because as I design the tables and relationships, I am already getting an idea of how they will be implemented. (Part of the Hernandez process is to identify Mission Objectives. These usually end up translating into forms or reports.) At the end of this process, I have an ER diagram and a scope document.

For more details of this process, see my blog serie:
What is Normalization?
and
Entity-Relationship Diagramming
or you can download them here:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=238

Database Creation:
Not to be confused with Application Design. At this point, I create all of the tables and relationships in my design. This becomes the Back-End database.

Nearly all of my databases are "split". Access allows you to create an application where the forms, reports, and tables are all in the same file. But for most applications, it is useful split the database in a "Front-End" (FE) and "Back-End" (BE). The tables and relationships to in the BE, while everything else (forms, reports, queries, and code) go in the FE. The BE is the "database" and the FE is the "application".

Application Design Philosophy:
There are many methods for applications development. Two of the most common are Top-Down and Prototyping.

In the Top-Down approach, you collect customer requirements, create your forms and reports, present it for customer approval, develop the application, test it, deploy it, then maintain it. The problem with this approach is that you have to understand the customer's requirements (and they have to understand their own requirements) thoroughly before you can start designing the application. If you get something wrong and the customer doesn't catch it during the presentation stage, you can produce something quite unworkable for the customer.

Prototyping is similar to the top down approach, but you do it in small sections of the application, creating quick, working samples for the customer to play with and critique. During this process, you may discover that what you understood the customer wanted wasn't really what they wanted.

The prototyping approach requires more customer interaction, but it allows you to get immediate customer feedback. As a Rapid Application Design environment, Access is a perfect prototyping tool.

I should point out that this ONLY applies to the application development phase. The database design phase for both approaches is nearly identical. You should try to figure out as much about the customer's data needs before beginning the application development. However, using the prototyping approach, you will discover flaws in your database design (and allow you to correct them) before you get too far into the project.

Application Creation:
I start with a BoilerPlate database which has many standard forms already built. (I have a couple of samples on my website.) Every application will need a main screen, forms for maintaining lookup tables, a form for running reports and so forth. Using a boilerplate also gives a measure of uniformity to my applications. I link my tables into this and it becomes the Front-end.

Next I create the Maintenance forms. These are the forms that the user can use to modify the data in their lookup tables. The forms are standardized so the user gets used to the way they work. My boilerplate has a form which lists all of these forms (they all have an "mnt" prefix) in a list box, so the user can just select the table they want to maintain and the appropriate form appears.

At this point, I either give the unfinished application to the user to fill the lookup tables manually or have them send me a spreadsheet with the information that I can import to the lookup tables.

My next step is the main data entry form. In most of my applications, there is a main transaction table that will be the entry point for most data entry. If I have a design like this:

Patient ---< Visits ---< Diagnoses ---< Treatments

I try to determine which one is the main transaction table. In other words, which will I be using most and is the most logical starting point. (To be sure, this is a subjective process and there are many ways it could be done.) In this case, I would probably start with Visits. I'd have a combo box to allow for the selection of the patient and also a button that would launch the Patient form for adding a patient that is not in the list. (Boilerplate.mdb has an example of this too.)

Most of these samples (with the exception of Training Registration and Books) are not full applications, just the bits necessary to illustrate the main problem. There may be multiple data entry forms. For instance, in the Training Registration sample, there is a form that allows you to enter students and assign them courses. There is also a form that allows you to enter courses and assign students to them. Two different ways to do the same thing.

Deployment and Testing:
With the prototyping process, there is no single deployment stage. The application is deployed for testing throughout the process. However, at some point, the input part of the application is "done" and is ready for use. At this point, I deploy the application to the user to fill with TEST transaction data. I emphasize that they need to use REAL data just as they would in their daily work.

However, I emphasize it is still test data. I will delete all of it just before we put it into production. Using real data will quickly point out any flaws in the design and allowing a knowledgeable user to test it will highlight problems that I as the developer cannot find. As they find problems, I will address them. This is all part of the cyclical prototyping design process that I follow.

Reports and other Output:
Once they have a good group of representative test data, I produce the reports that they requested back in the design process. When these are done, I wipe out the test data and deploy the final version to the customer.

A Word of Caution:
The process I've described here is just the development side of application development, not the business side. Here, I've ignored the business side which includes a proposal, objectives, milestones, customer signoff, formal testing procedures, documentation, and invoicing. This is meant to give you an idea of a general process for you to create your own database application.

.