Tuesday, May 19, 2009

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?
Entity-Relationship Diagramming
or you can download them here:

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.



Training Connection said...

Excellent summary of the steps to Design an Access Application. The design phase is critical, the more time spent "up front" the more time saved on the back end. Thanks for the prototyping information. Could have used it last year on a large form I was creating.

a.p.r. pillai said...

I adopt both approaches depending on the nature of the project. When data is maintained in Excel/dbase/AS400 etc. bottom up approach will give better insight to the over all project design.

First, I will create a Prototype database based on the initial study of the Project and create a Software Requirement Specification Document based on what I understood about their requirement and submit to the Customer with essential Screen shots of Forms and Reports etc. for their review and approval with comments.


Justin P. said...

Since this is my first time ever using Microsoft Access 2010, how would I create a simple contacts database that is sophisticated. I have made a table for basic contact information, hobbies, vehicles, and relationships. I am trying to make a database so I can find someone in particular that fits my needs or criteria for a certain activity. Am I approaching my objective correctly?

jslingerland said...

This is fantastic. I've been trying to build my own database from the ground up for the first time and have found it to be extremely frustrating with the limited knowledge I have. I haven't put too much thought into the design phase myself, but you certainly have convinced me that I need to begin truly focusing on it just as much as I have been the scripting behind it.

wayne said...

I am in an Access class, and found your tutorial to be very enlightening. I started to outline my database and stated to find that I was missing information. Well, missing might not be the best word, maybe figured out more data-sets to track and report on is better.
I am building my database to be easy to use, yet complete. I want it to be able to grow as ideas and needs expand. I will be returning to this tutorial and this blog in general as my class continues.
Thanks for the wisdom...

wayne said...

I am in an Access class, and found your tutorial to be very enlightening. I started to outline my database and stated to find that I was missing information. Well, missing might not be the best word, maybe figured out more data-sets to track and report on is better.
I am building my database to be easy to use, yet complete. I want it to be able to grow as ideas and needs expand. I will be returning to this tutorial and this blog in general as my class continues.
Thanks for the wisdom...