Kill a RecrodSet in VBA

[NarenMCA]

Hi Everyone,

I have a couple of dropdowns, based on the selection by user I must display values from many worksheets. I am using ADODB concept in VBA.

I am using the below code to get data from various sheets in a excel workbook.

Call rstNetAct.Open(strSQL, strConnStr, CursorTypeEnum.adOpenForwardOnly, LockTypeEnum.adLockReadOnly, CommandTypeEnum.adCmdText)

once the Output is shown, I am trying to kill this recordset as this occupies a lot of space in RAM.

I used the below code,

If (rstNetAct.State = ObjectStateEnum.adStateOpen) Then
rstNetAct.Close
End If
Set rstNetAct = Nothing

But it is useless. The RAM usage comes down only if I close the excel application.

(To avoid this I have used Pivot concept also, as I take data from different sheets that are in different formates. So, I must create 22 pivot tables and change selections in all of'em when the user selects a value in the dropdown. I takes lot of time to display the output.)


Is there is any way that I could flush out all the data(Data loaded by the query I used) from the RAM?

Please help.

Thanks in advance,
Naren

[QVeen72]

Hi,

Yes, Your Excel App takes the RAM,
Setting Recordset = Nothing, is enough to clear the RAM taken by recordset object..

Regards
Veena

[NarenMCA]

Hi Veena,

I am setting RecordSet = Nothing every time when the macro runs. If I open the task manager, it shows a huge memory space occupied. So when the user gets on to the next task, It again takes some more space in the RAM. So at a point of time the application stucks.

I guess "Nothing" will just clear the references to the recordset object but the querry o/p(Data) that is temporarily kept in RAM by the recordset is not getting flushed out unless I close Excel application.

[rpicilli]

Hi There,

Try to Dispose the object.

AdoCon.Dispose

Hope this help you

Rpicilli

[NarenMCA]

Hi Rpicilli,

It throws the below error.
"Method or Data Member not found."

Can you help me with more details that I must include to my code along with "RecSetName.Dispose".

Thanks,
Naren