Multiple users editing records

[lostside]

here's my problem:

I have an access db that is used by aprox 20 users, this db is devided in FE and BE each user has his own copy of the FE. The idea of this DB is that each user can work records from a table, so a user will open the db and get a record, finish working with it and get a new record to work with. i nedd that only one user can view a specific record so i came up with this structure:

Table:
Id - text
type - text
userlock - this will be filled with the username of the person woking the record
userdone - this will be filled with the username of the person that finished the record
locked - yes/no - will be yes if someone is working on the record.

ive posted the code to select new records below.

the problem is that sometimes 2 users get the same record. any ideas wy?

tks

Private Sub Command143_Click() 
Dim db As Database
Set db = CurrentDb
Dim rs As Recordset 
 
Set rs = db.OpenRecordset("Select * From report where locked=FALSE AND isnull(userdone) and isnull(userlock) and type ='type1'", dbOpenDynaset)
If rs.EOF Then
MsgBox ("No more cases")
Exit Sub
Else
rs.Edit
rs.Fields("locked").Value = True
rs.Fields("userlock").Value = userid
rs.Update
Set rs = Nothing
End If 
End Sub

 

[Guus2005]

When retrieving the record, first let the user lock it:

currentdb.execute "Update report set userlock = '" & GetUsername & "' 
     where id = '" & strID & "' and isnull(userlock)"

Here GetUsername return the username. You might have a similar function or variable. strID represents the ID of the record.
Then the user can retrieve the record when his/her name is stored in the field userlock for that record.
Releasing all the locks for a user:

currentdb.execute "Update report set userlock = null 
     where userlock = '" & getusername & "'"

Enjoy!

 

[DCrake]

Adopting this methodology is fine however you will need to build in some sort of adminstration override, in case a user exits Access through an unexpected route and the locked flag is not reset. As an Administrator they should then be able to see which records are locked and who has them locked. In theory 1 person cannot have more than one record locked at any one time.

David

 

[lostside]

tks for the replay,


you say to use this line where id = '" & strID & "' and isnull(userlock)"

so this means i have to select the id to be locked first right?

so its the same as my code:

Set rs = db.OpenRecordset("Select * From report where locked=FALSE AND isnull(userdone) and isnull(userlock) and type ='type1'", dbOpenDynaset)
If rs.EOF Then
MsgBox ("No more cases")
Exit Sub
Else
rs.Edit
rs.Fields("locked").Value = True
rs.Fields("userlock").Value = userid
rs.Update
Set rs = Nothing
End If

 

[lostside]

tks for the replay,


you say to use this line where id = '" & strID & "' and isnull(userlock)"

so this means i have to select the id to be locked first right?

so its the same as my code:

Set rs = db.OpenRecordset("Select * From report where locked=FALSE AND isnull(userdone) and isnull(userlock) and type ='type1'", dbOpenDynaset)
If rs.EOF Then
MsgBox ("No more cases")
Exit Sub
Else
rs.Edit
rs.Fields("locked").Value = True
rs.Fields("userlock").Value = userid
rs.Update
Set rs = Nothing
End If

 

[Guus2005]

Your solution is more or less the same. I prefer doing it in one sql statement. It is faster and when locking is an issue i prefer speed.

 

[lostside]

well with either your code or mine the problem still happens

users still get the same record to edit sometimes.

any ideas why?

 

[Guus2005]

Something isn't right. without seeing the code i can't give you more help.
please post a sample database describing the problem.

HTH

 

[lostside]

this is the code i'm using now:

Private Sub Command143_Click()
 
Dim db As Database
Set db = CurrentDb
Dim rs As Recordset
 
Set rs = db.OpenRecordset("Select * From report where  isnull(userdone) and isnull(userlock) and type ='type1' ORDER BY id", dbOpenDynaset)
If rs.EOF Then
MsgBox ("No more cases")
Exit Sub
Else
rs.MoveFirst
rs.Edit
rs.Fields("locked").Value = True
rs.Fields("userlock").Value = userid
rs.Update
Set rsadd = Nothing
End If
DoCmd.OpenForm "form1", , , stLinkCriteria
End Sub

each user has is own copy of the FE in the local HD, i can´t figure it out what is wrong.

thanks so much for the help

 

[Guus2005]

Where does stLinkCriteria come from and what are its contents?

Is it correct to state that

stLinkCriteria = "isnull(userdone) and isnull(userlock) and type ='type1'"

Private Sub Command143_Click()
 
Dim db As Database
Set db = CurrentDb
Dim rs As Recordset
 
Set rs = db.OpenRecordset("Select * From report where  [COLOR="Red"]" & stLinkCriteria & "[/COLOR] ORDER BY id", dbOpenDynaset)
If rs.EOF Then
  MsgBox ("No more cases")
Exit Sub
Else
rs.MoveFirst
rs.Edit
rs.Fields("locked").Value = True
rs.Fields("userlock").Value = userid
rs.Update
[COLOR="red"]rs.close[/COLOR]
Set rsadd = Nothing
End If
DoCmd.OpenForm "form1", , , stLinkCriteria
End Sub

and you should close the recordset neatly before freeing memory space.
HTH

 

[lostside]

no use for stLinkCriteria, i've removed it.

Well i'm about to give up, dont know what else to do.
i'm using a diferent code
still when i put it to test sometimes users get the same record,
how can this happen? MS selects the record in the exact same second as the next user?
i tought woking with this kind of multi user environemet should be easy to set up.

code:

main form:
 
Private Sub Command143_Click()
db.Execute "UPDATE report SET locked=True, userlock= " & Int(mainform.txt_userid.Caption) & "  WHERE (((report.id) In (SELECT top 1 id FROM report WHERE isnull(userdone) and isnull(userlock) and type='type1' ORDER BY id)));"
 
Set rs = db.OpenRecordset("SELECT id FROM report WHERE locked=True and userlock= " & Int(Form_entrada.txt_userid.Caption) & " AND isnull(userdone) AND type='type1'")
 
If Not rs.EOF And Not rs.BOF Then
DoCmd.OpenForm "inserirw1"
Else
MsgBox ("no more cases")
Exit Sub
End If
Set rs = Nothing
End Sub
 
Form1:
 
Private Sub Form_Load()
 
Set rs = db.OpenRecordset("Select * From report where userlock=" & Int(mainform.txt_userid.Caption)& "", dbOpenDynaset)
If Not rs.EOF Then
idtolock = rs!id
End If
 
Me.RecordSource = "SELECT report.id, report.comments, report.123 FROM report WHERE (((report.id)='" & idtolock & "'));"
 
Set rs = Nothing
End Sub

 

[Guus2005]

could you post a sample database?
i can't put my finger on it. it's easier to look at the total picture.

 

[datAdrenaline]

This is a LOT of effort for something that Access can do inherently....

Simply set your "Record Locks" property to "Edited Record", this will invoke Pessimistic locking, and AS SOON AS a user begins the edit of a record, the JET/ACE database engine will lock the record and prevent ANYONE else from editing the record.

Note that the default for the Record Locks property is "No Locks" which is called "Optimistic" locked. In optimistic locking, the record is NOT locked until an attempt s made to save it. Thus two users may edit the same record, but the SECOND one to save the record will get a warning about how the data has changed and how the changed data may be overwritten.

So .... I recommend you drop the code and just use pessimistic locking!