O'Reilly Network    
 Published on O'Reilly Network (http://www.oreillynet.com/)
 See this if you're having trouble printing code examples

Access Design and Programming Tips

by Steven Roman, author of Access Database Design & Programming, 3rd Edition

Unlike other applications in the Office Suite, Access requires careful planning and thought before creating something useful. Additionally, even though Word, Office, Outlook, and other applications can be customized using Visual Basic for Applications, Access, in particular, has a gold mine of power that lies beyond the user interface. In this article, I present a few simple tips I have found helpful when designing and programming in Microsoft Access. I hope you will find them useful as well.

Don't touch your PC until you have taken the time to rough out a design of your database.

All but the simplest database applications require some thought about the design of the database itself, that is, the design and content of the tables in the database. (Of course, the forms and reports also require design, but this is a user interface issue, not a database design issue.)

I would strongly urge that you spend some time with pen and paper roughing out this design before even touching the computer. You can waste a lot of time redesigning existing Access tables, not to mention the consequent redesign of dependent queries, forms, and reports. So it is much better to have pretty good idea of your database's table design before starting Access.

To be sure, you will probably spot changes that need to be made after you are well into the development cycle of the project, but that can't be helped. I don't know anyone who can see completely through to the end of a project during the initial stages of development.

Related Reading

Access Database Design & Programming, 3rd Edition Access Database Design & Programming, 3rd Edition
By Steven Roman
Table of Contents
Sample Chapter
Full Description

Keep one thing in mind: an Access table is intended to represent a single entity. For instance, a table may represent book entities, or author entities, or publisher entities--but only one of these entities. A table whose fields are:

Author ID
Author Name
Pub ID
Pub Name

is clearly a table about three entities: books, authors, and publishers. This is bad database design. Keeping this one axiom in mind will save you from numerous problems down the road.

There may be more than one right design for a database.

Recently, I had to import a collection of Excel spreadsheets into Access for a well-known restaurant company. Each sheet represented advertising data for a given market. For instance, the Los Angeles market might have data such as (This data is fictional.):

DMA: Los Angeles
Daypart Mix EE PR LE LN
M1 12% 56% 12% 20%
M2 12% 56% 12% 20%
M3 12% 56% 12% 20%
M4 12% 56% 12% 20%
M5 12% 56% 12% 20%

The rows of the table refer to different advertising campaigns (also called modules). The column headings refer to times of day (that is, dayparts) in which the advertising took place. For example, advertising for module 1 took place as follows: 12 percent early evening (EE), 56 percent prime time (PT), 12 percent late evening (LE) and 20 percent late night (LN). Finally, DMA stands for Designated Market Area.

Two ways present themselves in terms of turning this data into an Access table. One is a horizontal design, using the fields:


The other is a vertical design, using the fields:


In the former case, each row of the spreadsheet becomes a row of the Access table, as in:

DMA=Los Angeles, Module=M1, EE=12%, PR=56%, LE=12%, LN=20%

In the latter case, each row of the spreadsheet becomes four rows of the Access table:

DMA=Los Angeles, Module=M1, Daypart=EE, Value=12%
DMA=Los Angeles, Module=M1, Daypart=PR, Value=56%
DMA=Los Angeles, Module=M1, Daypart=LE, Value=12%
DMA=Los Angeles, Module=M1, Daypart=LN, Value=20%

Which is good database design? They both are. It just depends on what you need to do with the data. In fact, either table may suit your needs.

Comment on this articleDo you have any Access design and programming tips of your own?
Post your comments

The first table is about Module entities. The second table is about daypart entities. If you need to do a lot of calculation involving different dayparts, then the second table may be better suited, but if the calculations involve only one daypart at a time, then the first table may be better, for it is generally easier to compute across rows than across columns.

The real point here is that there may be more than one reasonable database design for a given project, and you may need to do some experimenting before you can select the optimal design for your project.

Be aware of AutoNumber field limitations.

As you probably know, AutoNumber fields can save you work when adding data to a table programmatically. You don't need to find an available key value and confirm that it is free because AutoNumber fields do this for you automatically.

However, there can be a price to pay--one I didn't know about until it was too late. Namely, you cannot have two AutoNumber fields in the same table.

This might not seem like a problem at first, but consider the possibility that you might want to merge two tables, each with an AutoNumber field, into a third table.

If this ever comes up, you won't be able to create a make-table query that includes the AutoNumber fields from each table. Just keep it in mind.

The Access Query Designer is useful for creating complex SQL statements.

I often need to create complex SQL statements for my applications. While it is more macho to type them out from scratch, it is easy to make a mistake, either in syntax or in semantics.

It is far simpler to use the Access Query Designer to create the query, then go to the SQL view, copy the SQL statement, and paste it into your code module. I even do this with simple queries, particularly at 3 a.m. when I can hardly see straight. And who's to know?

Subqueries are a powerful tool. Don't overlook them.

