Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Huge Memory Leaks using ODBC Drivers from Excel to retrieve data
Hi,
We're using the ODBC Text driver, and the ODBC Excel driver to retrieve data in several procedures in VBA. In each case the data is retrieved into ADO Recordsets, then copied to different worksheets using the Excel CopyFromRecordset, then the ADO Recordset (and it's connection) are explicitly closed and destroyed within the same procedure. Every data import leads to a memory leak. For example, in step 1, first a source text file is opened as an ODBC table from the LAN into an ADO Recordset with a WHERE clause restricting the date of acceptable records. The matched data is put in one worksheet, then that recordsegt is closed, and another procedure runs to import the exceptions (where the date field does not match). Finally the workbook is saved, then the ODBC Excel driver is used to read Aggregate data from the 1st worksheet into a 3rd sheet. This step (really 3 procedures) takes the memory used by Excel (in the Task Manager) from 33 MB to 48 MB - and yet only a few records are actually imported (like 10 !) There are several other steps like this, all of which do essentially the same tasks, either importing matching data from textfiles and manipulating it record by record, or reading from worksheets using UNION SQL Queries (which include formulae !) to perform complex data manipulations... Of course, I know that something so complex should be in a compiled application, but we had no time to build an app, only a macro (usual story!) And we had to use SQL Queries because of the complex nature of the data manipulations...for example, we had to import data, then return 2 rows into another sheet from 1 record - which means a UNION SQL Query. Can anyone suggest a way of nailing down the memory leaks please, or how I could ensure that memory is released... is there an API method I could use to force Excel to release the memory? thanks for any help or assistance! I know its a big one :) Philip |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Huge Memory Leaks using ODBC Drivers from Excel to retrieve data
Ensure that you set all of your objects to nothing (sounds like you know
enough to do that but just to be thurough). You can try saving the spreadsheet. Ensure that when you are pasting the cells that somehow you are not creating empty cells (Excel is not always perfect at cleaning up after itself). You will know this if your scroll bars allow you to scroll well past the end of the data. Delete the empty cells and the save to clean up the memory. Finaly here is a good resource: http://www.decisionmodels.com/memlimits.htm -- HTH... Jim Thomlinson "Philip" wrote: Hi, We're using the ODBC Text driver, and the ODBC Excel driver to retrieve data in several procedures in VBA. In each case the data is retrieved into ADO Recordsets, then copied to different worksheets using the Excel CopyFromRecordset, then the ADO Recordset (and it's connection) are explicitly closed and destroyed within the same procedure. Every data import leads to a memory leak. For example, in step 1, first a source text file is opened as an ODBC table from the LAN into an ADO Recordset with a WHERE clause restricting the date of acceptable records. The matched data is put in one worksheet, then that recordsegt is closed, and another procedure runs to import the exceptions (where the date field does not match). Finally the workbook is saved, then the ODBC Excel driver is used to read Aggregate data from the 1st worksheet into a 3rd sheet. This step (really 3 procedures) takes the memory used by Excel (in the Task Manager) from 33 MB to 48 MB - and yet only a few records are actually imported (like 10 !) There are several other steps like this, all of which do essentially the same tasks, either importing matching data from textfiles and manipulating it record by record, or reading from worksheets using UNION SQL Queries (which include formulae !) to perform complex data manipulations... Of course, I know that something so complex should be in a compiled application, but we had no time to build an app, only a macro (usual story!) And we had to use SQL Queries because of the complex nature of the data manipulations...for example, we had to import data, then return 2 rows into another sheet from 1 record - which means a UNION SQL Query. Can anyone suggest a way of nailing down the memory leaks please, or how I could ensure that memory is released... is there an API method I could use to force Excel to release the memory? thanks for any help or assistance! I know its a big one :) Philip |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Huge Memory Leaks using ODBC Drivers from Excel to retrieve data
Philip,
XL remebers the used range of a query, and will not release it automagically. The only way to do this is delete the rows that contained the data, Read the rowounct of the used range(this hack forces a reset of the usedrange of a sheet), and then save the worksheet. Then the memory is released. DM Unseen |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Huge Memory Leaks using ODBC Drivers from Excel to retrieve da
Hi,
thanks. Please would you help me out with that? Do you mean at the end of each procedure (that dumps data in the worksheet) I should use code like this: dim iRows as integer irows=xlSheet.UsedRange.rows.count and then save the workbook? thanks for clarifying... Philip "DM Unseen" wrote: Philip, XL remebers the used range of a query, and will not release it automagically. The only way to do this is delete the rows that contained the data, Read the rowounct of the used range(this hack forces a reset of the usedrange of a sheet), and then save the worksheet. Then the memory is released. DM Unseen |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Huge Memory Leaks using ODBC Drivers from Excel to retrieve da
Hi
I tried this in the Immediate window on a worksheet (called RealizedGL) ?realizedgl.UsedRange.Rows.Count then saved it. The before & after value was the same... Am I doing something wrong? thanks for your help Philip "DM Unseen" wrote: Philip, XL remebers the used range of a query, and will not release it automagically. The only way to do this is delete the rows that contained the data, Read the rowounct of the used range(this hack forces a reset of the usedrange of a sheet), and then save the worksheet. Then the memory is released. DM Unseen |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Huge Memory Leaks using ODBC Drivers from Excel to retrieve data
I also found this KB Article
http://support.microsoft.com/default...b;en-us;319998 BUG: Memory leak occurs when you query an open Excel worksheet by using ActiveX Data Objects (ADO) which shows there's definitely a problem. .... still trying to use DM Unseen's suggestion about querying the UsedRange rowcount. thanks if anyone can help Philip "Philip" wrote: Hi, We're using the ODBC Text driver, and the ODBC Excel driver to retrieve data in several procedures in VBA. In each case the data is retrieved into ADO Recordsets, then copied to different worksheets using the Excel CopyFromRecordset, then the ADO Recordset (and it's connection) are explicitly closed and destroyed within the same procedure. Every data import leads to a memory leak. For example, in step 1, first a source text file is opened as an ODBC table from the LAN into an ADO Recordset with a WHERE clause restricting the date of acceptable records. The matched data is put in one worksheet, then that recordsegt is closed, and another procedure runs to import the exceptions (where the date field does not match). Finally the workbook is saved, then the ODBC Excel driver is used to read Aggregate data from the 1st worksheet into a 3rd sheet. This step (really 3 procedures) takes the memory used by Excel (in the Task Manager) from 33 MB to 48 MB - and yet only a few records are actually imported (like 10 !) There are several other steps like this, all of which do essentially the same tasks, either importing matching data from textfiles and manipulating it record by record, or reading from worksheets using UNION SQL Queries (which include formulae !) to perform complex data manipulations... Of course, I know that something so complex should be in a compiled application, but we had no time to build an app, only a macro (usual story!) And we had to use SQL Queries because of the complex nature of the data manipulations...for example, we had to import data, then return 2 rows into another sheet from 1 record - which means a UNION SQL Query. Can anyone suggest a way of nailing down the memory leaks please, or how I could ensure that memory is released... is there an API method I could use to force Excel to release the memory? thanks for any help or assistance! I know its a big one :) Philip |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Huge Memory Leaks using ODBC Drivers from Excel to retrieve data
I Use the following code in the thisworkbook. This will resolve memory
issue on the worksheet although I Use an XL querytable with SQL and refresh it to load a query and not through an ADO recordset(I almost never use). Public WithEvents qtable As QueryTable Private Sub qTable_AfterRefresh(ByVal Success As Boolean) Dim x As Integer x = qtable.Parent.UsedRange.Rows.Count End Sub Private Sub qTable_BeforeRefresh(Cancel As Boolean) With qtable.ResultRange If .Rows.Count 1 Then .Offset(1, 0).Resize(.Rows.Count - 1, ..Columns.Count).Delete (xlShiftUp) End With End Sub Private Sub Workbook_Open() Set Me.qtable = Details.QueryTables(1) Application.EnableEvents = True End Sub Note that for updating you should use SQL statements instead of traversing the ADO recordset: i.e.use an UPDATE statement. The best way to do this is to load your textfiles into a database (e.g. Access) and run XL reports based on that. DM Unseen |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Huge Memory Leaks using ODBC Drivers from Excel to retrieve data
Thanks guys,
In fact I changed the code for all the imports as suggested in the KB Article http://support.microsoft.com/default...b;en-us;319998 BUG: Memory leak occurs when you query an open Excel worksheet by using ActiveX Data Objects (ADO) and that cleared all the memory leaks....I basically use SaveCopyAs then open the copy to read the data into the recordset! I prefer not to delete unused rows, as there is currently conditional formatting ... I suppose the best option (time permitting) is to copy down the formatting ... then I can delete the unused rows... anyway, thanks all! Philip "Philip" wrote: Hi, We're using the ODBC Text driver, and the ODBC Excel driver to retrieve data in several procedures in VBA. In each case the data is retrieved into ADO Recordsets, then copied to different worksheets using the Excel CopyFromRecordset, then the ADO Recordset (and it's connection) are explicitly closed and destroyed within the same procedure. Every data import leads to a memory leak. For example, in step 1, first a source text file is opened as an ODBC table from the LAN into an ADO Recordset with a WHERE clause restricting the date of acceptable records. The matched data is put in one worksheet, then that recordsegt is closed, and another procedure runs to import the exceptions (where the date field does not match). Finally the workbook is saved, then the ODBC Excel driver is used to read Aggregate data from the 1st worksheet into a 3rd sheet. This step (really 3 procedures) takes the memory used by Excel (in the Task Manager) from 33 MB to 48 MB - and yet only a few records are actually imported (like 10 !) There are several other steps like this, all of which do essentially the same tasks, either importing matching data from textfiles and manipulating it record by record, or reading from worksheets using UNION SQL Queries (which include formulae !) to perform complex data manipulations... Of course, I know that something so complex should be in a compiled application, but we had no time to build an app, only a macro (usual story!) And we had to use SQL Queries because of the complex nature of the data manipulations...for example, we had to import data, then return 2 rows into another sheet from 1 record - which means a UNION SQL Query. Can anyone suggest a way of nailing down the memory leaks please, or how I could ensure that memory is released... is there an API method I could use to force Excel to release the memory? thanks for any help or assistance! I know its a big one :) Philip |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel ODBC query tables memory overflow | Excel Programming | |||
ODBC Drivers to a SQL database | Excel Discussion (Misc queries) | |||
How to retrieve original huge data from pivottable? | Excel Discussion (Misc queries) | |||
documented memory leaks in Excel 2003 or OWC11? | Excel Programming |