Explicit destruction of Objects declared - Style Thing

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi everyone,

Is there any difference between the following two ? If so, which one is
better, and why ? Does this make any sense ? Any suggestions are appreciated.

Method I Method II
----------------------------- -------------------------------
Dim db As DAO.Database Dim db As DAO.Database
Dim rs As DAO.Recordset Dim rs As DAO.Recordset

'Set Statements here 'Set Statements here

'Code here 'Code here

Set db = Nothing --> ExitHandle:
rs.Close Set db = Nothing
Set rs = Nothing rs.Close
ExitHandle: <-- Set rs = Nothing
Exit Sub Exit Sub

ErrHandle: ErrHandle:
'error handling here 'error handling here
Resume ExitHandle Resume ExitHandle
 
Sreedhar said:
Hi everyone,

Is there any difference between the following two ? If so, which one
is better, and why ? Does this make any sense ? Any suggestions are
appreciated.

Method I Method II
-----------------------------
-------------------------------
Dim db As DAO.Database Dim db As DAO.Database
Dim rs As DAO.Recordset Dim rs As DAO.Recordset

'Set Statements here 'Set Statements here

'Code here 'Code here

Set db = Nothing --> ExitHandle:
rs.Close Set db = Nothing
Set rs = Nothing rs.Close
ExitHandle: <-- Set rs = Nothing
Exit Sub Exit Sub

ErrHandle: ErrHandle:
'error handling here 'error handling here
Resume ExitHandle Resume ExitHandle
Click to expand...

You should (generally) clean up in reverse order of creation. In the second
example you are destroying the db object before the rs object and the rs is a
child of the db. If that doesn't cause an error it should at least make little
sense to do it that way.
 
Hi Rick,

Got your point about the order of clean-up. Thank you.

There is no difference in both the examples **IF** the procedure runs
without ERROR. The second one cleans up in the ExitHandle, and the first one,
before the ExitHandle. Should an Error occur, I thought, with those objects
already loaded into memory, the second one will clean up with the "Resume
ExitHandle", whereas the first one simply exits without cleaning. Am I
getting it right here ? Does this still make any sense ? Any tips are welcome.

Thank you
 
That's correct.

Method II is the right way (once you fix the error with the order of
instructions)
 
Actually, looking at your two samples again, your first sample won't even
compile, since you're telling it "Resume ExitHandle", and you haven't
defined "ExitHandle"
 
Sreedhar said:
Hi Rick,

Got your point about the order of clean-up. Thank you.

There is no difference in both the examples **IF** the procedure runs
without ERROR. The second one cleans up in the ExitHandle, and the
first one, before the ExitHandle. Should an Error occur, I thought,
with those objects already loaded into memory, the second one will
clean up with the "Resume ExitHandle", whereas the first one simply
exits without cleaning. Am I getting it right here ? Does this still
make any sense ? Any tips are welcome.

Thank you
Click to expand...

Doh! I didn't even see that distinction. I always clean up in the Exit area with
an On Error Resume Next at the top of it. That way if I attempt to clean up an
object that was never initialized because the error occurred before that
initialization had occurred, the exit area still runs through without an error.
With your example that would look like...

Dim db As DAO.Database
Dim rs As DAO.Recordset

'Set Statements here

'Code here

ExitHandle:
On Error Resume Next
rs.Close
Set rs = Nothing
Set db = Nothing
Exit Sub

ErrHandle:
'error handling here
Resume ExitHandle
End Sub

Others prefer to test each object to see if it was initialized in the Exit area
and only clean it up if it was thus avoiding the need for the On Error Resume
Next. In my opinion that is a lot of extra code to write without much benefit
as I have never experienced any issues with Resume Next when used in this
specific context.
 
Hi Doug,

Thanks for the confirmation.

And, yes, there is an ExitHandle in the first method too.
 
Method III
Dim db As DAO.Database
Dim rs As DAO.Recordset
'Set Statements here

'Code here
Exit Sub

ErrHandle: ErrHandle:
'error handling here 'error handling here
End Sub

In general,
Set db = Nothing
rs.Close
set rs = Nothing
is not required at the end of a subroutine. Those lines are
in MS code examples mostly for compatibility with ASP.
The separate exit label is there so you can do exit handling,
so if this is the only exit handling you have, you can drop
that as well.

You should close objects when you are finished with them
(that is the reason why the lines are required in ASP), but
Ending the subroutine does that.

There is a particular problem when you have code like this:

set db = CurrentDB
....
db.Close

Closing the current db can orphan open recordsets. Access
re-opens CurrentDB, so your application doesn't crash, but
you may have trouble closing the application. Apart from not
making that mistake, you avoid the problem by closing recordsets
you aren't using. But that means not leaving module level or form
level or static recordsets open (Or 'server level' recordsets in ASP).
A good way to avoid that is to not use module level or form level
or static recordsets. Using 'close' immediately before a local recordset
is automatically closed does not do anything useful, and doesn't
even address the problem.

(david)
 
Method III
Dim db As DAO.Database
Dim rs As DAO.Recordset
'Set Statements here

