Recordset Locking in a Multi-User Enviroment - FE or BE ?

[bostonfrog]

Whatever type of record locking I use, where should I set the type of record locking, on the front end db where the tables are linked, or on the BE database? Obviously, I shouldn't set this on both the BE and FE. In this prototype, which has caused no problems so far, I have decided to be very restrictive (using pessimistic locking), but I could set this at the application level through Tools | Options | Advanced or at the form level on the front end. Which is better? If I set up record locking at the form level on the FE, do I leave the BE alone? If, on the other hand, I decide to use the application options, do I do this on the BE and leave the default FE options as is? If I use Option 2 (in VBA, Edited Record - pessimistic locking), would I also check Open database using record-level locking?
Please advise. Thanks.
-- Michel

 

[markgrizzle]

I think you're better off setting it at the form level, to save you the trouble of making sure everyone's copy of access is set up the same way.

 

[bostonfrog]

Thanks, Mark. I do call a simple function when the program starts to set the lock option to "Edited Record", but do I also need to check Open database using record-level locking? Thanks.

 

[markgrizzle]

Just looked it up in Access XP. Setting it makes record level locking the default for data accessed through a form, recordset, or datasheet, but not for action queries or code performing bulk sql operations.

If I understand this right, it means I can dispense with adding the locking statements to my ado code.

I normally leave the be alone, and do everything from the fe.

Hope that helps.