Removing customized locks on records after being disconnected

[kaiuweb]

Hi people,
I'll just briefly mention that this is my first database with SQL Server, so if the following makes no sense and you have a better suggestion then I'd like to hear it.

I have created a table called TBL_ENQ_LOCK which stores the primary key of a record from a table caled TBL_ENQUIRY along with the name of the user who first opened that record.
The purpose of this is to stop the next user getting the chance to edit the same record.They just get a message stating that the record is locked by userA.

When userA has finished, the record is removed from TBL_ENQ_LOCK and then userB is allowed to open the original record for editing, at the same time placing another record in TBL_ENQ_LOCK this time with with userB's name.

Before doing this I kept getting a message all about wanting to use the clipboard or undoing what I've just typed in, which I didn't want the user to go through.

What I am worried about is; if the Acccess Project front end 'CRASHES' instead of the user closing the form properly, then the delete stored procedure isn't run against TBL_ENQ_LOCK and whenever userA or userB goes in again, the record will still be locked until they notify me and I have to go in and manually delete the record.

What I was thinking of is, if a job could be set up on sql server to monitor connections and then possibly check the TBL_LOCK table to see if any records in there are locked by a user who no longer has a connection.

Any ideas anyone even if they totally rubbish what I have done so far. I'd really like to know how to do this properly.

Thanks.

 

[bdiaz]

I don't know about Access Project, but if you want to lock records, can't you use SQL Server locking facilities ?

you can jave a look on BOL ("Understanding Locking in SQL Server", "Locking hints",...)

 

[tlbroadbent]

Modify your lock check routine to see if the user seeking the lock is the one that already has the lock. If so, just update the table and allow the user access to the table. Thus if UserB gets the lock message, she could call UserA to see if he still needs the lock. UserA could open the record, the lock would be updated and then deleted when UserA closes the record.

You can also check to see if a certain amount of time has passed, check if userA is still logged in. Example: If the locking user is not currently logged in and a pre-determined amount of time has passed the lock could be removed. If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm