Access Design and Programming Tips
Pages: 1, 2

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.