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 !

--
Denis Béland





--
Denis Béland

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

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

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.


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

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" wrote:

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 !

--
Denis Béland





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





*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

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

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



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

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
"Denis Béland" wrote in message
...
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 :)



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!