Error: Row cannot be located for updating. Some values may have been changed since it was last read. On changing provider from SQLOLEDB to MSOLEDBSQL

Question

On change of provider from SQLOLEDB to MSOLEDBSQL in the ADODB connection string, we get the error:

-2147217864 Row cannot be located for updating. Some values may have been changed since it was last read.

The connection string is:

Provider=MSOLEDBSQL;SERVER=servername;APP=Applicationname;DATABASE=databasename;WSID=id;Trusted_Connection=yes;MARS Connection=True;DataTypeCompatibility=80

And the code looks like:

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient 
rs.Open("SELECT * FROM tableName WHERE 1 = 2", Adoconnection, adOpenStatic, adLockBatchOptimistic, CommandTypeEnum.adCmdText) 
rs.AddNew
'Add the fields
...
...
rs.UpdateBatch ''this line throws error

Now, when in the connection string of provider is changed to SQLOLEDB, with the same code it works great without any issue.

Answer 1

I found my way to this thread because I had a similar error. I am using Access 365 VBA 32-bit on Windows 10. Here's a snippet of my code (some details omitted):

Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
cmd.ActiveConnection = GetCurrentConnection()
cmd.CommandText = sql

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open cmd, , adOpenDynamic, adLockOptimistic

With rs
    .Fields("FailedImport") = True
    .Fields("ImportErrors") = ReadErrorsFromResponse(xmlDoc)
    .Update
End With

I also encountered the same error as in the OP. In my case, I didn't get the error until I included the update to the boolean field called FailedImport. I found my way to this forum post from 2008, containing this comment (formatting mine):

Had same error, however had it with recordsets rather than data control. From another forum, I learnt that if you are using a static cursor for the recordset, using adLockBatchOptimistic instead of adLockOptimistic solved the problem.

So, I changed this line: rs.Open cmd, , adOpenDynamic, adLockOptimistic

to this: rs.Open cmd, , adOpenDynamic, adLockBatchOptimistic

And my code worked again! Hope this helps someone else in the future!

Answer 2

Try adding a timestamp, or so called "rowversion" column to the table. (use type timestamp - which has ZERO to do with time).

Also, if you have any bit columns in that table, then make sure they are not null, and make sure a default value of (0) is set for that bit column.

And if the application has linked tables, then re-link your tables after you made the above change server side.

Answer 3

I found out the issue, it was in the SQL triggers.

The respective table had some update statements on the trigger. Adding SET NOCOUNT ON just before the update statement in the trigger helped me to avoid this error.