Take the 2-minute tour ×

I am trying to use batch updates in Access VBA. My code works fine when I update after every operation, but fails when I try to operate on multiple rows. The exception I get is


My code is

rs.Open "dbrammDump", CurrentProject.Connection, _
            adOpenKeyset, adLockBatchOptimistic
rowsDel = 0
Do Until rs.RecordCount < 1

    rowsDel = rowsDel + 1

Any ideas what the issue is?

share|improve this question

3 Answers 3

up vote 1 down vote accepted

I think the issue is that you need to explicitly use a client-side cursor. I suspect that you are implicitly using a server-side cursor.

I prefer to set the recordset object's properties individually because I think it is easier to read (and therefore debug) than using the overloaded Open method. Also, you can use the RecordCount property for your loop e.g.

With rs
  .ActiveConnection = CurrentProject.Connection
  .Source = "dbrammDump"
  .CursorLocation = adUseClient  ' <<< THIS MISSING FROM ORIGINAL CODE
  .CursorType = adOpenKeyset
  .LockType = adLockBatchOptimistic

  Dim counter As Long
  For counter = 0 To .RecordCount - 1


  rowsDel = counter

End With

FWIW I agree with others here that a set-based solution in SQL is preferable to procedural code such as that above.

share|improve this answer
You know, I don't think that's going to do a batch update unless you disconnect the recordset first. After .Open, you need to do Set .ActiveConnection = Nothing, then before the .UpdateBatch, do Set .ActiveConnection = CurrentProject.Connection. –  ErikE Jan 20 '11 at 17:38
Hmm... I can't seem to get disconnected recordsets working right so far. So perhaps my comment was premature. –  ErikE Jan 20 '11 at 17:47
Aha! I was not using adLockBatchOptimistic. adLockOptimistic prevents batch updates from working. –  ErikE Jan 20 '11 at 18:22
@Emtucifor: batch updates should work regardless of whether connected or disconnected provided your use adLockBatchOptimistic (and that the OLE DB provider and/or source supports it). –  onedaywhen Jan 21 '11 at 9:42
Yeah, it was the lock type I was missing. Sorry about the misinformation. Now that I'm onto the issue about lock type, I see that you don't have to disconnect the recordset to prevent the updates from occurring as you make the changes. –  ErikE Jan 21 '11 at 18:03

Why are you deleting records in this manner, when you could call myconnection.Execute "DELETE FROM myTable WHERE....."?

Also, how have you opened the recordset? Paste the myrecordset.Open(...) statement here.
See if this link helps.

share|improve this answer
True, that would be better for this case (thanks :-). However, I am also inserting a few thousand rows - and would like to do this batched rather than individually. –  jwoolard Jun 25 '09 at 10:08
Is the source of your inserts a data set that can be used in a SELECT statement to be appended as a batch? If so, that's your answer. –  David-W-Fenton Jun 25 '09 at 18:43


If you need the number of rows deleted/inserted/updated with ADO code use the following.

CurrentProject.Connection.Execute strSQL, lngRecordsAffected, adCmdText
share|improve this answer

Your Answer


