VBA - ADODB Recordset memory leak issues

mrhopko

Board Regular
Joined
Jan 31, 2012
Messages
68
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:

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

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Is the source data workbook open or closed when you create the recordset? You should have it closed.
 
Upvote 0

Forum statistics

Threads
1,203,435
Messages
6,055,357
Members
444,781
Latest member
rishivar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top