Record Locking (Access 2000)

Topic

New Reply

We have a routine that is used by multiple users at once, so needs to rely on record locking. The procedure is designed to read the first record in a file that has no entry in a specific field and then to stamp the users name in this field (thus reserving the record for them). Because more than one user can be executing this routine at once, it is important that two people could not be writing their name against the same blank field at the same time.

The code looks like this:

Dim db As Database, rs As Recordset, ok2go as integer
ok2go=false
Set db = CurrentDb()
Set rs = db.OpenRecordset(“SELECT * FROM [MasterFile] where isnull([Processedby])=true and isnull([processedon])=true and [followupon] <= now() ORDER BY [MasterFile].UniqueRef", dbOpenDynaset, dbSeeChanges)
If rs.EOF = False Then
rs.MoveFirst
Do While ok2go = False
If IsNull(rs![ProcessedBy]) = True Or rs![ProcessedBy] = CurrentUser() Then
rs.Edit
rs![ProcessedBy] = [Forms]![splash]![TheUser]
rs.Update
If rs![ProcessedBy] = [Forms]![splash]![TheUser] Then
ok2go = True
End If
Else
rs.MoveNext
End If
Loop
End If

Having read articles in the lounge on locking in SQL and using .lockedits =true, I can get the code to report 3197 "The Microsoft Jet Database Engine stopped the process because you and another user are attempting to change the same data at the same time", which is part of the way there (at least two users don't end up with the same record), but does anyone know of a way to check if the record is locked and if it is move to the next record in the set rather than just wave a flag of defeat?

Perhaps there is a better way that I am trying to use to get the same end result?

Any assistance gratefully received!

Reply | Quote

Replies

I didn’t see any error trapping code, but [warning — inelegant solution about to be suggested] in your error handler check to see if Err.Number 3197 is thrown. If it is resume at a label “TryAgain:” which I would put right after the Do While statement.

Slightly revised code

Public Sub DoWonderfulThings()
On Error goto Err_DoWonderfulThings

Dim db As Database, rs As Recordset, ok2go as integer
ok2go=false
Set db = CurrentDb()
Set rs = db.OpenRecordset(“SELECT * FROM [MasterFile] where isnull([Processedby])=true and isnull([processedon])=true and [followupon] <= now() ORDER BY [MasterFile].UniqueRef", dbOpenDynaset, dbSeeChanges)

If rs.EOF = False Then
rs.MoveFirst
Do While ok2go = False
TryAgain:

If IsNull(rs![ProcessedBy]) = True Or rs![ProcessedBy] = CurrentUser() Then
rs.Edit
rs![ProcessedBy] = [Forms]![splash]![TheUser]
rs.Update
If rs![ProcessedBy] = [Forms]![splash]![TheUser] Then ok2go = True
Else
rs.MoveNext
End If
Loop
End If

Exit_DoWonderfulThings:
Exit Sub

Err_DoWonderfulThings:
If Err.Number = 3197 Then 'Record locked try again
Resume TryAgain
Else
MsgBox "Error: " & Err.Number & VBA.vbNewLine & Err.Description
Resume Exit_DoWonderfulThings
End if

End Sub

Reply | Quote

Thanks FlyerMike, I’ve never been too hot on the error trapping thing, but I understood what you suggested and it worked fine!

Reply | Quote

What do you mean by this: [indent]

---

The procedure is designed to read the first record in a file that has no entry in a specific field and then to stamp the users name in this field (thus reserving the record for them).

---

[/indent] Are you talking about a linked file or a table, which are two very different things, indeed? Assuming from the SQL that you’re talking about a table, what creates those records in the first place so you can stuff in a user name?

One of the problems you’re having is the direct result of using CurrentDb, which points to the instance of the Access database loaded into memory on that machine, not to the backend on the server, so the two are slightly out of sync. That means that you may be able to avoid the problem by using DBEngine(0)(0) instead of CurrentDb to instantiate your database object. However, until you save the newly changed record other users will not see the “locked” record, and you will need to force a refresh on the database object to get that into the CurrentDb as well.

Perhaps if you explain more about what you’re trying to accomplish with this table, we can be of more help.

Reply | Quote

What do you mean by this: [indent]

---

The procedure is designed to read the first record in a file that has no entry in a specific field and then to stamp the users name in this field (thus reserving the record for them).

---

[/indent] Are you talking about a linked file or a table, which are two very different things, indeed? Assuming from the SQL that you’re talking about a table, what creates those records in the first place so you can stuff in a user name?

One of the problems you’re having is the direct result of using CurrentDb, which points to the instance of the Access database loaded into memory on that machine, not to the backend on the server, so the two are slightly out of sync. That means that you may be able to avoid the problem by using DBEngine(0)(0) instead of CurrentDb to instantiate your database object. However, until you save the newly changed record other users will not see the “locked” record, and you will need to force a refresh on the database object to get that into the CurrentDb as well.

Perhaps if you explain more about what you’re trying to accomplish with this table, we can be of more help.

Reply | Quote