closing DB connections

[Steve]

I have an ADP in which I am making lots of connections within the VB code to
the SQL database and then closing the connections. In addiotn, I am
creating lots of recordsets and then setting the record sets to nothing when
I am done.

Is there and easy way to determine if the code is leaving connections open
or retaining orphan recordsets?

Specifically, whenever I am using code such as:
set conn = ado_connect
I should be ending the procedure with
set conn = nothing

Additionally, for everytime I setup a recordset such as
set rs = conn.execute("SELECT Agent from stbl_Agents")
There should be the following code when the procedure is done:
set rs = nothing

Am I correct in these assumptions? If so, Is there a good way to test the
code to find orphan connections?

NOTE: It appears that after a while, my machin is running out of memory and
I am thinking it might be trying to keep connections open and retain orphan
recordsets.

Help?

 

[Sylvain Lafontaine]

Personally, I always take the precaution of making an explicit close:

conn.close
set conn = nothing
....
rs.close
set rs = nothing.

Also, in the case of the recordset (and maybe also for the connection), it's
probably better to check for its status first:

if (rs.state <> 0) then rs.close
set rs = nothing

or even better:

if ((rs.state & adStateOpen) = adStateOpen) then rs.close
set rs = nothing

(The value of adStateOpen = H00000001). Don't know if this will help you
solve your problem.

 

[dragos]

If you open/close connections/recordsets many times may be it is better
to write a generic procedure/function to handle all of them. Release
resources (but pay attention for nesting) at this generic procedure.
Also if you want an 'endurance' test for Access 'garbage collector'
open some (growing)connections/recordsets (without closing them) and
watch your system resources. After that close them programmatically and
monitor also your resources. You will be surprised of what you'll see,
but as Microsoft says: "This behaviour is by design "

PS: I don't know a way to detect orphaned recordsets/connections from
the outside of the calling procedure, but the golden rule is to always
first close (this frees up system resources) and second set the object
variable to nothing (to completely eliminate an object from memory)

Cheers!
Dragos

 

[aaron.kempf]

I am sorry dude.

but you're a ****ing retard; and you're simply confusing ADO and DAO.

ADO and ADP doesn't have this problem.

DAO and MDB DOES have this problem

One of the reasons I use ADP

-Aaron

 

[onedaywhen]

Personally, I always take the precaution of making an explicit close:

conn.close
set conn = nothing
...
rs.close
set rs = nothing.

Sometimes there are valid reasons for tearing down an object, rather
than trusting the garbage collector. One such circumstance, as arraon
points out, is when you use DAO because its model has bugs, the result
of which is that the user must explicitly tear down objects *and* in
the correct order (it has been suggested that this one bad object model
has given VBA coders the erroneous idea that the VBA garbage collector
is faulty - once bitten twice shy or something). ADO does not have
these bugs but a valid reason for tearing down an ADO connection is to
release server resources where connection pooling is used.

There are some valid reasons for not explicitly releasing or tearing
down object variables *every* time. By doing so you are saying you do
not trust the garbage collector and is there any reason not to trust
the garbage collector? The memory leaks I've seen have been the result
of bad programming (often by me <g>) e.g. the DAO problem lies with
DAO, not the VBA garbage collector.

In most cases, you will be releasing objects just before the garbage
collector will do so itself; even when you do, the garbage collector
still has to verify they have been released so the step is not only
unnecessary but involves more work. You could say that doing it
yourself does no harm but from a code maintenance perspective it
creates 'noise' that obscures those special cases where explicit tear
down is of value.

I don't want to give anyone sleepless nights <g> but consider this code
snippet:

Dim rs1 As Object
Set rs1 = CreateObject("ADODB.Recordset")
With rs1
.Fields.Append "Test", 200, 12
.Open
.AddNew "Test", "Test"
End With
Dim rs2
Set rs2 = rs1
rs1.Close

