CODEWITHSUNDEEP
ms-accessvbaado
jwoolard
jwoolard

Reputation: 6284

ADO Batch Update in Access VBA

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?

Upvotes: 1

Views: 9522

Answers (3)

Tony Toews
Tony Toews

Reputation: 7882

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

Upvotes: 1

onedaywhen
onedaywhen

Reputation: 57023

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.

Upvotes: 1

shahkalpesh
shahkalpesh

Reputation: 33474

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.

Upvotes: 1

Related Questions