'Code here
Exit Sub

ErrHandle: ErrHandle:
'error handling here 'error handling here
End Sub

In general,
Set db = Nothing
rs.Close
set rs = Nothing
is not required at the end of a subroutine. Those lines are
in MS code examples mostly for compatibility with ASP.
The separate exit label is there so you can do exit handling,
so if this is the only exit handling you have, you can drop
that as well.

You should close objects when you are finished with them
(that is the reason why the lines are required in ASP), but
Ending the subroutine does that.

There is a particular problem when you have code like this:

set db = CurrentDB
...
db.Close

Closing the current db can orphan open recordsets. Access
re-opens CurrentDB, so your application doesn't crash, but
you may have trouble closing the application.
Click to expand...

David, it's my understanding that a call to close CurrentDb is just
ignored; these days, at least, though I believe it was different in the
early days of Access. I think that understanding is derived from an
authoritative post by Michael Kaplan, but a quick Google hasn't turned
up the post in question. Have you tested this statement about orphaning
recordsets in recent years?

I agree that you shouldn't close CurrentDb, on the principle that you
don't close what you didn't open, but I thought MS made a specific
provision for user obstinacy and guarded against it.

I believe I have seen problems with closing DBEngine.Workspaces(0)(0),
though.
Apart from not
making that mistake, you avoid the problem by closing recordsets
you aren't using. But that means not leaving module level or form
level or static recordsets open (Or 'server level' recordsets in ASP).
A good way to avoid that is to not use module level or form level
or static recordsets. Using 'close' immediately before a local
recordset is automatically closed does not do anything useful, and
doesn't
even address the problem.
Click to expand...

I prefer to explicitly close objects I've opened, on the belt and
suspenders principle. There have been problems reported in the past
with objects not getting destroyed and released properly, and although
those problems may have been fixed, I don't necessarily want to trust
the runtime environment to clean up after me. It may be
obsessive-compulsive, but that's not a bad trait for developer.
 
Hi,

This is another surface to the issue that you have brought up. All the way,
I have been preached by the books and examples to stick to explicit
destruction of objects that I have created. That is deeply engraved in my
mind that it is my fundamental responsibility to do so. If there is nothing
harmful in doing so, I feel comfortable to stick to that.

And, regarding the db.Close that you mentioned, I didn't use it in my
examples. Also, here is what I have read about it. Your comments are welcome.

<<Quote>>
The problem stemmed from the fact that in Access 2.0, if you called the
Close Method against DBEngine (0) (0) or CurrentDb, the call would fail, but
problems would occur with any open objects,specifically recordsets. This
resulted either in an Application hang, or with Access refusing to close.
Following the fix to this bug (where the internal "OK to Close?" check
routine was moved from the end of the method, to the beginning), calls to
db.Close issued against either DBEngine (0) (0) or CurrentDb() now do
absolutely nothing to the permanent internal database object. Many people
still believe that this long dead bug still exists......

However, although you can call Close if it gives you a warm fuzzy feeling
inside, any attempt to do so against DBEngine(0)(0) or CurrentDb() will
literally do nothing. Therefore, db.Close is redundant.

Some people have experienced bugs with DAO Recordset object, in particular,
the RecordsetClone object, where an orphaned reference sometimes prevents
Access from closing. There has never been any such bug with the Database
object.

Destroying object references is a different affair. For the present, you
still should set Database object variables to Nothing when you have finished
with them, as you would with any other object reference. It is perfectly safe
to do so, regardless of whether the reference came from DBEngine(0)(0) or
CurrentDb...........
<<Unquote>>
 
Sreedhar said:
And, regarding the db.Close that you mentioned, I didn't use it in my
examples. Also, here is what I have read about it. Your comments are
welcome.

<<Quote>>
Click to expand...

That quote sounds like the MichKa post I was referring to.
 
I confess that I can't remember what the situation was with A2.0,
only that it changed with A97.

However, my experience with orphaned recordsets is with
A2K, and closing the current database is the only way I have
ever managed to get orphaned recordsets with A2K.

Recordset retain their own references to their database object,
which enables them to remain open even if there is no explicit
reference for the database object. However, releasing the
database object without first closing the recordsets leaves you
with no easy way to find and close the recordsets.

Probably the main reason that I so rarely get orphaned recordsets is
that I almost exclusively use local variables for recordsets, which are
therefore closed cleanly and correctly according to the language definition.

I would encourage you to use local recordsets instead of form level
or module level or statically declared recordsets: they are generally
more trouble than they are worth, and should be used only rarely,
for specific tasks which can't be performed any other way.

Long running transactions, database variables opened in different
workspaces, and even database variables opened against other
databases, are excuses sometimes used for not closing objects, and
so are associated with orphaned references, but the basic solution is
to never leave recordsets open, and in Access the best way to achieve
that is to use local recordsets so that they are closed automatically.
(Note that in ASP, the Server Session never went out of scope)
destruction of objects that I have created. That is deeply engraved in
mind that it is my fundamental responsibility to do so. If there is
Click to expand...