What is the reference count on the recordset object? The answer is
three: rs1, rs2 and the reference created when you used With. I know
how to explicitly destroy the first two e.g. by setting them to
Nothing. But what about the third? Should I trust the garbage collector
to clean it up? Of course *I* do, but then I'd trust the garbage
collector to do the business on all three.

But can anyone of the 'destroy what I create' persuasion tell me what
they do about the third reference? As I say, don't lie awake at night,
just trust the garbage collector.

Jamie.

--

 

[Robert Morley]

But rs1 is already defined & reference-counted...why would using With count
it again? I'm not saying it doesn't, but I've never heard/read about With
doing that. Even assuming you're right and it does, wouldn't the End With
destroy the reference?



Rob

 

[aaron.kempf]

you're full of crap I think

ADO you don't need to explicitly close ANYTHING.

If I wanted to de-allocate pointers I would dress up in PINK and write
C++

-Aaron

 

[Sylvain Lafontaine]

It's a question of time: the garbage collector will always finish to clear
up the object; however, this could happen a lot of time later. With things
that are constraining for Windows (and as such, are often polled), you can
end up with a lot with a lot of resources that belongs to objects that have
become out of context but have not yet been released by the GC.

In the case of resources that are polled by Windows, you can end up in a
situation where your code cease to run because it is waiting for some timer
in the GC to kick up and release these resources so that they can be used
again. For these kinds of resources, MS has always suggested that these
resources to be explicited closed as soon as possible; without relying on
the automatic release mecanism of the GC. This is particularly important in
the case of ASP and ASP.NET pages, where the IIS must do a lot of
requests/pages per second and doesn't have the luxury to wait for the GC to
release these resources before serving new pages but can (should) also apply
in the case of VBA everytime you are in the situation of grabbing many
resources in a short period of time.

It will never hurt to release these resources yourself; however, if you
always rely on the GC to release them, your code might be in a situation
where it will have to create un-needed new resources (or wait for them to be
returned to the pool) because a lot of objects with lock to resources will
be have gone out of scope but still be waiting to be cleaned by the GC.

 

[decland]

But rs1 is already defined & reference-counted...why would using With count
it again? I'm not saying it doesn't, but I've never heard/read about With
doing that. Even assuming you're right and it does, wouldn't the End With
destroy the reference?

You are correct about End With, of course; I fudged the code! Try this
revision:

Sub Test()
Dim rs1 As Object
Set rs1 = CreateObject("ADODB.Recordset")
With rs1
.Fields.Append "Test", 200, 12
.Open
.AddNew "Test", "Test"

Dim rs2
Set rs2 = rs1
Exit Sub

End With
rs1.Close
End Sub

My point was supposed to be: at the Exit Sub line the reference count
will be three and *within* the With block you cannot release the third
reference.

Jamie.

--

 

[onedaywhen]

In the case of resources that are polled by Windows <<snipped>>

We are in agreement that there are circumstances where explicitly
releasing object variables is a good thing.

What I am in disagreement with is the commonly held notion that doing
so is *always* a good thing. I note you have not explicitly said this
but your earlier code example, where you set your object variables to
Nothing, implied this. Apologies if I misrepresented your views.

There are people who always release object variables, even when they
are just about to go out of scope e.g. immediately before the End
Sub/End Function. However, the same people do not always release their
String variables early; Strings are reference/pointer types too and can
occupy a lot a memory, so the same criteria should apply, no? And if
the concern is efficient resources, why do we not see the String * N
(fixed width text, being an intrinsic value type rather than a
reference type) being used more?

It will never hurt to release these resources yourself

I suspect you use the term 'hurt' subjectively. Explicitly releasing an
object variable just as it is about to go out of scope duplicates the
work that VB does: it still has the check the variable has been
deallocated and will still (internally) generate the code to do so. Is
duplication 'harmful'? It certainly creates 'noise' and I suspect gives
coders the impression that it *must* be done. Is unnecessary (to the
point of being potentially misleading) code 'harmful'?

