VBA - ADODB Recordset memory leak issues

[mrhopko]

Hi all

I really hope someone can help me because I am stuck. I am using 32-bit Excel 2010 on a 64-bit Windows 8 i7 8GB machine. This means I only have <2GB of ram for Excel to play with.

I have a workbook that imports data from another workbook using the ADODB.Recordset/Connection model. This works well for me as the workbook I am extracting data from has >500K rows and requires grouping that could not be done in a simple Pivot Table.

I'll call the workbook I am using to extract and transform the data my ETL workbook. I will call the workbook I am pulling data from my Data Workbook.

The problem is as follows:
When I open my ETL workbook, Windows tells me Excel is using 170MB of RAM. Fair enough.
As my VBA script runs through the recordset script, Windows tells me that my RAM usage is about 1GB, not good but nothing dies.
When Excel has finished generating the recordset, Windows tells me that my RAM usage is now 350MB - quite manageable
When the script finishes and i close the recordset, Windows tells me that my RAM usage is now 300MB.
If I then save and shut the workbook, so only the Excel shell is open, Windows tells me my RAM usage is 130MB
If I save, close the workbook down completely and re-open it, windows goes back to 170MB

The only explanation I have for this is that Excel is creating some sort of object in memory as it creates the recordset that is not being killed off completely when the script ends.
I would like Excel to return to its state of 170MB with the workbook still open. Can anyone help?

Script I am using to create the recordset:

Sub CreateRecordSetFromDataSmallGrain(lsCurrentFileName As String)

Dim lsConnectionString As String
Dim lsSQLString As String
Dim lsSheetName As String


'exit sub if no file chosen
    If lsCurrentFileName = vbNullString Then Exit Sub


'check that file is an excel file
    If InStr(1, lsCurrentFileName, ".xls") > 0 Then


'create the query to get the data from the sheet
        lsSQLString = ADOSQLPriorMonthAdj
        
'create the connection string
        lsConnectionString = ADOConnectionStringODBC(lsCurrentFileName)


        Set grsdataSmallGrain = New ADODB.Recordset
'Set cursor location to client side
        grsdataSmallGrain.CursorLocation = adUseClient


'Set cursor type to static
        grsdataSmallGrain.CursorType = adOpenStatic


'Set the lock type to batch optimistic
        grsdataSmallGrain.LockType = adLockBatchOptimistic


'open the recordset
        grsdataSmallGrain.Open lsSQLString, lsConnectionString, , , adCmdText


'close connection
        grsdataSmallGrain.ActiveConnection = Nothing
        
    End If


grsdataSmallGrain.Close
Set grsdataSmallGrain = Nothing


End Sub

 

[Firefly2012]

Is the source data workbook open or closed when you create the recordset? You should have it closed.