[AccessD] Memory leak

>  Question: what version of Access are you testing this in, John? If you
like, I'll try this on A2003 to see if this got fixed.

2K, Xp and 2003.


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: Monday, April 17, 2006 3:42 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Memory leak


John,

>> ... Just goes to show how badly VBA's "garbage collector" sucks ... 

My working theory lays more of the blame on Access, and maybe ADO:

Theory 1: John's Original Observation
-------------------------------------
CurrentProject.Connection has a "don't try suicide" feature: All
CurrentProject.Connection.Close and Set CurrentProject.Connection = Nothing
requests are ignored. My guess is that CurrentProject.Connection inherits
from ADODB.Connection, but overrides the Close method with nothing, making
the project connection uncloseable. 
  The rub comes when a programmer requests a copy of
CurrentProject.Connection in code: my guess is Access returns the overridden
CurrentProject.Connection type, not the ADODB.Connection parent type. That
makes any copy of CurrentProject.Connection uncloseable, thus the leak John
observed. This is just a bonehead implementation mistake on MS's part. 
  Question: what version of Access are you testing this in, John? If you
like, I'll try this on A2003 to see if this got fixed.

Theory 2: My Slow Leak, 'Proper ADO' Code
-----------------------------------------
My intended fix to the problem above was to not use
CurrentProject.Connection, but derive my connection using
CurrentProject.BaseConnectionString and a fully closable ADODB.Connection
object. Now, you would really expect this to work with no leaks, but there
is a slow leak here, too. 
  This is more worrisome, because it is closer to the 'pure' ADO code you
would write in VB6. The best case is that
CurrentProject.BaseConnectionString duplicates itself on every reference,
and doesn't garbage collect. The worst case is that there is some subtle
problem in the VBA/ADO/Provider chain itself. 
  I will test this on A2003, and see if I get the same behavior. 

In any case, this is one small illustration of why Shamil is so cautious
about thoroughly testing the ADO Disconnected Recordset/Access 2003 idea
before recommending it as a stable development technique. There must be a
running bet within MS on whether the Access team can make it work properly
with ADO before it becomes completely obsolete ;)

John's best practice recommendations are still best practice, but keep an
eye on that memory! 

If you or anyone else has any observations or corrections, please jump in. 

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