CODEWITHSUNDEEP
vbaperformancems-access
Christian Geiselmann
Christian Geiselmann

Reputation: 622

VBA in MS Access: how to accelerate a loop operation on a recordset

In a database under my maintenance (MS Access 2010) I use a VBA procedure for cleaning up records, concretely: setting values in a field from "yes" to "no". The procedure loops through all records and sets the values in the respective field as required.

My database has about 900 records so far. Not too many, one should think.

My problem: the VBA procedure operates very slowly. On my current machine I have to wait for about 10 seconds until the loop has gone through the 900 records. That's impractical in everyday work.

What I need: I am looking for ways to speed this up, either through improvements to the code, or through a completely different approach.

Here is my procedure: 

Private Sub WipeSelectionMarks_Click()

'Remove any filter that might be set to reduce the number of records in
'the form - We want here to operate on all records in the database!

   Me.Filter = ""
   Me.FilterOn = False
    'Don't know if we really need two commands here; but at least it works 

'Operate on a recordset that holds all the records displayed in the form
   Dim rs As DAO.Recordset
   Set rs = Me.RecordsetClone
   rs.MoveFirst
   Do Until rs.EOF
      With rs
        .Edit
        !Int_IwSelectMove = False
        .Update
      End With
      rs.MoveNext
   Loop

'Message box with info what has been done 
   MsgBox "A total of " & rs.RecordCount & " records were updated by wiping the Move mark"

'Cleaning up
   Set rs = Nothing
   Me.Refresh

 End Sub

Note 1: if a solution would be using an SQL command instead, I will be grateful for practical hints or examples. I use SQL commands at many places, still, getting put on the right track here would be helpful.

Note 2: Or can the VBA procedure be rewritten in a way that only records where the field in question has value "yes" are processed (these are usually only 20-30 of the 900), and those with "no" are left out?

Upvotes: 2

Views: 714

Answers (2)

Gustav
Gustav

Reputation: 55841

can the VBA procedure be rewritten in a way that only records where the field in question has value "yes" are processed

Indeed, and that may very well be the fastest method, as you will not have to requery the form:

   With rs
       Do Until .EOF
           If !Int_IwSelectMove.Value = True Then
               .Edit
               !Int_IwSelectMove = False
               .Update
           End If
           .MoveNext
       Loop
       .Close
   End With

Or you could use FindFirst or a filtered recordset. Running SQL on the recordset of a form is usually the last option.

Upvotes: 2

mdialogo
mdialogo

Reputation: 473

You can use the UPDATE command:

CurrentDb.Execute "UPDATE YourTable SET Int_IwSelectMove = False"

Upvotes: 2

Related Questions