Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


ADO Recordset : delete records doesn't release memory

Posted on 1999-08-03
Medium Priority
Last Modified: 2013-12-25

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
Question by:moosach
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Expert Comment

ID: 1507440
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>


Expert Comment

ID: 1507441
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.


Accepted Solution

JaganMohan earned 300 total points
ID: 1507442
Close the recordset

Set dup = Nothing

This will clear all the resources from the memory

Author Comment

ID: 1507443
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.



Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

704 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question