MS Access: Avoiding "Lost Updates"

Question

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.

Answer 1

Inherited MS Access is always the best,isn't it. To get the obvious solution over and done with early, I recommend moving the data over to SQL Server. It's really not a big job. You might also get some benefit by splitting the database (GUI and data) into separate .mdb's

That all said, I've never come across that sort of behaviour before. Obvious questions are:

• How large is the database?
• What version is it?
• Has it been compacted and repaired recently (take a backup first of course)?

There is transaction processing that you can do with MS Access, but I've found it to be a pain in the butt, and often leads to bizarre errors (until you start grokking the way that the transactions are colliding). Most of your application is probably assuming optimistic locking, and the default behaviour is contradictory to transactions the way you would think of them in Oracle or any other real database. It's probably not worth it.

One more thought, perhaps there's two people with write access looking at the same record at the same time; and one is doing updated in a different field of the record.

Answer 2

Some thoughts:

1. Is there a genuine need for 2 people to have write access to the same table at the same time? I think you can lock out the 2nd person easily enough by changing the Record Locking and Open Modes in Tools->Options.

2. You said the updated data usually reverts to its old self at night. Could someone be running an overnight process that also updates the same tables?

Answer 3

I have seen something slightly similar more than a decade ago, before the bookmark bug was found and patched. In that case, leaving an edited record via bookmark navigation was losing the edit without throwing an error.

In that case, before MS patched it, the solution was to force the save before navigating away from the record:

  With Me.RecordsetClone
    .FindFirst "..."
    If Not .NoMatch Then
       If Me.Dirty Then
          Me.Dirty = False
       End If
       Me.Bookmark = .Bookmark
    End If
  End With

The other thing to check is if error reporting is turned on and how that's interacting with the VBE error handling settings. If the code is littered with On Error Resume Next in order to ignore generated errors, then the code needs to be completely rewritten. The problem with it is that it doesn't always go out of scope as expected.

I never use On Error Resume Next, myself. Instead, if I'm expecting a particular error but want to ignore it, I trap for that error and ignore it, and thus allow any other errors that I haven't anticipated to not disappear down the memory hole.

My thinking here is that some kind of locking issue is generating an error that's being ignored. Thus, the error never gets reported and the changes get lost without anyone ever knowing.

Another thing to look at is whether the back end is stored on a file server where the drives are replicated. This is an untenable setup for Access and Jet, because it completely kills all of Jet/ACE's record locking and internal transactions because the file image is in a state of flux. I've seen reports of what you're describing where two servers were replicated, and both versions of the MDB were being edited. The result was loss of data when the file system replication kicked in and overwrote the changes on one side with the changes from the other.

Answer 4

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.