wrong record field being changed/displayed

[Dave]

I'm desperately looking for some help to resolve a strange problem that only
seems to impact two of my users. Quick background: Split DB with 20 users,
front end resides on user's PC, back end resides on network sever(linked
tables). The form is a continuous form with subforms, and the bottom of the
form contains tabbed list boxes serving as record selectors ( book marks used
for record navigation).

I've witnessed the following problem but I can’t duplicate it on my PC. On
some occasions, the user will select a record to work on in the list box. The
correct record is brought up. The user goes to change a field value(usually
date fields or drop down boxes with status codes). On some occasions not all,
(1) wrong data(apparently from another record) appears in the related record
fields or (2) if the correct data shows and a change is made, the change is
not always there the next time they return to the record. Not sure where the
“update†is made.

The users claim that data for certain records has changed, even though they
did
not intend these to be records to be changed.

I’ve looked at the queries for the form, looked at the book mark bug,
checked user
permissions, looked at Access versions, stepped through the form’s VBA
while running test records and tried to duplicate the problem numerous times
unsuccessfully.

Any thoughts to what could be going on? Being a daily user of the database,
I have not run into this issue and its becoming more and more of a
problem with these two particular users. I’m really stuck, any help would be
appreciated.

 

[Arvin Meyer [MVP]]

You don't mention the version you are using. There was a bug in Access 97
without Service Pack 2 installed that did something like this. I believe it
was fixed in later versions. If you are using Access 97 on those 2
computers, get SP-2 and apply it.

 

[Dave]

Hi Arvin,

Thanks. Unfortunately, everyone is running Access 2003. Anything else we can
look at?

 

[James A. Fortune]

I'm desperately looking for some help to resolve a strange problem that only
seems to impact two of my users. Quick background: Split DB with 20 users,
front end resides on user's PC, back end resides on network sever(linked
tables). The form is a continuous form with subforms, and the bottom of the
form contains tabbed list boxes serving as record selectors ( book marks used
for record navigation).

I've witnessed the following problem but I can’t duplicate it on my PC. On
some occasions, the user will select a record to work on in the list box. The
correct record is brought up. The user goes to change a field value(usually
date fields or drop down boxes with status codes). On some occasions not all,
(1) wrong data(apparently from another record) appears in the related record
fields or (2) if the correct data shows and a change is made, the change is
not always there the next time they return to the record. Not sure where the
“update†is made.

The users claim that data for certain records has changed, even though they
did
not intend these to be records to be changed.

I’ve looked at the queries for the form, looked at the book mark bug,
checked user
permissions, looked at Access versions, stepped through the form’s VBA
while running test records and tried to duplicate the problem numerous times
unsuccessfully.

Any thoughts to what could be going on? Being a daily user of the database,
I have not run into this issue and its becoming more and more of a
problem with these two particular users. I’m really stuck, any help would be
appreciated.

I encountered almost exactly the same problem with A97 SP2 a couple of
times. Deleting and recreating the primary key fixed the problem for
me. Be extra careful how you go about this if the primary key is a
foreign key in other tables. Although it seems only to have an effect
on two users it will likely affect all of them soon if not fixed.

James A. Fortune
(e-mail address removed)

 

[Dave]

James,

Thanks for the input. Was your example a result of the DB corrupting?? Do
you know the cause or is this some other Access mystery? Unfortunately, the
PK is somewhat complicated. This key is autogenerated by code on record
creation(differnt form) and IS a foreign key in other tables.

In your situation in A97, were you able to isolate why this was occuring and
force it to happen during testing? Even if I'm able to delete all the PK's in
my A2003 SP2 version and recreate, I still don't know what causes this issue
nor how to prevent it in the future. Replacing all the PK's would be a very
tedius jobs effecting thousands of records.

 

[James A. Fortune]

James,

Thanks for the input. Was your example a result of the DB corrupting?? Do
you know the cause or is this some other Access mystery? Unfortunately, the
PK is somewhat complicated. This key is autogenerated by code on record
creation(differnt form) and IS a foreign key in other tables.

In your situation in A97, were you able to isolate why this was occuring and
force it to happen during testing? Even if I'm able to delete all the PK's in
my A2003 SP2 version and recreate, I still don't know what causes this issue
nor how to prevent it in the future. Replacing all the PK's would be a very
tedius jobs effecting thousands of records.

One of the occurrences was when the company switched from using Samba
with a linux server I had set up to a Windows 2003 Server. I had about
80 administrative (not Admin) users, of whom about 40 to 50 were logged
in concurrently. Simultaneously, I had about 100 non-administrative
users, of whom about 10 to 60 were logged in concurrently making a total
of about 50 to 110 concurrent users. The problem happened on a database
accessible only to the administrative users. My best guess of what
happened, based partly on the fact that an expensive network upgrade got
rid of the problem, is that the Windows 2003 Server, being several times
slower at creating records than Samba on linux greatly increased the
likelihood of corruption of the primary index. I further suppose that
the corruption of the primary index happens in the short amount of time
when multiple users are editing the same record or when multiple users
obtain the same primary key value for adding a new record. Because of
the circumstances it is quite difficult, but not impossible, to
duplicate the conditions necessary to cause corruption of just the
primary key. The problem never occurred with only a few concurrent users.

The primary key that got corrupted was a foreign key in several other
tables. In the original table I created a Long field and ran an update
query to put the primary key values in the Long field. If I remember
correctly, I created a table with the same fields as the original table
(including an AutoNumber field for the primary key) and used the Long
field in an update query to populate the new primary key field. That
way the related tables still relate.

The following post by David Epsom was particularly helpful:

http://groups.google.com/group/comp.databases.ms-access/msg/212a827088849517

I don't know of another way to solve your problem. Maybe playing with
the Indexes collection will reveal a way to fix or regenerate the
indices. A timestamp field might shed some light on the source of the
problem.

James A. Fortune
(e-mail address removed)

 

[Arvin Meyer [MVP]]

James,

Thanks for the input. Was your example a result of the DB corrupting?? Do
you know the cause or is this some other Access mystery? Unfortunately,
the
PK is somewhat complicated. This key is autogenerated by code on record
creation(differnt form) and IS a foreign key in other tables.

In your situation in A97, were you able to isolate why this was occuring
and
force it to happen during testing? Even if I'm able to delete all the PK's
in
my A2003 SP2 version and recreate, I still don't know what causes this
issue
nor how to prevent it in the future. Replacing all the PK's would be a
very
tedius jobs effecting thousands of records.

If indeed you have a corrupted index you only need to replace the PK in that
table along with the matching foreign keys. Not too hard to do. Typically,
you can be done in less than 1 hour.

1. MAKE A COPY OF THE FILE (several copies is better)
2. Import all your tables into a clean empty database.
3. Identify the affected table and all the foreign key tables.
4. Change the Primary Key autonumber to a long integer
5. Create a new autonumber field in that table and an empty field in the
others
6. Use update queries on each pair of tables, linking on the old keys and
updating the empty column with the new autonumber.
7. Rename the old fields until you are sure they won't be needed. Then
delete them.
8. Rebuild the relationships.

The first set of table will take you 15 to 20 minutes to do. As you gain
confidence, they'll take 3 to 5 minutes a set.

 

[Dave]

Gentlemen,

Thanks to both of you for your help! I've got a starting point now to a
problem that has been driving me nuts for weeks. I'll give your suggestions a
shot. Thanks again for your help, its appreciated.

Dave