random number / record locking puzzle

Hey guys, I'd really appreciate any help on this one: I have a shared database and one of the tables contains info that when someone is using, nobodoy else should be allowed to access the same info. Therefore, each record in the table has a lock on it, and only the user who has access to the lock can edit the info. I'm trying to deal with concurrency amongst other users all in the same office. I thought the code below would be the best solution. I check if the lock is available, if it is, then I may be checking it at the same time as another user, so I wait a random number of milliseconds, then check it again, if it's still available, then I set the lock so that nobody else can edit that record.
The problem : when I test this by clicking edit at the same time on 2 different computers, both end up getting the same random number of milliseconds to wait, then they both check the lock at the same time AGAIN, so both try to update the record's lock at the same time, thus crashing the whole function. How can I create a random number that will be different when done on 2 different machines, but at the same time ?

checkLock.Open "SELECT lock FROM [A] WHERE item_num=" & num, con, 3, 3
If checkLock.Fields("lock") = 1 Then ' record is available
checkLock.Close
waitForTimer = True
Randomize CLng(date) + Timer 'random number generator
tmrWait.interval = CInt(Rnd * 1000)
tmrWait.Enabled = True
While waitForTimer
DoEvents
Wend
checkLock.Open "SELECT lock FROM [A] WHERE item_num=" & num, con, 3, 3
If checkLock.Fields("lock") = 1 Then 'first one to get lock
checkLock.update "lock", 0
checkLock.Close
Else
alreadyLocked = True
End If
Else
alreadyLocked = True
End If

If alreadyLocked Then
MsgBox "This entry is currently being edited by another user, try again later", vbOKOnly, "Currently Accessed"
Exit Sub
End if


'Timer has gone off.
Private Sub tmrWait_Timer()
tmrWait.Enabled = False
waitForTimer = False
End Sub

Query and Form objects have a RecordLocks property, and can be set to No Locks, All Records or Edited Record. Have you tried setting this property to see if that works?

I have tried using the adPessimistic, adOptimistic settings that go with the SELECT statement, but it doesn't offer enough control. If a record is in use and a second attempt is made to access it, it simply throws an error, and then I have to filter the error numbers to find what situation occured. I'd rather do the locking myself with the code I posted above.

what about trying a different strategy? i don't think you need to use the timer control to wait for availability. add a field to the locking table to hold a date/time string. this field is updated with the return value of the now() function when the locking table is checked for availability.

pseudo code eg:

read value of Lock field in table A for item_num
strReadTime = now()
if Lock field = available then
set date/time string field = strReadTime
set Lock field = unavailable
update table
end if

'check if lock was successful. if another user had also read that the lock
'field was available, they would have updated the date/time string field to
'a different time

read value of date/time string field in table A for item_num
if date/time string field <> strReadTime then
user has not successfully locked record
exit
end if

continue with edit...

In addition, I am going to assume you are working with Access here. Access does not support transaction isolation, meaning there is no guarantee regarding if two people hit the record at the same time, that the first user and the second user will have different lock flags and receive unique records.

What you are going to have to do is come up with your own home grown strategies. True client/server database engines such as SQL Server and Oracle will provide much more flexibility for you. We have had a lot of discussions on record locking with Access, you can start here:

http://www.xtremevbtalk.com/showthread.php?t=174674