Replies

This same exact thing happened to me, though we’re using Access 2002/Win XP. From what my users said, two people were looking at (editing) the same record at the same time. The database has several tables, but there is only one table with a memo field. The users were working with data in that particular table. One user got completely locked out, the other was still able to work, but nobody could access that particular record. So I closed everyone down, deleted the .ldb file, compacted/repaired the database and everything seemed to be OK.Then same thing happened again about a month later. Did the same thing and thought everything was OK. At that point, the setup was 3 machines connected to a switch, with the database split into FE/BE on one machine and shared. Shortcuts to the FE were on the two other workstations.

A few weeks later, I happened to look at the database relationships and noticed some were missing! Tried to restore them and got the message that I couldn’t due to referential integrity rules violations. Yikes! Went into the affected tables and found a handful of records without parents, and records close by with the same data that did have parents. My guess is that some of the records were just left over when a parent record was deleted and the cascade didn’t occur and that the other records had been re-entered. So I deleted the orphans and restored the relationships. I also had to reset the primary keys in the affected tables, as they’d changed to regular keys.

Did a little more research on this great board and decided to put the front ends on each workstation, as well as changing the default record locking behaviour from “no locks” to “edited record”. Tried to duplicate the error and got a warning message about another user making changes to the record, which makes me feel a little more confident about the stability now. I am stuck with the memo field, I’m afraid, as it’s not possible to use SQL server and the users need a lot of space to record case information.

If anyone has other ideas about how to avoid this problem, I’m all ears. Thanks for listening!

-John