Jamie.

--

 

[onedaywhen]

ADO you don't need to explicitly close ANYTHING.

See:

http://msdn2.microsoft.com/en-us/library/ms810829.aspx

Pooling in the Microsoft Data Access Components:
Troubleshooting MDAC Pooling:

"Common causes of connection creep include: The ADO Connection and
Recordset objects are not actually closed. If you don't explicitly
close them, they won't be released into the pool. This is probably the
single most frequent cause of connection creep."

Jamie.

--

 

[aaron.kempf]

I disagree with this and I haven't seen it in the real world.

I _HAVE_ seen this crap with MDB and DAO all the ****ing time

you notice the word SHOULD here

http://www.4guysfromrolla.com/webtech/060999-2.shtml

If we don't explicitly inform the server that we're done with these
allocated resources, ASP should do it for us. Putting all of our faith
into ASP is a bit of a risk. It's much safer and much more reliable to
explicitly close and clean up the instances of our recordset and
connection objects.

there is a big difference between 'asp should do it for us' and 'DAO
doesn't ever do it correctly'

basically.. a little bit of a memory leak isn't that big of a deal and
from where I am standing there is no ROI on making this change; and
thus becoming a slower developer-- because I've never had a problem
with ADO.

if you do the same thing in Access _ONCE_ with DAO you are going to get
a hang

I don't agree with this crap; I don't need to explicitly close
anything.

If I wanted to deallocate pointers; I would shove a big fat pink C++
dildo up my ass

and I am outraged that you dipshits take your bad experiences with DAO
and transfer them to ADO and VB6.

ADO and VB6 is more stable than anything else I've found.

I don't have this explicit closing problem; and I claim that it is
merely FUD that is spread in order to get us to move to .NET.

if they claim that there is a major pressing issue where we all must
move to dotnet; but it's an issue I haven't seen in almost 10 years of
using ADO?

then it must be a scam and you dipshits are just scarred by DAO and you
don't know the ****ing difference between ADO and DAO.

ADO doesn't have this problem.

END OF STORY.

-Aaron

 

[Robert Morley]

Hehehe...I was brought up on Pascal. I *never* use an Exit Sub within a
With block, or similar construct, and there's only ever one Exit Sub (if
any) in each procedure. I don't even use Exit For and the like; I've never
found a need to do so.

