I'm developing a front-end system that will be for multiple users. There is a table called Tasks and a form will allow a user to perform actions on a Task row such as:


Once either are clicked the Status field will change which is checked for each action and if one action is started another cannot. For example if User1 clicked Accept and User2 also clicked Accept, User2 would get an error.

Imagine this psuedo code for a user clicking accept:

#1 Get Status field
#2 Check if Status field is 0
#3 If it is 1 then Exit function
#4 If it is 0 then set status as 1
#5 [...]

So should User 1 and User 2 try the above but User 2 does it 1 second after, they would get the error.

The problem I have is what if both users click at exactly the same time? Both may run Line #1 at the same time and see Status as 0 and then the same task is assigned to multiple users which shouldn't happen.

So what I want to happen is this:

#1 Set lock on row in Status table
#1 Get Status field
#2 Check if Status field is 0
#3 If it is 1 then Exit function
#4 If it is 0 then set status as 1
#5 Remove lock on row in status table
#6 [...]

What should happen in the above is, only one user will be able to check and modify the Status field at any one time preventing concurrent errors as described above. In the case where two users run the code at the same time, User 2 will wait for the lock to be released after which it will see the correct Status field.

So how can I achieve this in MS Access VBA?

This is the code I have:

strSQL = "SELECT * FROM Task WHERE TaskId=" & SelectedTaskId
Dim rs As New ADODB.Recordset
Set cn = CurrentProject.Connection
rs.Open strSQL, cn, , adLockPessimistic
MsgBox rs!TaskId & " " & rs!TaskStatusId
Sleep 10000
MsgBox rs!TaskId & " " & rs!TaskStatusId

The Sleep command is to allow me time to switch to another user and execute the same code with a button click.

I expected to see some record lock error but both users seemed to be able to perform the same query. I don't need an error, just some way of confirming that a record lock was successful.