1

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.

June7
  • 19,874
  • 8
  • 24
  • 34
Shah
  • 31
  • 1
  • 5
  • Then why use MSOLEDBSQL? – June7 Dec 08 '21 at 09:12
  • As Microsoft says "SQLOLEDB is not maintained anymore and it is not recommended to use this driver for new development." and MSOLEDBSQL, provides security updates, datatype compatibility, mars features, and many others – Shah Dec 08 '21 at 09:49

3 Answers3

1

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.

Shah
  • 31
  • 1
  • 5
  • Thanks! This was the case for me. Had the same error as in the question. But the cause was the trigger and not the provider for the driver. – Simorgh May 12 '23 at 12:12
0

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.

Albert D. Kallal
  • 42,205
  • 3
  • 34
  • 51
  • We already have timestamp or so called "rowversion" column :) and none of the bit column have null value, all have default value. – Shah Dec 08 '21 at 15:22
  • If this is being called from a form, then right before the above code do a me.Dirty = false and then run your code. – Albert D. Kallal Dec 09 '21 at 19:24
0

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!

Pflugs
  • 772
  • 1
  • 10
  • 18