Multiuser db - what kind of record locing to use

[dpimental]

All, I have a multi-user datbase application. What kind of locking should I use. Should I use Record Level?

There will be about 20 t0 30 users who will be using the system, across a network.

David Pimental
(US, Oh)

 

[Ed2020]

It depends on the situation and the users.

I tend to use pessimistic record locking more often than optimistic and I prefer to create my own functions than use Access's built-in record locking.

Ed Metcalfe.

Please do not feed the trolls.....

 

[dpimental]

what is the difference between pessimistic vs optimistic?

Also, can you give me an example of the kind of locking you do?

About the first point, does it affect speed of updates?

David Pimental
(US, Oh)

 

[Ed2020]

Pessimistic Record Locking - When a user attempts to lock a record already being edited by another user a message will be displayed immediately, telling them that the record is already being edited and preventing them from making any amendments themselves.

With optimistic record locking the user will be able to continue making updates. When they attempt to save the record a dialog box will be displayed asking them whether they want to overwrite the other user's amendments with their own, discard their own changes, or copy their changes to the clipboard. It is "optimistic" because you must have confidence that the users will take the correct course of action.

I create my own routines by adding a "LockedBy" field to the relevant tables. When the user opens the record for editing their user ID is added to the field. When they save their changes the LockedBy field is cleared. When records are opened for editing a check is performed for the value of LockedBy and, if it it not null, a messagebox is displayed to inform the user who already has the record locked (if memory serves Access record locking does not do this).

I'm not sure how clear my explanation of the two types is. Access help files have some information and a bit of Googling will give more detail.

Ed Metcalfe.

Please do not feed the trolls.....

 

[dpimental]

No, that's very clear. I should definite use pessimistic. I mean the likely hood that the same record will be editied by more than 1 person is highly unlikely.

While I have some 25 - 30 users who can edit records. They cannot edit the same record types.

There are purchasing records, that on the purchase dept. can edit, engineerig records, assembly records, management records, etc.

Thanks you made this clear. I'm a programmer, but it's not something that I've had to work with much yet.

David Pimental
(US, Oh)