memory issue using ADO to query Excel
Hi Jamie,
Are you saying you cannot (easily) recreate the situation using only
northwind?
Yes. Making a query to Northwind with only one field for only 20 times
produces 0.001% of what I am doing. If you really want to test make the
following:
What I'm seeking is evidence
that not doing so actually causes problems.
- create a new mdb, link all tables of Northwind (link some dbf-tables,
too).
- run queries on the new mdb only
- make sure you get thousands of records in your recordset and many (what
about 250?) fields. Use a complicated sql-statement, eg. join 2 tables, use
conditions (and group by...)
- do not pause the macro to avoid what you call timing problems (you don't
want to wait 1000x3sec, do you?)
- loop through the query maybe 1000 times.
If failing to clean up was the sole cause of the problem
then you should be able to recreate it using a single data source.
Just to be clear, are you saying that
Sub Test()
' Just let the open connection go out of scope
End Sub
causes the Excel.exe to grow and slow down queries, whereas:
Sub Test()
Con.ActiveConnection = Nothing
Set Con = Nothing
End Sub
does not?
Correct. In a quick test using a foxpro table I started with excel.exe with
18MB. At the end I had with cleaning 25MB, without 118MB of RAM (30 queries,
a very simple one, 525 records as result).
I run queries vs. not saved excel-files
A not saved Excel file can only mean an open worksheet and the title
I fill my workbook with data from the mdb-file. Then I run queries on my
workbook.
BUG: Memory Leak Occurs When You Query an Open Excel Worksheet Using
ADO
I know this one. It uses Worksheets as databases, this is one of the reasons
why I use _named_ranges_ as databases. I do not have a problem with excel
files.
In the end, I do not know why my excel was so terribly big and slow
(sql-statements, odbc drivers, lots of data in the worksheets, joining
different sources (mdb and foxpro)...) but cleaning up and using one
connection for many queries solved everything.
arno
|