Re: filter with vb code
From: Jamie Collins (jamiecollins_at_xsmail.com)
Date: 14 Mar 2005 02:18:40 -0800
Nate Oliver wrote:
> Where's the ADO Recordset in my post?
> Quote by MS:
> "When you retrieve a Microsoft ActiveX Data Objects (ADO) Recordset
> Excel worksheet that is open in Excel, a memory leak occurs in the
> Workaround by MS:
> "Use the SELECT INTO syntax of the Jet OLE DB Provider to export the
> data to a new worksheet.
Nate, It's not your fault but what can I do? I've tried giving
Microsoft 'feedback' about this article but they have so far taken no
notice; I guess ADO classic doesn't interest them. So my only recourse
is to spread the word myself and jump on anyone in the ngs who repeats
MS's bad advice.
There may be no recordset in your post be there certainly is a memory
I've done extensive testing using the very same performance monitoring
code found in the article in question and I think I know the truth. The
key to the issue is not the recordset, not the connection, not the
provider or driver. It's as simple as this: using ADO to execute Jet
SQL involving an open workbook causes a memory leak.
Let's be fair: did you test your assertion or did you just put your
faith in Microsoft? I did a test just now. My connection was to a .mdb
MS Access/Jet database i.e. not even an Excel connection. I executed
the following SELECT..INTO..FROM which does not return a row set and I
certainly did not use a recordset:
INTO [Excel 8.0;Database=C:\db1.xls;].Sheet1
FROM [Excel 8.0;Database=C:\db.xls;].[Sheet1$];
With the source workbook open I got a memory leak. When it was closed,
I experienced no leak.
I'm not sure how I can prove to you that the leak occurs other than to
say, try it for yourself. If you don't have VB6 or other requirements
of the code, just open Windows Task Manager, switch to the Performance
tab and keep you eye on the Mem usage: if the value increases but does
not return to something very close its original value then you're
If any of the regulars who use ADO (you know who you are <g>) and have
VB6.0 are willing to test and post their experience/results here, I
would be grateful. Here is the link to the article and code:
I really wish I was wrong on this one: it would be nice to be able to
safely operate on an open workbook. There must be someone out there
itching to publicly show me up ...?