Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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... |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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. -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Input boxes in excel and MS Query | Excel Discussion (Misc queries) | |||
"Query cannot be edited by the Query Wizard" | Excel Discussion (Misc queries) | |||
Excel2000 ODBC query oddity | Excel Discussion (Misc queries) | |||
Query of External Data | Excel Discussion (Misc queries) | |||
How to use a Access Query that as a parameter into Excel database query | Excel Discussion (Misc queries) |