"C did not have memory management. Programmers had
to do it themselves. Good programmers were good at
memory management: bad programmers were bad a memory
management. C programmers looked at BASIC code and thought
they were looking at very bad C: they looked at BASIC
programmers and thought they were looking at very bad
C programmers."

I too have read many books written by people from or influenced by
the C community. However, I was a BASIC programmer before,
during, and after the time in which 'BASIC programmer' was generally
a contradiction in terms, so I was only ever amused by the C paradigm.

After a 25 year lacunae, the world is shifting back to rationalism on
memory management and hosted languages, and you'll see that the
c# books and examples are rather different to the older books and
examples.

However, I'm not a style Nazi, and if you ever hired me to work on
your code, I'd follow your style guidelines.

(david)
 
Hi David,

If I can summarize what you said in a way I understood it:

1) Always use procedure level variables to declare objects to ensure "good
memory management."

2) There is neither a need to set the procedure level variable objects to
Nothing nor to Close them, because the VBA closes and destroys them for you
as soon as they go out of Scope.

3) All those extra lines of code that give us a lot of "warm, fuzzy feeling
inside" don't derive their Origin from VB, and hence redundant in VBA.

Did I get it right ?

(P.S: Neither am I a style Nazi, but believing what you said looked like
blasphemy to me in the beginning, but you seem to have a point or two to
learn. And, I am more than willing to learn new and better things and adopt.
:-) )
 
1) Always use procedure level variables.

2) There is neither a need to set the procedure level variable
objects to Nothing nor to Close them, because VBA closes
and destroys them for you as soon as they go out of Scope.

3) All those extra lines of code that give us a lot of "warm,
fuzzy feeling inside" don't derive their Origin from Access and
are redundant in Access.

******
3) I'd actually say 'Access' instead of 'VBA' or 'VB' because
in Access you wind up writing a lot of small procedures glued
together by a form object. It is possible to consider some other
VBA container where you might commonly wind up with scripts
that don't go out of scope, like in classic ASP.

2) Technically, objects are closed and destroyed after the
reference count goes to zero. If you stick to cohesive procedures
and procedure level variables, the distinction is not important.

1) I'd encourage people to use procedure level variables
for several reasons, not just memory management. In this case,
good memory management is just a side effect of good logic
management. If you get your program design right, the memory
management comes good by it's self, but memory management
is not the only reason for good program design.


(david)
 
Hi David,

Ok then, I will code like a real "Basic Programmer", but only after I test
my procedures thoroughly that everythings cleans up at run time.

As a sub-query, how can I test to see if my objects are closed and destroyed
for good ? Is it the Locals window or anything more advanced ?
 
Ok then, I will code like a real "Basic Programmer", but only
I test my procedures thoroughly that everythings cleans up at
run time.
Click to expand...

:~) No, that is the difference. In Basic you don't have explicit
control: You only give instructions to the virtual machine (In
Java called the virtual machine, in MS BASIC called the runtime,
in c# called the CLR, in VB6 called the runtime, but loaded
from a VM dll).

Yes, it does hurt to give up control. Yes, sometimes the VM
fails. But on the other hand, normally the VM is less likely to
fail than you are. And delegation is the price of higher productivity.

You can't debug the code that closes and destroys objects
in VBA. As we have discussed here,
CurrentDB.close
Set CurrentDB = nothing
does not have the expected effect of closing and releasing
CurrentDB. Even when you explicitly use close and nothing,
you are dependent on the hidden behaviour of the objects.

If you are using local variables, they are gone when leave
the procedure. There is no way to find the objects that they
once referred to. When using your own objects, it is possible,
without two much difficulty, to create reference loops which
defeat the VBA reference counting algorithm. We don't normally
see this problem with Application, DAO and ADO because
they are hierarchical classes.

Which is why we are reduced to using second-order effects
(does the application close? do you run out of memory?) and
coding standards (always use local variables) instead of spending
hours reading lint files and checking our code (what does that
pointer point to?)

(david)
 
david@epsomdotcomdotau said:
:~) No, that is the difference. In Basic you don't have explicit
control:
Click to expand...

That is what I was trying to gain in the first place - "Explicit Control"
which Dirk Goldgar termed as "obsessive-compulsive, not a bad trait for a
developer."
Yes, it does hurt to give up control. Yes, sometimes the VM
fails. But on the other hand, normally the VM is less likely to
fail than you are. And delegation is the price of higher productivity.
Click to expand...

Sometimes, I wonder why I didn't see any code that declares a string
variable setting it back to Null, an integer setting it back to Zero etc etc.
Why is the coding community after only the object variables ? Your point
about "delegation is the price of higher productivity" begins to make sense
here. But, yes - it does hurt to give up control and trust the runtime.
Which is why we are reduced to using second-order effects
(does the application close? do you run out of memory?) and
coding standards (always use local variables) instead of spending
hours reading lint files and checking our code (what does that
pointer point to?)
Click to expand...

Well, I think I'm beginning to see the big picture :-)
 
Back
Top