StrikeEagleII
Technical User
I have a form that uses the following code to open an ADO recordset to SQL Server:
When I use the .Update method I was getting the error "Row cannot be located for updating. Some values may have been changed" even though the recordset definitely had the primary key (and the table had a timestamp).
Turns out that even though I had specified adLockOptimistic, when ADO was sending the call to SQL Server it was including the timestamp field along with the primary key and since the record had been updated, the timestamp field had too.
The solution was this (after binding the above recordset to the form, i.e Set Me.Recordset = rst):
This apparently tells ADO to use only the primary key values and ignore the timestamp.
I guess my question is is what's the point of specifying "adLockOptimistic" vs. "adLockPessimistic" if you still have to include the ".Properties("Update Criteria").Value = adCriteriaKey" to get it to ignore the timestamp field?
Code:
rst.Open strSQL, gCon, adOpenKeyset, adLockOptimistic
When I use the .Update method I was getting the error "Row cannot be located for updating. Some values may have been changed" even though the recordset definitely had the primary key (and the table had a timestamp).
Turns out that even though I had specified adLockOptimistic, when ADO was sending the call to SQL Server it was including the timestamp field along with the primary key and since the record had been updated, the timestamp field had too.
The solution was this (after binding the above recordset to the form, i.e Set Me.Recordset = rst):
Code:
Me.Recordset.Properties("Update Criteria").Value = adCriteriaKey
This apparently tells ADO to use only the primary key values and ignore the timestamp.
I guess my question is is what's the point of specifying "adLockOptimistic" vs. "adLockPessimistic" if you still have to include the ".Properties("Update Criteria").Value = adCriteriaKey" to get it to ignore the timestamp field?