[AccessD] Memory leak

Fascinating stuff.  Just goes to show how badly VBA's "garbage collector"
sucks.  We are all told that when the last reference to an object closes,
the object is destroyed and the garbage collector will reclaim the memory.
We all also know that cleaning up behind ourselves (by specifically calling
close where one exists, and setting the pointer to nothing) often is a good
idea.  This just demonstrates that even that won't necessarily reclaim the
memory. 

Good info there.  Thanks.

John W. Colby
www.ColbyConsulting.com 


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Ken Ismert
Sent: Friday, April 14, 2006 7:31 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Memory leak


John, 

I tried various ways of fixing it. In the FWInit function, closing the
connection and setting it to nothing didn't help. In fact, you can
demonstrate the leak with just: 

    Function TestFWMemLeak_0()
        Dim rConn As ADODB.Connection
        While 1
            DoEvents
            Set rConn = CurrentProject.Connection
        Wend
    End Function

This code leaked very slowly (growing from 16,900 KB to 17,348 in about
2 hours):

    Function TestFWNoLeak_0()
        Dim rConn As ADODB.Connection
        While 1
            DoEvents
            Set rConn = New ADODB.Connection
            rConn.Open CurrentProject.BaseConnectionString
            FWInit rConn
            rConn.Close
            Set rConn = Nothing
        Wend
    End Function

But this code doesn't leak much at all: 

    Function TestFWMemLeak_1()
        Dim rConn As ADODB.Connection
        Set rConn = CurrentProject.Connection
        While 1
            DoEvents
            FWInit rConn
        Wend
    End Function

I let it run over lunch, and the memory usage went from 16,708 KB to 16,776.
Another test, for several hours, went from 19436 to 19496.

So, the moral of the story seems to be: set the CurrentProject.Connection to
a ADODB.Connection variable before you pass it to another subroutine. 

My guess is that CurrentProject.Connection is not a stable, long-term
object, in the sense of the DAO CurrentDB(). ADO connections are meant to be
temporary, and Access probably makes a new one from scratch every time you
refer to it. 
 
Help for "Connection Property" bears this out: 

Notes:  The Connection property actually returns a reference to a COPY of
the ActiveX Data Object (ADO) connection for the active database ...

This prevents you from closing or altering Access' own private connection,
but has the unfortunate side effect of being unable to close the copy, thus
the leak. 

-Ken


-----Original Message-----
From: John Colby [mailto:jwcolby at ColbyConsulting.com]
Sent: Thursday, April 13, 2006 9:14 PM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] Memory leak

I have just discovered that the following code causes a memory leak:

Function TestFWMemLeak()
    While 1
        DoEvents
        FWInit CurrentProject.Connection
    Wend
End Function
Public Function FWInit(lCodeProjConn As ADODB.Connection) End Function

Open task manager and keep it on top.  Find MSAccess and watch the memory
usage.  As you run TestFWMemLeak the memory usage climbs steadily.

When I google "memory leak ADODB.Connection" I get a ton of hits, though not
this specific issue.

John W. Colby
www.ColbyConsulting.com 



--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com