Multiple users - help please!

[MarkWaddington]

Is there any easy way I can prevent users from accessing a record on a form if somebody else already has it open? I.e. a message box popping up saying "Another user is looking at this record right now, come back later."?

I have implemented record level locking but it doesn't really do the trick, and throws up errors when more than one person tries to edit one particular record.

Thanks in advance.

Mark Waddington.

 

[JeremyNYC]

I often make a tblLockedRecords to record the records being accessed. It's not bulletproof, but it can help.

In the databases where I've done this, it's not possible to touch data in datasheet view and the full forms are always opened filtered to just one record.

When you open the forms with which you want to lock records, add record to the above table. The fields should just be tableName, ID, and UserName. In this same event you'll also have to check for a lock on this record. If there's a match, make the data untouchable (allowEdits, allowDelete, etc.). When you close the forms, get rid of that record.

The futzing comes when a user shuts down incorrectly, such as through a poweroff, etc. I also only let users get out of the application through closing all forms and then closeiing the switchboard. But still, there are sometimes records left in tblLockedRecords. To deal with this, there are three strategies:
1) when you leave the database code kills all records with your name that are in the table.
2) when you get into the database, that same code fires.
3) the administrator has the ability to clear all records at the touch of a button.

It's a good bit of work, and comes mostly from the fact tat I used to deal exclusively with unbound formsl But it does get the job done to a fairly good extent.

Also, when you open a record that is locked down, the caption of the form tells you that it's locked, and shows you the name of the person who has it locked.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.

 

[DanJR]

With pessimistic locking, couldn't you just check the status of the lock when opening the form, somthing like:

[tt]
Private Sub Form_Open(Cancel As Integer)
'Example of crudely checking lock status
Dim rs As DAO.Recordset

on error goto errLoad

Set rs = Me.RecordsetClone

On Error Resume Next
rs.Edit

If Err.Number = 3188 Then
'locked by someuse -so don't open form
MsgBox "Locked", , "Example"
Cancel = True
End If

Err.Clear

on error goto errLoad
me!LastAccessedDate = now() ' force locking

CleanUp:
Set rs = Nothing
exit sub

errLoad:
msgbox err.description
resume cleanup

End Sub
[tt]

However, I can see the problem if a user opens the form but doesn't start editing (ie, no lock occurs) - hense using some method to force locking.

Nevertheless, would ever approach, you'll never to ensure the user can't lock the for too long, e.g. by using a timer of some sort.

Are you using Access 2000 or above - as this defaults to record level locking. I wouldn't try this with page level locking

Cheers,
Dan

 

[MarkWaddington]

Hey thanks guys. You've given me hope that this is possible!

Both methods sound like they would improve things for me, but I don't really understand Dan's method, as errors don't occur when two people open the same record, it's only when they close it!

I think i'm going to have a go at a manual record locking table.

When you say this:

"1) when you leave the database code kills all records with your name that are in the table."

Would this still work if the user left the database via a crash of some sort (i.e. not by closing the switchboard) - if so, what code do you use (and where do you put it)?

Thanks.

Mark Waddington.

 

[JeremyNYC]

Mark,

No, that's one of the places where the imperfection of my method shows up.

One other helper for those situations that I left out is that you should include code to clear out that locking table when teh last user gets out of the database.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.

 

[DanJR]

sorry, there actually three common update error messages that can occur (that I can think of):

3188 - locked by this machine
3260 - locked by user....
3218 - locked

 

[MarkWaddington]

Jeremy, how would I determine when the last user has logged out of the database? I use Access' in-built security.

Thanks,

Mark Waddington.

 

[JeremyNYC]

Mark,

The way I do it is to only allow the user to close the database through a comand button, suing code in the unload event of every form to cancel the event unless each one is closed using a button, and making it so that the user always has to pass through a switchboard type form to get out of the database (that's the only one that will actually close the database). There's code in the close button of that form that removes the record from the table recording who's using the database.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.