473,320 Members | 2,122 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Tip on error handling and DAO reference cleanup

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.
Nov 13 '05 #1
2 2981
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
--
Nov 13 '05 #2
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...
:)
Nov 13 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

12
by: Ritz, Bruno | last post by:
hi in java i found that when a method has a throws clause in the definition, callers must either handle the exceptions thrown by the method they are calling or "forward" the exception to the...
5
by: John Perks and Sarah Mount | last post by:
When handling resources in Python, where the scope of the resource is known, there seem to be two schools of thought: (1) Explicit: f = open(fname) try: # ... finally: f.close()
6
by: Squirrel | last post by:
I have a command button on a subform to delete a record. The only statement in the subroutine is: DoCmd.RunCommand acCmdDeleteRecord The subform's recordsource is "select * from tblVisit order...
4
by: Sandy | last post by:
Hello - I read an interesting article on the web wherein the author states he doesn't handle too many errors at page level, but handles them at the application level. He further goes on to show...
8
by: jcrouse | last post by:
I am using the following code to trap errors in a sub routine: Try Executable code Catch ex As Exception Dim strInputE As String = Application.StartupPath & "\Error.txt" Dim srE As...
3
by: dgiagio | last post by:
Hi, I'm creating a SMTP application and I would like to hear opinions about error handling. Currently there are two functions that communicate with the remote peer: ssize_t...
0
by: ruju00 | last post by:
I am getting an error in Login() method of the following class FtpConnection public class FtpConnection { public class FtpException : Exception { public FtpException(string message) :...
4
by: John Wright | last post by:
I need some good ideas or references for robust error handling in VB.NET. I am using try catch but find myself using the generic exception handler. I would like to get more precise error handling...
35
by: jeffc226 | last post by:
I'm interested in an idiom for handling errors in functions without using traditional nested ifs, because I think that can be very awkward and difficult to maintain, when the number of error checks...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.