But to your point, I suspect that with something this relatively
straight-forward, the garbage collector would have no problems cleaning it
up. Where it becomes a problem is when you have parent/child objects where
the parent has a child collection, and the children have a parent object.
Since the objects have a mutual relationship, de-referencing the objects
themselves doesn't properly decrement the reference counter...you have to
manually set the Parent property to Nothing for all child objects (or
similar coding, depending on how you've set things up, exactly). This is
one instance where I know the GC will fail, there are probably others.

This also leads into your point about why you would clean things up yourself
when the GC can do it for you (with the exception of the above). As you've
pointed out, one reason not to wait for the GC is to conserve resources be
de-allocating earlier on in your code, or to choose the order and/or timing
of de-referencing if that's important to you for whatever reason, or due to
simple distrust of automated processes...some people like to ensure that
things ARE cleaned up properly, rather than trusting that the GC will figure
it all out properly. Also, if you have a bug in your tear-down code (i.e.,
object destruction/termination/whatever) and the GC is handling it, it may
be more difficult to debug, or may not respond as you'd want it to in the
event of an error (such as logging the error to a file/table).

You're right that many people don't think of cleaning up strings, but I can
see at least some argument for that, in that strings are "simple"
objects--there's no tear-down for them beyond de-allocating the memory that
they've used. Often for more complex objects, there is. That said, if
you've got an object that allocates only a few bytes of memory and has no
tear-down vs. a string that's tens or hundreds of kilobytes long, and you're
concerned about resources, then obviously it's to your benefit to worry
about the string long before the object.


Rob

 

[aaron.kempf]

you're a ****ing MORON


DAO is the only library where you must explicitly close everything

 

[aaron.kempf]

and for the record? you should stop listening to your retard computer
science professor-- this isn't black magic.

Yes; I only use one Exit For also.. but I'll use GOTO until they pry it
out of my cold dead hands

-Aaron

 

[Sylvain Lafontaine]

You're right, I also set the object to nothing and maybe it's an useless
thing to do. However, what exactly happens when you set or not set an
active-x object created by VBA to nothing (after its closing, when
applicable) has never been really documented or explicited by MS.

I have seen a lot of code exemples from MS where these references are set to
nothing after their closing; so I prefer to err on the safe side. It's quite
possible that this is useless; however, the OP and my answer was more about
the explicit call to the Close operation than about setting them to nothing
before the reference goes out of scope.

 

[Sylvain Lafontaine]

About your exemple with Parent and child collections, GC are immune to this
kind of mutual relationship; this is why they are used. You can find a good
explanation about this process in the following two-part article:

http://msdn.microsoft.com/msdnmag/issues/1100/GCI/default.aspx
http://msdn.microsoft.com/msdnmag/issues/1200/GCI2/

Of course, this is about the GC in the .NET framework (notice that some of
the mecanisms and functions explained in this article are for NET 1.0 and
are no longer valid for 1.1 and 2.0) and cannot tell us so much about the
one used in VBA; however, I suspect that many conclusions that we can draw
from it will apply to VBA, too.

 

[Robert Morley]

Yes, the GC in .Net is immune to this, since it's not based on reference
counting. The garbage collection in VB6/VBA is much more primitive and is
subject to the circular reference problem, since it IS based on reference
counting. See: http://support.microsoft.com/kb/189156/en-us and a similar
problem in this article: http://support.microsoft.com/kb/190519/en-us.



Rob

 

[Sylvain Lafontaine]

Yeah, I remember now. I see that I'm now in the process of fast forgetting
everything that I knew about Visual Basic; probably to make some place for
the new technologies. Good riddance!

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Yes, the GC in .Net is immune to this, since it's not based on reference
counting. The garbage collection in VB6/VBA is much more primitive and is
subject to the circular reference problem, since it IS based on reference
counting. See: http://support.microsoft.com/kb/189156/en-us and a
similar problem in this article:
http://support.microsoft.com/kb/190519/en-us.



Rob

 

[onedaywhen]

I disagree with this and I haven't seen it in the real world.

basically.. a little bit of a memory leak isn't that big of a deal and
from where I am standing there is no ROI on making this change; and
thus becoming a slower developer-- because I've never had a problem
with ADO.

if you do the same thing in Access _ONCE_ with DAO you are going to get
a hang

I don't agree with this [...]; I don't need to explicitly close
anything.

As regards *closing*, I know from experience that letting a ADODB
Connection go out of scope will close the connection without problem
when using SQLNCLI, SQLOLEDB or Microsoft.Jet.OLEDB.4.0 but I don't
know whether this behaviour is ADO- or provider-specific.

As regards setting to Nothing, I am already in the habit of doing this
only when necessary. Consequently, I this code that frequently catches
my eye:

con.Close
Set con = Nothing ' release possible connection pooling

I suspect I had a problem once with connection pooling and the
'workaround' was to set the connection to Nothing, being something I
would not normally do. The aforementioned article seems to confirm
this.

In other words, there are exceptions to every rule and - don't take
this personally, it's not your fault - you sometimes have to do things
'manually'. Yes, you have to do this _all the time_ in DAO and in my
experience only very infrequently in ADO.

Other than that, you seem to have mistaken me for someone who uses DAO
but I accept your apology in advance ;-)

I am outraged that you [others] take your bad experiences with DAO
and transfer them to ADO and VB6.

Amen.

Jamie.

--