it isn't a code problem in particular. It's a very real problem of a any multi-user system - where more than one user needs access to a record at the same time
by default access uses a "locking" strategy called optimistic locking. (This is the no locks setting you saw) It assumes that a record will be safe to edit, unless it finds otherwise.
If you change the no locks setting to one of the other settings then you get pessimistic locking which I expand on below. It isn't as simple as just assuming that is an easy fix though - as it can cause as many or even more problems than it fixes.
record locking is an option to manage the situation with multiple "readers" and "writers". Readers are harmless, as any number of readers can access a record without problems. The problem is writers. If two writers get the same record, and both change it - then depending on the sequence, you can get inconsistent updates.
say a record has a value 4, and one user wants to add 5 to it, and another add 6. The correct result should be 15 - but if both users get the record when it is 4, one will make it 9, and the other make it 10, and neither will correctly make it 15.
now if you "lock" the record when the first user gets it - it stops the second user getting it - but it also stops all the "readers" getting it. If the user who locked the record leaves it locked (eg goes to lunch) everyone gets affected. It may prevent normal reports being run, and so on.
so with optimistic locking, instead of actively locking a record, access assumes the write will be Ok.
given the above scenario - user A gets a value of 4, adds 5, and sets the value to 9. user B gets the value of 9 adds 6 and sets the value to 15.
now if they both get the record when it is 4, what happens is this
users A gets 4, adds 5 and sets it to 9. User B also gets 4, adds 6 and tries to set it to 10.
BUT what happens when a record gets saved (ie changed) is that access re-reads the record first, to make sure it is the same as when you first read it. User A doesn't have a problem. The value is still 4, so the edit proceeds, and the value gets updated to 9. Now user B also read the initial value 4, and edited the value to 10 - but in his case the re-read finds that the record no longer contains 4 - it now contains a 9 - so now you get the "another user changed your data" message
It's a matter of taste whether you prefer
a) to get this message or
b) to actively implement a strategy that prevents users accessing records in the first place, with the attendant problems that can be generated by that method.
The problem with active locking strategies, as well as the problem described above of waiting for locks to be released - is the opposite to inconsistent updates and is called a deadly embrace. When you implement an active locking strategy you can get a situation where user A gets and locks record 1, and needs to get and lock another record 2. However user B might have already established a lock on record 2, and now needs record 1. So both users are stuck waiting for the other to finish. So you need a way of making one of these users abort their process, and backing out and releasing a lock and possibly undoing previous edits - which means your app needs very careful design. you may even have a circular chain of several users, each of which needs a resource held by another user. I never use active locking, but I think access does include retries, and time-out settings for this purpose. But in general, if you want locking it is best to design the app to require locks for the absolute least time necessary.
All in all, I prefer optimistic locking, and the occasional "another user" message.
just one other point. Record locking itself is often achieved by "page" locking. Memory is split into pages of various size. A record can exist on more than one page, and other records can use the same page your record is on. Locking records can also result in locally adjacent records being locked also - another reason to be careful about minimising the time you actually hold locks.
If you are desperate to lock records in your situation it might be easier to have an "inuse" flag within the record that can be set and unset by your programme, although sometimes such flags can be left in place accidentally, rather than use active record locking. Nobody said programming for multiple concurrent users was easy!
