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.

.

New Sample: Report_RepFooterformultiColumns

Report_RepFooterForMultiColumnsSample

by AD Tejpal

This sample db demonstrates handling of report footer for a multi-column report with Down and then Across (D&A) layout in such a manner as to prevent its spill-over to a redundant extra page.

In a multi-column report with D&A layout, spill-over of normal report footer section to an extra page, despite adequate space being available at the end of last column, is attributable to the fact that page footer has already been hit by the first column. Once that happens, there is no longer any room for the report footer, which has to move over to next page.

Two alternative styles are demonstrated in this sample db:

1. Style A - Use the page footer in dual role.
2. Style B - Place the report footer in an outermost dummy group footer.

You can find the sample here: http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=454

.

What is the fastest way to return the Record Count from an Access Query?

In my earlier post (What is the fastest way to return the Record Count from an Access table?), I found the fastest way to return the record count of an Access table. It turns out the RecordCount property of the OpenRecordset method is at least ten times faster than any other method.

Unfortunately, the RecordCount property does not work the in the same way for a query as it does for a table. For a query, it will return a record count of 1, regardless of the number of records in the query. The reason for this has to do with the three recordset types that OpenRecordset returns.

If I open a table-type recordset,

OpenRecordset("MyTable", dbOpenTable)

The RecordCount property returns the number of records in the table. When the object I'm opening is a table, it defaults to dbOpenTable, so I don't really need to specify it. What makes this so fast is that the record count is actually stored with the table. It doesn't need to calculate the record count at all.

However, if I open a Dynaset recordset,

OpenRecordset("MyTable", dbOpenDynaset)

the recordset property returns only the number of records accessed, which is 1 when the recordset is first opened. In order to find the actual number of records, I have to use the MoveLast method to move to the last record. Then the RecordCount will return the accurate number.

This should be much slower, since the MoveLast method is not particularly fast. But is this true? I propose to find out.

I tested both native and linked tables, so I had two databases on a standard 100mbps network.

Methods

Again, there are three basic methods to find the number of records in a query:

1. DCount domain aggregate function.
   
2. SQL Count aggregate query.
   
3. Recordcount property of the OpenRecordset method.
   

Each of these methods has several variations, but as I showed in the last post, the difference isn't enough to worry about. So this time, I only tested one variation. I used a query which returned 7125822 records from indexed native and linked tables across a standard 100mbps network.

So here are the basic methods I tested.

Method 1 (DCount)

lntCount = DCount("*", "MyQuery")

Method 2 (embedded SQL)

lntCount = CurrentDb.OpenRecordset _
("SELECT Count(*) AS [CountAll] FROM MyQuery;") _
![ CountAll]

Method 3 (saved query- same query as Method 2)

lntCount = CurrentDb.OpenRecordset("CountQuery") _
![CountOfID]

Method 4 (RecordCount method)

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("MyQuery", dbOpenDynaset)
rs.MoveLast
lntCount = rs.RecordCount

Testing the Methods

To minimize any differences in network traffic and processor usage, I ran each of the methods 10 times and averaged the elapsed times. The Figure 1 below shows the outcome.

Figure 1: Comparison of Record Count Methods

	Native Table	Linked Table
Method 1	10.7 seconds	10.1 seconds
Method 2	10.6 seconds	10.1 seconds
Method 3	10.3 seconds	10.3 seconds
Method 4	10.4 seconds	10.5 seconds

To be honest, I expected that the RecordCount method would be significantly slower than the other methods. I assumed that while DCount and SQL would make use of the index while the MoveLast method would have to do a full table scan. That should be slower, but that doesn't appear to be the case.

As far as I can determine, there isn't any significant difference between the various methods. I hate being wrong.

Parse Multivalue Field To Normalized Structure.mdb

Parse Multivalue Field To Normalized Structure.mdb

by Roger Carlson

This sample illustrates how to write values from a multiple valued field (not the Access 2007 multi-value field) into a normalized Many-to-Many structure. The original table is called "ContactsDownloadMultivalue". The contact information is written into "tblContacts". The category information from the multiple valued field is written to "tblCategories".

Then a record is created in jtblContactCategory junction table, establishing the many-to-many relationship.

You can find the sample here: http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=449

.

What is the fastest way to return the Record Count of a table?

As with most things in Access, there are many ways to get the record count of a table. So which is the fastest? The answer is: it depends.

There are a many factors that determine performance:

• Whether the database is on the local drive or a network,
• Whether the table is native or linked,
• Whether the table is indexed or not,
• The number of records in the table.

There are other factors, of course, like processor speed and disk access speed, but we'll assume we're testing all of the proposed methods on the same machine, so the results will be proportional.

I don't propose to test all combinations of these performance variables. If there are only a few records, all the methods shown will return the record count so quickly that it doesn't really matter. Similarly, if the database is on a local drive (C:), there is no perceptible difference. On the other end, if the table is not indexed, some of the methods are so slow, they are impractical.

