ADO Recordset using Excel as a database. Can't free the memory once opperation is over?

[Denis Béland]

Hello.

Thanks for reading, hopefully one of you will have a suggestion.

I have 3 sheets of data in an Excel workbook. Those data where prevouisly
imported from an SQL database.

I need to perform many filtering opperation on those datas to fill different
tables with the numbers of rows left after the
filter is applied.

After creating a connection object and a recordset, i pass the recordset to
a funtion. Here is a portion of this funtion.

With rstX
If .State = adStateClosed Then
.Source = strSql -----------------> exeample "SELECT *
FROM [Data1$] Where RefCat IN (2,5,10,15)"
.Open , , adOpenStatic, adLockReadOnly
End If
End With
For i = 1 To 3
rstX.Filter = "Date>='" & CLng(Fonctions.Cells((5 + i),
3).Value) & "' And Date<='" & CLng(Fonctions.Cells((5 + i), 4).Value) & "'"
tabRange.Cells(j, (i * 2)).Value = rstX.RecordCount
Next i
rstX.Close

The funtion retuns an integer indicating if the opperation was successfull.

After all the filter are over the calling sub puts the recordset object and
the ado connection to nothing.

I call this function over 40 in the same event ( click of a button )

My problem comes with the memory usage. For some reason excel seems to store
the result set of all the Select .
When i'm done excel use 110 megs of memory and if i start the opperation
again it will build up more memory until the available memory is full.

It wont free that memory until i close Excel itself, closing the workbook
does not do it.

Thank you !

 

[Andi Mayer]

Hello.

Thanks for reading, hopefully one of you will have a suggestion.

I have 3 sheets of data in an Excel workbook. Those data where prevouisly
imported from an SQL database.

I would use a total different aproach:

import direct from the SQL database

or if you have to use the Excel.

Dim ary() As Variant
Dim objExcel As Object
Dim aRange As Object
Dim MyRows as long, MyCols as Long

Set objExcel = CreateObject("Excel.Application")
objExcel.Application.Visible = False 'True
objExcel.workbooks.Open "C:\MyExcel.xls")

With objExcel.Application.activeWorkBook
Set aRange = .activesheet.usedRange
ary = aRange
Set aRange = Nothing
.Close False
End With
objExcel.Quit
Set objExcel = Nothing

myRows= UBound(ary)
myCols = UBound(ary, 2)

Now I can use this array to filter the data and write the tables