ADO Recordset : delete records doesn't release memory


can somebody explain me this behaviour ?

Any help is appreciated



'' ADODB/ADOR Recordset : deleted records doesn't release
'' virtuell memory
''   1) Start program
''   2) Open TaskManager and note virtuell memory
''   3) Add some 1000 records; virtuell memory increases by
''      about 270 bytes per record
''   4) Delete some 1000 records; viruell memory
''      consumption does not change (at least not within
''      half an hour)
''   5) Add 1000 records again; virtuell memory increases
''      again
''   6) Copy/Replace Recordset; virtuell memory is released

Option Explicit

Dim theRs As Recordset

Private Sub Form_Load()
    Set theRs = New Recordset
    With theRs
        .CursorLocation = adUseClient
        .CursorType = adOpenStatic
        .LockType = adLockOptimistic
        ' define recordset
        .Fields.Append "Number", adInteger, 4
        .Fields.Append "Name", adVarChar, 255
        ' open recordset
        ' add initial record
    End With
End Sub

Private Sub cmdAdd_Click()
    ' add 1000 records
    Dim i As Long
    For i = 1 To 1000
End Sub

Private Sub cmdDel_Click()
    ' delete 1000 records (oldest first)
    Dim i As Long
    For i = 1 To 1000
End Sub

Private Sub cmdCopy_Click()
    ' make a fulcopy of the recordset and release the old one
    Set theRs = DupRecordset(theRs)
End Sub

Public Function DupRecordset(rs As Recordset) As Recordset
' returns a fulcopy of rs
    Dim f As Field
    Dim dup As Recordset
    Set dup = New Recordset
    With dup
        .CursorLocation = rs.CursorLocation
        .CursorType = rs.CursorType
        .LockType = rs.LockType
        For Each f In rs.Fields
            .Fields.Append f.Name, f.Type, f.DefinedSize, f.Attributes
            .Fields(f.Name).Precision = f.Precision
        Next f
        While Not rs.EOF
            For Each f In rs.Fields
                .Fields(f.Name).Value = f.Value
            Next f
    End With
    Set DupRecordset = dup
End Function

Private Sub disableUI()
    cmdAdd.Enabled = False
    cmdDel.Enabled = False
    cmdCopy.Enabled = False
End Sub

Private Sub updateUI()
    Caption = "RecordCount = " & theRs.RecordCount
    cmdAdd.Enabled = True
    If theRs.RecordCount > 1000 Then
        cmdDel.Enabled = True
        cmdDel.Enabled = False
    End If
    cmdCopy.Enabled = True
End Sub
Who is Participating?
JaganMohanConnect With a Mentor Commented:
Close the recordset

Set dup = Nothing

This will clear all the resources from the memory
Deleting a record does not automaticle clean the record from the recordset.
Try to do a rs.Requery, or rs.close and rs.Open again. This should release the memory.
The deleted records are not really removed, they are just pegged for deletion.  This saves speed. At the cost of memory obviously... <g>

Have you tried using a server-side cursor?

I don't know enough about your application, but if you're not using features of the Microsoft Client Cursor Provider that are not supported using a server-side cursor (such as disassociated recordsets), you may want to try switching.

Data values are still transmitted over the network (as with client-side cursors), but the impact on local workstation memory and disk space is reduced.

If it's a multi-user application, then I'd definitely switch.  They allow for additional sensitivity to changes others make to the data source.

I have found other problems using a client-side cursor (updating tables with a large number of fields).  I haven't tested it with your situation, but it's worth a try.

moosachAuthor Commented:
Hello again,

thanks  for your efforts.

In my real application the recordset is build by code, as it is in the sample program.
The recordset is used as a client-side buffer, which fills to some limit over time.
When the buffer is full, then older records are deleted, when new one are added.
The records are only removed from the buffer, but not from the database.
I use ADO.Recordset here because of it's sorting and filtering features.

I don't like to do a database Requery or Close/Open of the recordset at this point, because the query is very costly and would bring additional load to the server.

Your answers tell me:  I can't avoid, that removed records still use memory as long as the recordset is open.
So I think, I have to copy the recordset from time to time, like in the sample program.

If someone knows a better solution, you are welcome.


Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.