Multi-User Record Locking Strategy

[DaveTappenden]

I need to consider a record-locking strategy for my multi-user application (Access 2000 front-end, SQL Server 2000 back-end). Many of my forms are bound (eeeek) but in all cases they are bound to underlying queries that have returned (at most) a handful of records.
The default record locking in Access is pants. I don't want my user to be editing a record that someone else is already in. Also, it would be good if I could display which user is actually updating the record.

Can anyone suggest the best record-locking strategy?

I am seriously considering creating my own with boolean fields in each table that I can test and set when the user presses the edit control on a form. Has anyone done this or considered doing this and are there any pitfalls? My worry is system problems causing records to remain locked in error(e.g. if the user doesnt exit the form normally).

Any feedback would be most welcome

 

[KenReay]

Hi

First why re-invent the wheel and replace it with an inferior one, ie do not write your own

Second get yourself a copy of Access Developers Handbook it contains useful advice on this and many other topics

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[DaveTappenden]

Ken,
I only considered the manual (lock field) method because i actually believe the Access locking is inferior! I really need a way to stop a user even BEGINNING to update a record if it's currently being updated by someone else.
As I understand it, the Write-Conflict error message that I will get with optimistic record locking can be trapped, but my only option is to allow Access to display its (unfriendly) Write Conflict Error, or to just ignore the error. Neither options are really suitable for me. I would like to warn the user that the record is locked as soon a it is viewed (in any form). Even better, tell the user who has locked it.
Creating fields to manually control record locking is a pain I grant you, but the result would surely be superior to the Access default method?

 

[KenReay]

Hi

In Access, you can use Pessimistic locking, which will lock the record on edit, rather than on update (attempt).

I am not sure if this is available when using SQL Server tables.

I really would recommend reading the Access Developers Handbook on this subject. I have no connection to the authors or publishers or anyone else connected to the book for that matter, but it is a good investment.



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK