MS Access: Avoiding "Lost Updates"

We've inherited a rather large Access DB that has some very strange (and unsettling) behaviour: Some updates to the database are lost. What can we do to avoid this? Are there any settings in Access to provide better transactional control?

Here are some more details:

We have one access user that has write access (shared by a very limited number of users - currently only me as the others are on vacation).
We have another access user that is used by many people that only has read access.
Some changes to the data are made by the write user.
After leaving and re-entering the table and/or even the application, the changes seem to have been "committed".
After a while (usually overnight) the changes are lost and the data is back to the old values.

What can cause this behavior?

Our theory is that this is caused by some weird transactional control within Access. The read-only user gets some kind of "exclusive lock" to the data that is used within a query or form. Once the user leaves that query/form, Access makes sure that this is still in the database. If in the meantime the write user has changed the data, these changes will be reverted when the read only user leaves the query/form, resulting in a lost update. Does this make sense? Is this a known issue with MS-Access??

Also, we're interested how we can avoid this issue. Is this something that is inherent in Access and can only be avoided by switching to a "real" database with better transactional control? (From a technical viewpoint, that would be nice; but of course it would be an urgent todo that we would rather avoid at this point in the project.)

Thanks for any input and let me know if there is extra information that is required.

Solution

Your clarification of the scenario is appreciated.

The idea that a read-only user could cause changes by a write user to be lost is not possible.

Maybe the read-only users aren't actually read-only? How, exactly is read-only vs. read/write implemented? In my apps, I'd probably have the forms by default set to read-only, and set Allowedits/Allowadditions/Allowdeletions on the forms to True for the read/write user. I assume from what you've said that you're using Jet user-level security, likely with the default Admin user being the read-only user, and some other user name being the read/write user. Thus, in all the forms you could do this in the OnLoad event:

  Me.AllowEdits = (CurrentUser()<>"Admin")
  Me.AllowDeletions = Me.AllowEdits
  Me.AllowAdditions = Me.AllowEdits

It would also be wise to apply user-level restrictions to the tables (back end only, or both back end and front end), giving read-only Admin user group and User user group, and then give read/write to your one user. Jet ULS is not like NTFS security, where least permissive permissions win -- in Jet ULS, MOST permissive wins, and that's why you have to make sure that you make both Admin and User groups read-only (and give no permissions to the Admin user specifically, i.e., so all permissions are inherited from group membership).

I'm assuming, of course, that this is not being attempted through NTFS permissions on the back-end MDB. If so, that's likely to be a major problem and is simply not the right way to do it. From what you've said, it seems unlikely that this is what is going on, so I won't say any more about it.

But the first thing I'd look at is whether or not the so-called read-only users really are read-only.