Drop Changes

[Luciano]

Hi, I really don't know if my message passed a few hours ago, that's the reason I repeat my question.
I have a splitted and bound multi-user front-end back-end Access database. In the options in my back-end database, I choosed 'Default record locking: Edited record'. In my Front-end, I'm working as much as possible with ADO-Recordsets to update my tables. I uselly open adOpenDynamic and adLockOptimistic. It happens, when two front-end users are editing in a form a field in the same record, that the 'slowest' user get the message 'Save Record/Copy to Clipboard/Drop Changes'. In my appllication, this user MUST choose 'Drop Changes' (something like .Undo and Cancel in BeforeUpdate). Sometimes however, the user choose 'Save Record' (with dramatic consequenses). Is it possible to force the user to make the good choose (Drop Changes), or to suppress this message by another (something like msgbox "Update didn't work out, because of an other user")?

[PATRICK]

Luciano, are you working with unbound fields into your form? looks like not... it could be a good option to update record where you can update certain record using ID number and SQL Update statement.

[June7]

What is it about your business process that multiple users would need to edit the same record at the same time? This has happened maybe twice in the 6 years my db has been deployed. Our process makes that occurrence extremely rare.

[Luciano]

It's true that it's very rare that multiple users are editing the same record; but it happens quit frequently that the code behind this updated records is updating the same record in another tabel.

[June7]

Code is updating a record that a user is also trying to edit? That is so unlikely in my db I don't even worry about it. Again, your business processes seem to be the real issue and/or the db design.

Access was not intended to be a multi-user database. It was designed for personal use. It does function for multiple users but doesn't handle well the circumstances you describe. It's my understanding that databases like Oracle or SQLServer are better suited.

[Luciano]

Indead, when 2 (or more) users are updating different records in table1, it is necessary that the code behind is updating occasionally (depending of some parameters) the same record in table2. It is at that very moment, the Error occurs.

[June7]

I still don't understand why editing table1 records would cause simultaneous edits to a table2 record.

[Luciano]

I have tblReservations with reservations (with a field = number of passengers) for a balloonflight (in tblFlights) with a field = sum passengers in the several reservations) in a hot air balloon. A balloonflight can have several reservations. Two (or more) reservations of the same flight can change their number of passengers or/and new reservations can be added to the same balloonflight (this happens occasionally).

[June7]

The sum passengers should not be saved to table. This should be calculated when needed. AFAIK, that will be the only resolution to your issue - don't save aggregate data, calculate it.