Re: filter with vb code

From: Jamie Collins (jamiecollins_at_xsmail.com)
Date: 03/14/05 

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
from an
> Excel worksheet that is open in Excel, a memory leak occurs in the
Excel
> process.
>
> Workaround by MS:
> "Use the SELECT INTO syntax of the Jet OLE DB Provider to export the
Excel
> 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
leak.

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:

  SELECT *
  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
leaking.

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:

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q319998

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 ...?

Jamie. 

--

Relevant Pages

  • Re: Tips on finding memory leaks
    ... But when Access reports that it's using 500M of memory, ... The problem is that at least with Access as the front-end, and ADO in the ... mentioned collections and child objects along with ADO. ... what a Recordset is: "A collection of collections contains objects". ...
    (microsoft.public.vb.general.discussion)
  • Re: Any REAL reason to use ADO vs. DAO?
    ... I've run this test and I've _NEVER_ seen DAO faster than ADO ... has little to do with marshalling a recordset object across processes. ... Here both, ADO and DAO, don't allocate much Memory (no Copy ...
    (microsoft.public.vb.general.discussion)
  • Re: running out of memory
    ... First of all it is not ADO, ... using of the SQL statements on a server side inside of SP to do this rather ... > I've found that looping through an ado recordset will cause memory ... > if it's a large recordset. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Tips on finding memory leaks
    ... Any suggestions, either specific to ADO, collections, or just general to ... As for tracing memory leaks with VB, ... mentioned collections and child objects along with ADO. ... Recordset which can then be wrapped with an object to provide the same ...
    (microsoft.public.vb.general.discussion)
  • ADO memory leak version 2.82.1830.0
    ... ADO seems to memory leak in msdart.dll. ... that last two days every 1 minute execute sql statement and "dumps" recordset after that. ...
    (microsoft.public.data.ado)