I've got a problem with maintaining consistency in my database. I'm using Access 2000 as my database and VB6 with ADO recordsets. I have an issue with Multi user access and record updates.
I use server side cursors with pessimistic locking and keyset recordsets. The senario is, I have a set of records that one user may be editing at the time. To another user this reports that the record is locked which is OK. I'm guessing however that if the record is recently updated by the other user, then the next user will have access to the record and update it with his updates. This is not what I want to happen. At the moment I have a field in the record which identifies the status of that record. If this field says "Available" Then the user is able to update it. If it doesn't say this then the user should not be able to update it. Because records do not reflect the current status of the record till the data is requeried, the user is able to update the record even though another user has previously made it not available.
Any ideas on how to keep the data more consistent. I think that if the record is locked, I need to cancel any pending update another user may have. Requery the data to get the current record status and deny an edit if it does not read available.
I've got to protect the record once the status has been changed even though the user has a pending edit.