ADO Batch Update in Access VBA -

I'm trying to use batch update in VBA. When I update after every operation, my code works fine, but when I try to work on multiple lines fails I get exceptions

"run -Time '- + 2147217887 (80040e21)' Error: .. Multiple steps OLE DB generates operational errors, then available, check each OLE DB status value There was no work done. "

my code is

  rs.Open "dbrammDump", CurrentProject.Connection, _ adOpenKeyset, adLockBatc HOptimistic rowsDel = 0 Do rs.RecordCount up to & lt; 1 rs.MoveFirst rs.Delete rowsDel = rowsDel + 1 loop rs.UpdateBatch  

Any ideas what's the issue?

I think the problem is that you need to explicitly use a client-side cursor I think you are using a server-side cursor indirectly.

I personally like to set the properties of the recordset object because I think that (and therefore debug) is easy to read, by using overloaded to open Method Apart from this, you can use the RecordCount property for your loop eg.

  with Rs .ActiveConnection = CurrentProject.Connection.Source = "dbrammDump" .CursorLocation = adUseClient '& lt; & Lt; & Lt; This is missing from the original code. CursorType = adOpenKeyset .LockType = adLockBatchOptimistic .Open to the counter counter counter = 0 .RecordCount as long as - 1 .Delete .MoveNext Next .UpdateBatch with rowsDel = counter  End 

FWID I agree with others that a set-based solution in SQL is better for procedural code as above.