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

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
>

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

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
>
>

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
>
>

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
>

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

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
>