Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Difference between ADO recordset "update criteria" and "lock type

Status
Not open for further replies.

StrikeEagleII

Technical User
Oct 4, 2006
57
US
I have a form that uses the following code to open an ADO recordset to SQL Server:

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?
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top