First of all, even though this has nothing to do with the problem, the CursorType adOpenKeyset is not used for a client side cursor and gets changed to a Static cursor. So, to the causes of the problem.
The 4 main causes for this is that there are one or two other causes for this, but by looking at your sql statement, you should not be affected by these: If you have a Unique field included in the table AND recordset, then this problem is avoided. If not, all fields are used and if there is no uniquness between records, you will get this error. With JET mdb, you need to include this unique field with the recordset.
It gets then included as a hidden field. This is optimistic locking. If another user has made changes to the record since you have opened the recordset, then you are not "allowed" to change the record you need to be notified that a change has occured and then decide how to proceed - this is the second meaning of the error message. True optimistic locking when another user has maked changes to ANY field a certain record, sinced you have opened your recordset, then you are locked out from making a change as well.
The ADO default optimistic locking is "Column locking". This is where a change made by a second user to a record since you have opened a recordset including that same record, is prevented ONLY when that second user has made a change to the SAME column s you are trying to change.
This means that Optimistic locking will not happen if a second user changes a different field in the same record that the field you are changing. How does the Optimistic locking work? When ADO performs Optimistic locking, it checks the rs. If they are different, then the lock takes place. Then, the criteria used for this update not only includes the primary key, BUT ALSO includes the field s being updated and there original values in order to find the record and perform an optimistic lock: OriginalValue If this db field was changed by some other action another recordset update, action query, either by the same connection or another user then the value of the db field does not match the value stored in the recordset for the field's OriginalValue, and the record cannot be located for updating There are different types of "Optimistic locking" or let's rather say that htere are different ways to handle the locking - even to turning off optimistic locking under ADO.
Column criteria -ADO default: Only lock the record if a change was made to the same field s through another user, or query update, etc -. Turn off Optimistic locking completely. You implement these types by using the dynamic recordset property "Update Criteria", if supported. The settings for the above are: First, a word about 2: The adCriteriaTimeStamp is the ideal setting which cause True optimistic locking.
For SQL Server, or some other database which supports timestamps, only the primary key and timestamp fields are checked for a change. Because when you THINK you are updating a field of a certain single record, you may cause a change in that field for ALL records, as it may not be certain which record you are requesting a change to similar to the same action caused by an bulk UPDATE query - which in this case, it is probabaly desired more often to all, or groups of records meeting a certain crteria.
JET is not capable of doing this. You can see these hidden fields by inspecting the dynamic "Hidden Columns" recordset property - again, only if supported. Are you working with disconnected recordsets, or executing updates via Action queries and then trying to update the same record through a recordset using the UpdateBatch method?
If not 1, 2 or 3, then either handle the error as optimistic locking inform the user that a change has been made by another user since they have viewed the record and ask if they want to continue or see the changes made before proceeding 5. Or, "turn off" the optimistic locking by setting rs.