Subqueries are powerful tools and it may be well worth your time to study up on them. I have added a chapter to the third edition of my book Access Database Design & Programming that gives several examples of the use of subqueries. Here is one example.

The computation of running sums is a common one. Consider Table 1, called Running, which contains the duration (in hours, say) for various events

Table 1. Running
Event Duration
1 1
2 5
3 6
4 3
5 4
6 1
7 8
8 2

For each event, we want to compute the sum of all the durations of the events that precede that event. This sum is a running sum.

One solution is to use a subquery in the form of a nested SELECT statement; that is, a SELECT statement within the main SELECT statement. This is permitted in Access SQL provided that the internal SELECT statement returns at most one record.

Here is an SQL statement that does the job. Note the use of table aliases, which are needed because we must refer to the Running table in two contexts:

SELECT R1.Event,
(SELECT SUM(R2.Duration) FROM Running As R2 WHERE R2.Event < R1.Event)
   AS StartTime
FROM Running As R1

The internal SQL statement:

SELECT SUM(R2.Duration) FROM Running As R2 WHERE R2.Event < R1.Event

returns the sum of the duration for all events preceding the current event, which is denoted by R1.Event. All you need to do is open a query based on the main SQL statement.

Use the best recordset type.

If you still use DAO (as I do), then it is easy to become complacent about creating recordsets:

Set rs = db.OpenRecordset("MyTable")

But it is important to remember that DAO (Data Access Object) provides several forms of recordsets: table-type, dynaset, snapshot, and forward-only. The point here is that if a snapshot or forward-only recordset will suit your purposes, you should use it, because it will be more efficient and therefore your code will run more quickly.

Snapshot recordsets are faster to create and to access than dynaset recordsets, but they use more memory because the entire record is stored in memory. The downside is that you cannot update the records in a snapshot recordset.

Forward-only recordsets are like snapshot recordsets, but you can only move forward through the recordset.

Ultimately, the only way to tell whether you will save significant processing time is to try the various suitable recordset types in a given situation. The point here is to not fall into the habit of always creating a table-type or dynaset-type recordset.

Warn your clients to do frequent database compacting.

I recently received a frantic call from a client who said, "Your Access application seems to take forever to run lately." I could see no reason why the behavior of my application should change, so I asked the client to tell me about the PC on which he was running the application. Just trying to get the processor type and memory size was not easy over the phone, for my client was, shall we say, not very computer savvy.

In any case, it took me longer than it should have to ask if my client was compacting the database regularly. His reply was, "What is compacting?"

Even though in my application, after every operation, I display the message "Please compact the database" in a text box in the user interface, this message was obviously getting ignored.

I don't know how to make clients compact the database, but from now on I won't forget to ask this question first thing!

Don't forget about referential integrity. It can be very important and also a bit dangerous.

Referential integrity is a handy feature and it should not be overlooked. To set referential integrity, you use the Access References dialog.

To illustrate, imagine you have two tables: Books and Publishers. The Publishers table has an ID field (the primary key) named PubID, along with fields for various publisher data. The Books table also has a field named PubID, which is the foreign key that links each book to its publisher.

Now, we do not want users of the database to be able to:

    Add a book to the Books table for a publisher that is not listed in the Publishers table.

    Change a PubID in the Publishers table, without making the same changes in each Books row associated to that table.

    Delete a publisher from the Publishers table without deleting the corresponding books from the Books table.

These problems can all be taken care of using referential integrity. Just setting referential integrity takes care of the first issue above: Access will not allow the user to add a record to the Books table with a PubID that is not present in the Publishers table.

Cascading updates takes care of the second problem above: by choosing cascading updates, when the user changes a PubID in the Publishers table, the change is also made in each relevant row of the Books table.

Cascading deletes takes care of the third issue: when the user deletes a publisher from the Publishers table, all records in the Books that with that PubID are automatically deleted!

In summary, referential integrity is very helpful, but note that cascading deletes can be dangerous because they may result in massive deletions from a related table.

To be sure, there are times when referential integrity gets in the way. One case is during development, when you may want to play around with the table data for testing purposes. In such a situation, you may need to temporarily turn off referential integrity.

Send Microsoft a message asking them to support DAO!

My final tip will be controversial. I like DAO, I don't like ADO. I wish Microsoft would continue to support DAO.

Actually, DAO can do one thing that ADO cannot do: save queries in the Access environment. Yes, I know about ADOX. In fact, there is a chapter on ADOX in the third edition of my book, Access Database Design & Programming. However, you still cannot save a query. A query created using ADOX will not appear in the Access 2000 user interface.

In any case, DAO is well understood, easy to use, does what is necessary, seems quite stable, and is optimized for Microsoft Jet. Why not support it?

If you feel the same way, you can go to Microsoft's Access Web site and choose Contact Us (at the bottom of the page). Tell them that you would like them to reinstate support for DAO.

O'Reilly & Associates recently released (January 2002) Access Database Design & Programming, 3rd Edition.

Copyright © 2009 O'Reilly Media, Inc.