Tip on error handling and DAO reference cleanup

[Steve Jorgensen]

When writing VB or VBA code that works with databases or other external
libraries that cannot be trusted to automatically do the right thing when
references to their objects are arbitrarily released, some thought must be put
into how to make sure the objects will all be closed and released in the
correct order, even in the result of an error.

This requirement can make our code really ugly, even following the best of
commonly known best practices.

For instance, technically, the only procedures that should be -required- to
have their own error handlers are procedures triggered by UI events, and those
that must take specific action in response to specific errors. All of our DAO
code, though, requires a clean-up block though, and custom error handling that
either duplicates that logic or resumes to a point just before the clean-up
happens. If the calling code should ultimately be responsible for reporting
and/or logging the error, now we actually have to raise the error again so
that code can respond.

Now, we may find ourselves repeatedly writing code to do things like set up
the database reference, create a Querydef, open a recordset, do one useful
thing, then close the recordset, etc., all with massive setup/teardown
overhead - yuck. In many cases, then, a simple procedure can take 2 screens
worth of code, and most of it has nothing to do with the primary purpose of
the procedure. Code becomes unduely hard to read and maintain. In fact, to
keep code simpler then, we frequently skip using querydefs and parameters when
they would be useful or improve robustness.

Enter custom class modules.

Unlike classes in external libraries, we do have control over what happens
when instances of our custom classes are terminated, and unlike
garbage-collector based languages such as Java, we know that any terminate
procedures will run when the last reference to the object goes out of scope.
The key point here is that this is true even if the reference goes out of
scope when the procedure that referenced it raises an error that pops control
back to a calling procedure!

So - the trick is to make a class that does nothing more than handle the
running queries. This includes maintaining a database, a querydef, and
optionally, a recordset (if the query is a select query). The class also
keeps track of whether it got its database reference by explicitly opening it
(as opposed to being given one, or using CurrentDb to get one), so it knows if
it will be responsible for closing it.

When the class instance terminates, it first closes the recordset if one is
open, releases the recordset, releases the querydef, closes the database if it
is responsible for doing so, then releases the database reference. Notice
that now the calling code needs no direct references to the 3 DAO objects, nor
does it need to clean them up, or include custom error handling if it doesn't
otherwise need to. Also notice how simple the classs can be since it doesn't
need any internal error handling to speak of.

A class like this can easily be under 100 lines long, save you 1000s of lines
elsewhere, and relieve the stress of repeatedly fussing over DAO object setup
and teardown.

Note that this is also useful for ADO even though we have a higher trust in
its ability to handle dropped references. First of all, we don't have 100
percent trust because we've been burned by DAO and other libraries. Second,
the process of adding parameters to an ADO Command object can be arduous, and
our wrapper class is a great place to put a simplified procedure call for
adding such parameters.

[Lyle Fairfield]

Steve Jorgensen wrote:

Enter custom class modules.

Thank you for the tip. Even when we have no particular use for a tip,
reading it can prompt us to review our own practises and, perhaps,
modify them. I think CDMA would be better if each of the regulars
(others too if they wished) took a few minutes to post new procedures,
methods, whatever we are trying, ideas even, from time to time.

--
--
Lyle
--

[Steve Jorgensen]

On Wed, 16 Mar 2005 12:26:18 -0500, Lyle Fairfield <ly******@yahoo .ca> wrote:

Steve Jorgensen wrote:

Enter custom class modules.

Thank you for the tip. Even when we have no particular use for a tip,
reading it can prompt us to review our own practises and, perhaps,
modify them. I think CDMA would be better if each of the regulars
(others too if they wished) took a few minutes to post new procedures,
methods, whatever we are trying, ideas even, from time to time.

--
--
Lyle

You're probably right. In fact, I'll bet you have some good tips to offer...
:)