Memory leak query
 

I'm using Excel 2002 and querying excel files using ADO. I have read on the
microsoft site that there is a memory leak bug when you query open files, but
could not find any fixes for the problem. (Other than their suggestions of
effectively forming a duplicate copy of the data). Has anyone come up with a
sensible work around for the bug, or found an upgrade (maybe Excel 2003??)
that would help me?

Thanks.

Blue Aardvark wrote:
I'm using Excel 2002 and querying excel files using ADO. I have read on the
microsoft site that there is a memory leak bug when you query open files, but
could not find any fixes for the problem. (Other than their suggestions of
effectively forming a duplicate copy of the data). Has anyone come up with a
sensible work around for the bug, or found an upgrade (maybe Excel 2003??)
that would help me?

There is currently no fix. And I don't think there ever will be an
upgrade fix: MS Jet is a depreciated component, this bird has flown.
Even alleged fix 'Method 1' in Q319998 is not really a fix because it
creates a memory leak i.e. that which we're trying to avoid.

Operating on a copy of the workbook is the only workaround. I don't
think I'll be using SQL code with Excel in future...

Hi Jamie,

Thanks for the info. I was led to believe that ADO was microsofts new
wondertoy, but it seems to have some definite issues. I would still like to
use sql to retrieve data from an open excel file as it would save me a lot of
hard coding vba. Do you know any other tricks? Perhaps DAO instead? Otherwise
I may be stuck repeatedly using the saveCopyAs function until my hard disk
burns out.

Steve

Blue Aardvark wrote:
Thanks for the info. I was led to believe that ADO was microsofts new
wondertoy

Indeed, if you mean circa 1999 <g. ADO 'classic' remains a great set
of components from the COM era. I use fabricated ADO recordsets to hold
in-memory data much more than I use Collections or Dictionary objects.

but it seems to have some definite issues

The memory leak bug is the only sigificant *ADO* issue, IMO. There are
other 'Excel Jet' issues, notably data typing, but these may be
designed around very effectively.

I would still like to
use sql to retrieve data from an open excel file as it would save me a lot of
hard coding vba.
I may be stuck repeatedly using the saveCopyAs function until my hard disk
burns out.

Personally, I don't think saving a copy is too much of a problem.
Remember you only need to save the source worksheet(s) to a new
workbook and close it.

Do you know any other tricks?

The alternatives may or may not cause memory leaks and other problems.
IMO querying an open workbook is not a good solution under any
circumstances. And, as I hinted earlier, I'm coming back to the idea
that if data is work querying with SQL then it's worth keeping in a SQL
database (Access/Jet, SQL Server/MSDE, non-MS products are also
available <g).

Jamie.

--