[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