ADO Batch Update in Access VBA

Question

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

"Run-time error '-2147217887 (80040e21)': Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done."

My code is

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

    rowsDel = rowsDel + 1
Loop
rs.UpdateBatch

Any ideas what the issue is?

Answer 1

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.

Answer 2

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.

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

CurrentProject.Connection.Execute strSQL, lngRecordsAffected, adCmdText

Answer 3

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
  .Open

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

  .UpdateBatch

  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.