[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