Multi User DB Access using ADO

[J Staniforth]

As is often the case there are several ways to Skin a cat in programming - what I would like to know is what is the best/preferred way of providing Multi-user VB prog with Access97 using ADO (because want to easily upgrade to SQL server if need to). I.e. if user edits record which another edited between them viewing and editing then handle it by asking which update to use ( "Original field value=200, other user set it to 230, you set it to 250 - which do you want?"). I think optimistic locking would be best.
Thanks in advance.
Regards,
John.

[Clunietp]

General rule of thumb: Use pessimistic locking if you expect contention for a relatively small number of records and you want to prevent other users editing the same record at the same time.

The performance is slower than optimistic locking, but sometimes you gotta do what you gotta do.....

[J Staniforth]

Still, how do I take account of the position where someone else changed a value and when other goes to record they see old value - change it and inconsistency - this happens regardless of where optimistic or pessimistic.
Thanks.

[Clunietp]

If you are using a static or forward only cursor, I suppose you can use the Resync method before a user edits the record to make sure it has not changed

If you are using a keyset/dynamic cursor with pessimistic record locking, nobody but the person currently editing the record should be able to edit it....

[J Staniforth]

I hear what your saying but when the second user accesses the record after the first has done the editing they require the 2nd doesn't see the updates of the 1st. Ive looked at the properties of the ADO query/DED but there is no sign of a record change even in the underlyingvalue or originalvalue properties.
I'm doing something wrong!

[Clunietp]

What kind of cursor are you using? (dynamic, keyset, static or forward only?)

[J Staniforth]

Keyset (read that Access wouldnt allow something important using Dynamic).

[Clunietp]

Got me. It should work, assuming you are issuing an edit and then an update command.....

anyone else?