Data Shifting Between Records

[Neil]

So it is not the enteirng of the data that is doing something unusual, it

is rather the editing of it. If you also do this via stored procedures
that simultaneously apply changes to both tables then I must assume that
there is a flaw in the logic or application of those stored procedures.

The stored procedures are only used for creating the records. Once the
records are created, the user is brought to them and editing is done via
bound form and ODBC table links. In the case of "Form1" (from original post)
the form is bound only to TableA (the one without the problem). In the case
of Form2, the form is bound to TableA joined with TableB (the one with the
problem).

In this recent incident, as I noted in the post, the users of both the
record that was affect and the record from which the stray data came, used
only Form1. However, the data that was shifted was in TableB.

In other words, though the forms used in editing these records didn't touch
TableB, the records in TableB were affected. Their only connection to the
TableA records that were affected is the one-to-one relationship.

Do you disallow edits to the tables by all other means besides your stored
procedures?

Per above, edits are done through bound forms.

If not, I suggest you do. I also suggest that you add some sort of audit
logging type of mechanism so that when you next find a "shifted" record
you can see exactly when and by whom the change was made.

There is a history log in place, and it records the complete record every
time a change is made. While it shows who was editing the record when the
change was made, it doesn't explain why. The user would not take data from
an unrelated record and place it in the record they're editing, and they
usually have no knowledge of how the data got there.

The first almost-conclusion was that the user was pressing Ctrl+' and
getting data from a previous record. That almost explained it, especially
since data almost always comes from a recently-created record. However, the
order of the form is such that newer records are in front of older records.
Thus, Ctrl+' wouldn't have taken data from a previous record.

But that issue aside, I since disabled Ctrl+' anyway; yet the problem still
occurred. And, as noted here, the form used in this case didn't even contain
TableB, so the data shift couldn't have happened through the form.

Strange situation.

 

[lyle fairfield]

In other words, though the forms used in editing these records didn't
touch TableB, the records in TableB were affected. Their only
connection to the TableA records that were affected is the one-to-one
relationship.

I suspect that you can become mildly famous in the db world if you strip
everything to the bare essentials and post the (small!) db and (small!)
front end that will replicate this unusual behaviour.
I would certainly like to observe this phenomenon in action, and examine
the db and front end where it occurs.
I'm willing to host the download on my site if you can whip one up.
(I don't have the whole thread in front of me here so apologies if this has
been discussed already.)

 

[Rick Brandt]

You and me both. I'd like nothing better than to be able to replicate
this behavior. As it is now, I'm left with guesses when the user
contacts me and says that they completed certain fields, and then,
when they went back to the record sometime later, the record had data
in those field from another record -- usually one that was created a
little earlier than the one with the problem. This happens about once
or twice a month. Being able to replicate it would be wonderful.

I have one particular user who would chronically tell me that changes she
made to records would magically become undone when she would examine the
same records later. More often than not she would blame this on the fact
that she had gotten an automatic update on the front end application file.

Try as I might to convince her that an update to the front end would never
affect the data on the server she was utterly convinced. Once I put in an
audit trail table to log exactly what happened to every record and by whom
the problem has not once occurred (or at least she has not made the claim).

I realize that you already have an audit log of sorts. I just wanted to
state that when given the choice between the database engine mucking up data
and the user mucking up data the smart money is on the user. That isn't to
say that the user isn't being assisted in the crime by a flaw in the
application, but the idea that it is all happening on the server is the
least likely scenario.

 

[Neil]

I have one particular user who would chronically tell me that changes she
made to records would magically become undone when she would examine the
same records later. More often than not she would blame this on the fact
that she had gotten an automatic update on the front end application file.

Try as I might to convince her that an update to the front end would never
affect the data on the server she was utterly convinced. Once I put in an
audit trail table to log exactly what happened to every record and by whom
the problem has not once occurred (or at least she has not made the
claim).

I realize that you already have an audit log of sorts. I just wanted to
state that when given the choice between the database engine mucking up
data and the user mucking up data the smart money is on the user. That
isn't to say that the user isn't being assisted in the crime by a flaw in
the application, but the idea that it is all happening on the server is
the least likely scenario.

--

Yes, I agree. And that was the direction I was heading in. But then, in
this recent situation, where the record that was affected was, according to
the history logs, never used in a form that touches "TableB," the one with
the problem, but was only used with a form that accesses "TableA," it left
me stumped. Since the user had no access to TableB, since they were using
that other form, it seems impossible for them to have affected TableB. The
only scenario I was left with was that since TableA and TableB were joined
with a one-to-one relationship, that something was going on under the hood.
But, who knows, maybe it's "one of those things" that'll never be explained.

 

[hancjiao]

Has anyone ever had experience with data moving from one record to
another? I have a form in which I'm using the FMS rich text control
("Total Access Memo"). Several times when there has been an error (such as
the user unable to print, or other error), the user finds that data in one
or two of the rich text fields are actually data that had previously been
saved in another record. This has happened two or three times in the past
few months. And, while not a frequent problem, it is especially troubling
when it happens.

I've checked the FMS web site, and haven't seen anything about this. And
their technical support is pretty rudimentary. Not much help there. So I
was wondering if anyone has ever experienced anything like this, either
with or without a rich text control.

I'm using an Access 2000 MDB with a SQL Server 7 back end, with ODBC
linked tables.

Thanks for any assistance!

Neil

 

[Ashley]

hey, what's up.............................

Thanks for your reply, Gordon. But, unfortunately, there is no code that
updates these fields. They're simply bound controls, and are managed by
the control and the ODBC driver.

Furthermore, the fact that the contents of the control are replaced by the
content of a previously saved record in that control indicates that it
can't be user error. These controls hold multiple sentences. For the user
to be able to retype exactly what was in another record, after already
typing what was in there originally, would be extremely unlikely.

I have code that copies the entire content of the record to a separate
history table whenever the form AfterUpdate event is run. Looking at the
history table, I see exactly what the user is talking about. There will be
multiple entries with the correct paragraph of text in the field, and
then, boom!, all of a sudden, out of nowhere, the contents of the field is
replaced, character-for-character, with the contents of another record for
the same field. If the user had done this, they would have to, after
typing the paragraph in the first place, all of a sudden decide to replace
it wit the wrong paragraph of text, and then replicate that other record's
text perfectly. Extremely unlikely.

Since this is the rich textbox control by FMS, and these types of controls
tend to get a little flaky at times, I'm still believing there's something
with the control itself. Since it happens infrequently, and so far only in
conjuction with an error, I see it as a hiccup within the control,
especially since, as noted above, I don't have any code that updates these
controls, and user cause is extremely unlikely.

The fact that no one responding to this thread has ever encountered
anything like this is comforting. But, at the same time, it leaves me with
few ideas as to how to address it. Right now I'm applying the KFC
("keeping fingers crossed") approach. :-(

Thanks,

Neil

 

[Neil]

?

hey, what's up.............................

 

[kazanç evi]

Dell all our best deals
Dell toutes nos meilleurs offres
Dell alle unsere Top-Angebote
Dell todas nuestras mejores ofertas
Dell alle vores bedste tilbud
Dell kaikki parhaamme käsitellään
Dell tutte le nostre migliori offerte
???????????
???????????
?? ?? ??? ??
Dell ??? ?? ???? ?? ???? ???????

http://www1.euro.dell.com/content/default.aspx?c=fr&l=en&s=dhs&dgc=AF&cid=5676&lid=137598