Memory Leak With ADO kb=319998

[RB Smissaert]

Other than saving the workbook and closing it and running the query on the
closed workbook, you could move the sheet data to a text file(s) and query
that or
you could transfer to real database like for example SQLite and query that.
No idea why this hasn't been fixed yet.

RBS

 

[billbell52]

Excel has a very useful feature to allow you to query spreadsheets
using ADO. I use this feature in Excel VBA for an addin I support.
It saves me a lot of effort to get data from the spreadsheet. Not
using it would mean more or less writing an SQL processor in VBA. Not
something I want to do. The memory leak does cause a problem but I
tell users to kill Excel when it gets too slow. With some of the
larger spreadsheets I am seeing now they have to kill Excel after an
hour or less of use.

What I don't understand is this bug has been in Excel since Excel
'97. Why on earth have they not fixed it? Please tell me they fixed
it in Office 2007.

Is there any better workaround to this other than the asinine one they
propose in the article.

From MS Website

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. Repeated queries may eventually cause Excel to run
out of memory and raise an error, or cause Excel to stop responding.

http://support.microsoft.com/kb/319998