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 !

 

[Sharad]

Hello Dennis, Try this:

Just after rstX.Close add ' Set rstX = Nothing '
Setting to nothing sholud clear the memory.

Ofcourse between the multiple calls you have to Set it again to valid
RecordSet, and I guess, this must be there already in your code for each
call.

Sharad

 

[Guest]

Have you tried closing the connection? Not just the recordset (which is what
rstX appears to be) but the connection itself.

Alternatively, have you considered using just XL-native tools? Check out
XL's filter capability (Data | Filter > Advanced filter...) And, yes, it can
be automated.

 

[Denis Béland]

Thanks for the suggestion Sharad.

In fact that was the way i did it the first time.

The fucntion would create a new recordset and put it to nothing at the
end. I then noticed the memory problem.

I decided to create the recordset only one time for all the calls to the
fuction. Hoping that since only one recordset was created it would clear
all te rows every time i change the source property (freing the memory)
with the new sql Query.

Here is the code structure.

Private Sub cmdLoadTables_Click()
On Error GoTo Denis
Dim intErrNumber As Integer
Dim adoCnn As ADODB.Connection
Dim rstX As ADODB.Recordset

'function that return a connection on the workbook
Set adoCnn = objSql.GetCnnExcel(wrkBx.FullName)
Set rstX = New ADODB.Recordset
rstX.ActiveConnection = adoCnn

intErrNumber = FillTableau(rstX, Tableaux.Range("B7", "H9"),
Tableaux.Range("J5", "N9"), Data1.CodeName)

'3 other calls to FillTableau not shown here

Set rstX = Nothing
Set adoCnn = Nothing

End Sub

Private Function FillTableau(ByRef rstX As ADODB.Recordset, tabRange As
Range, filtreRange As Range, strData As String) As Integer
On Error GoTo Denis
Dim strSql As String
Dim strWhere As String
Dim i As Integer
Dim j As Integer

For j = 1 To tabRange.Rows.Count
'Build the where condition acording to the value enter by the
user
strWhere = BatirWhere(filtreRange, (j + 2))
'if the where is empty no data on this row
If strWhere <> "" Then
strSql = "Select * from [" & strData & "$] " & strWhere
With rstX
If .State = adStateClosed Then
.Source = strSql
.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
Else
If tabRange.Cells(j, (i * 2)).Formula = "" Then
tabRange.Cells(j, (i * 2)).Value = 0
End If
Next j
FillTableau = 0
Denis:
FillTableau = Err.Number
Err.Clear
End Function

It did not work.
Dosnt mather if i create the recordset only one time and put it to
nothing at the end of the process or if i create it and put it to
nothing at each iteration. The amount of memory used by XL pass form 15
megs to 110 megs.

And like i posted on the firt time. The only way the memory will free is
if i close XL, closing the workbook wont free it.

The only thing i could see is that there is a property in
XL that can be set to prevent it form keeping tracks of those request.

OR there is a bug with OLEDB with XL.
There is no reason for the memory load up this way.

 

[Denis Béland]

Yes i did just that as a first solution.

But the code was getting long and difficult to maintain and to update.

This is true when you have to use more that 3 column to filter and when
you have to combine OR and AND condition in the same filter.

By doing this ADO solution i was hoping to reduce the leght of the code
and simplify it for future maintnance.

The code is much lither and easier to follow, and works just fine but
only for the memory problem!

Thanks

 

[Guest]

Hi Denis

Denis Biland" wrote:

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

Have you propertly this connection closed ??

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.

If this is an open wookbook see

http://support.microsoft.com/?kbid=319998


Good Luck
TK

 

[Denis Béland]

Thank a lot TK
This was exactly my problem (open workbook) and the link you provided
gave me the solution to this bug.

Have a good one

 

[Sharad Naik]

Hi Dennis,

Would you care to inform which version of excel you used? The Article
provided by TK, says applies up to Excel 2000. I am just wondering whether
it affects excel 2003 also, hence this qustion.
(I am about to make a database project in excel 2003).

Thanks

Sharad

 

[Guest]

Hi Dennis,

Would you care to inform which version of excel you used? The Article
provided by TK, says applies up to Excel 2000. I am just wondering whether
it affects excel 2003 also, hence this qustion.
(I am about to make a database project in excel 2003).

Thanks

Sharad

Hi Sharad:

I didn't read that quite the same.

STATUS
Microsoft has confirmed that this is a bug in the Microsoft
products that are listed at the beginning of this article.

APPLIES TO
• Microsoft Excel 2000 Standard Edition
• Microsoft OLE DB Provider for Jet 4.0
• Microsoft Excel 97 Standard Edition
• Microsoft Excel 2002 Standard Edition


Good Luck
TK

 

[Denis]

Hi Sharad.

I am using Exel 2002. Maybe it is corrected with 2003

In the article i used the second suggestion that is to do a .SaveCopyAs
and then i connect to that saved copy.

I did not chage anything in the code other that add one line before
opening the connection.

wrkBx.SaveCopyAs Application.Path & "\Data.xls"


Denis

 

[onedaywhen]

Maybe it is corrected with 2003

Take a look at the components involved.

The data source is the Excel workbook. The MS product in question is
the Office suite. The latest release version is Excel2003. Development
for Office is on going. From what the NDAs (I'm not one of them) have
let on we can expect an enhanced future version of MS Access (but don't
hold out much hope for your Excel wish-list <g>).

The SQL engine is Jet. This most recent version is Jet 4.0,
approximately contemporary with Office2000 but is not directly linked
with Office development. Jet development has been discontinued; Jet no
longer ships with MDAC (since MDAC 2.5). The Jet development team has
long been disbanded. Jet 4.0 service packs have continued to be
released (to 2004) to address security issues. We are told a Jet team
has been formed for the version of MS Access currently under
development but not the extent of their remit. I would guess they are
working on further security enhancements only.

The link between the data and the engine is the OLE DB provider. It is
also part of Jet. The most recent Jet provider is the Jet 4.0 version.

The memory leak is caused by a bug in the OLE DB provider. I do not
expect this to ever be fixed. It certainly wasn't fixed for the release
of Office2003. A fix for the next MS Office release is possible but
extremely unlikely: MS Access's new Jet team are unlikely to prioritize
fixing a bug which only causes problems when the data source is Excel.
Jamie.

--