Locking Headache

[Stevie]

Sorry but this is going to be a long one.

(Unbound controls)
In DAO to update a recordset you would Edit, alter the fields then Update.

In ADO to update a recordset you would alter the fields then Update.

Hence DAO knows when you are in Edit mode before you try to update, whereas it would seem that ADO does not.

(Bound controls)
When ever a controls contents are altered it would be in Edit mode. (ADO & DAO) Update would occur when a save button is pressed for instance.

As far as I can tell (in short laymans terms) pessimistic locking occurs when trying to edit a record, and optimistic locking occurs when trying to update a record.

Now I need to know if this is correct, surely when using unbound controls with ADO, pessimistic and optimistic locking would appear to work in exactly the same way to the user, as it would not know when it is in edit mode, and the update could be instantaneous.

If this is so is there any way for me to lock records when a user tries to edit a field, apart from me implementing my own locks based on timestamps or something?

Any help/ideas/input welcome

[paulw]

Nope. The locking applied to the record (whether Pessimistic or Optimistic) applies all the way through from the initiation of the Edit, until the Update is posted. The locking is determined by the cursor type.

The Edit in ADO is implicit. You can examine whether a record is currently in an Edit state in ADO.

e.g. an ADO Recordset has an EditMode property which can be: adEditNone (no current editing); adEditInProgress (data has been edited but not saved) or adEditAdd (a record has been added but is unsaved). There are also object events such as WillChangeRecord etc.

Try the Hitchhiker's Guide to VB and SQL Server by William Vaughan, it is excellent.

Cheers,

Paul.

[Stevie]

I checked the EditMode property just before saving alterations, and also just before adding a new record, and on both occasions it = 0 (adEditNone).

As this indicates it does not know it is in edit mode, how can pessimistic locking be accomplished?

[paulw]

You definitely implement the locking from the cursor. the edit state and the locking strategy are not the same thing. Locking only comes into play in multi-user editing, not what you are doing.

How are you checking the EditState because if you move away from the edited field it will post an implicit Update and the edit state will become adEditNone. Check the events that are occurring with your recordset. To enable the Event handlers, declare your connection: Dim WithEvents cn As Connection etc.

Cheers,

Paul.

[Stevie]

I'm writing a multi-user system using VB and Access.

I just checked the edit state from two different PC's at the same time and both came back as adEditNone.

Now as both pc's do not seem to be in an edit state, how can pessimistic locking occur, as it can not lock records when trying to edit if it does not know it is in edit state.

Am I explaining myself clearly or have I got hold of the wrong end of the stick?