So I've chosen a table with 7125822 indexed records (mostly because I had a table with 7125822 records available). I will test both native and linked tables, so I need two databases, which I will access across a standard 100mbps network.

Methods
There are three basic methods to find the number of records in a table:

1. DCount domain aggregate function.
2. SQL Count aggregate query.
3. Recordcount property of the OpenRecordset method of the DAO database object.

Each of these methods has several variations as well. For instance, both the DCount and SQL methods can count on * or on a specific field. This may affect performance. The SQL method can use a saved query or an embedded SQL statement, which also may affect its performance. Lastly, the RecordCount method will ONLY work on a native table, not on a linked table. However, I can use the OpenDatabase method to open a linked table as a native table.
So here are the basic methods I tested.

Method 1a (DCount with *)

lntCount = DCount("*", "MyTable")

Method 1b (DCount with fieldname)

lntCount = DCount("ID", "MyTable")

Method 2a (embedded SQL with *)

lntCount = CurrentDb.OpenRecordset _
("SELECT Count(*) AS [CountOfID] FROM MyTable;") _
![CountOfID]

Method 2b (embedded SQL with fieldname)

lntCount = CurrentDb.OpenRecordset _
("SELECT Count([ID]) AS [CountALL] FROM MyTable;") _
![CountALL])

Method 3 (saved query)

lntCount = CurrentDb.OpenRecordset("Count_Query") _
![CountOfID]

Method 4a (RecordCount Native table)

lntCount = CurrentDb. _
OpenRecordset("Mytable").RecordCount

As I said, the RecordCount property will only return the correct record count if the table is a LOCAL table. If it is a linked table or a query, it will always return 1 as the record count. However, I can explicitly open the table with the OpenDatabase method.

Method 4b (RecordCount Linked table)

lntCount = OpenDatabase _
("c:\My Documents\LinkedDatabase.mdb"). _
OpenRecordset("Mytable").RecordCount

Testing the Methods

To minimize any differences in network traffic and processor usage, I ran each of the methods 10 times and averaged the elapsed time. The Figure 1 below shows the outcome.

Figure 1: Comparison of Record Count Methods

The results of whether to use a * or a fieldname in either the DCount or SQL statement appears inconclusive. It also doesn't seem to matter much if the table is native or linked. However, I can say that that the RecordCount property of a TableType Recordset object is the fastest method by at least an order of magnitude.

.

How Do I Decompile a Database?

Decompiling An Access Database

In order for any program code to be run by a computer, it must be converted to machine-readable code. This code is called Object Code. The text version of this program that you and I can read is called Source Code.

In Access, the process of producing Object Code from Source Code is called "compilation". Whenever code is run for the first time in Access, the code is first compiled. (You can also compile it yourself by pushing the compile button). The only place you can make changes to code is in the Source Code, which must again be compiled into Object Code.

Occasionally, code can be deleted from the Source Code, but for some reason is never removed from the Object Code. This code is never again seen on the screen as text, but is still sitting there somewhere in the database file. This can, at times, interfere with the normal operation of the program.

To remove these stray bits of code, you can "Decompile" your database and then re-compile it. This process removes all of the compiled Object Code, then when you re-compile it, you only get Object Code that reflects the current Source Code.

To Decompile your Access database, do the following:

1. Click the Start button on the task bar and choose Run…
2. In the Run dialog box, type the following:
   "C:\Program Files\Microsoft Office\Office\Msaccess.exe" /decompile
   where the first part (in quotes) is the complete path to your Access program. If you have the default installation, it is likely that it is just as listed here. Click OK.
   Note: if you have Windows Vista, just type the command line in the Start Search box of the Start menu.
3. This will open Access and allow you to choose which database to open. Whichever database you open will be decompiled.
4. Choose a database and open it. Access 97 used to give you a dialog which told you the database had been decompiled, but newer versions do not. Nevertheless, the database has been decompiled.
5. Open any Module in design view, or any Form or Report in design view and choose View > Code from the menu. In the next screen, choose Debug > Compile from the menu bar.
   Note: In Access 2007, you can also choose Database Tools on the ribbon, then select Visual Basic. Then choose Debug > Compile.
6. Your database has now been Decompiled and Re-compiled.

Creating a Shortcut:

I use decompile quite frequently, so instead of typing the command line into the Run box, I've created a shortcut on my desktop. There are several ways to create a shortcut, the easiest is to use the Shortcut Wizard.

Right-click anywhere on your desktop and select New > Shortcut. The wizard will give you a dialog box allowing you to browse to the file you want. It should be the same as above in Step 2. Once you've browsed there, add the "/decompile" switch to the end as shown below. Be sure to separate the decompile switch from the file path with a space. It will look as follows:

Click Next.

It will ask to name your shortcut. Choose something descriptive like Decompile Access 2003. And click Finish.

The finished shortcut will look like this:

Now when I want to decompile a database, I just click the shortcut and start with Step 3 above.

.

The Normal Forms: In a Nutshell

This article has moved: The Normal Forms: In a Nutshell