John Colby
jwcolby at ColbyConsulting.com
Fri Apr 14 21:04:06 CDT 2006
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