Data Shifting Between Records

  • Thread starter Thread starter Neil
  • Start date Start date
Neil said:
I previously posted about data shifting between records in my Access
2000 MDB with a SQL Server 7 back end, using ODBC linked tables.
Every once in a while, data from one record mysteriously appears in
another record. This incident happened again, this time adding a new
wrinkle to the situation.
There are two tables -- TableA and TableB -- which have a one-to-one
relationship with each other, joined on TableA's autonumber primary
key field to TableB's long int primary key field.

And there are two forms that are used by the users. Form1 is bound to
TableA alone. Form2 is bound to TableA joined with TableB.

TableB is the table that has the problem with data shifting. However,
in the most recent episode of the data shifting, the record in
question was only edited using Form1 (which is only bound to TableA),
and not Form2 (which is bound to both TableA and TableB). Thus, it
would not have been possible for the data to shift through user
intervention, or even though anything within the form, but only
through some other mechanism.
A record is created by the user clicking a New button and completing
a few fields. Code in the back end then creates the TableA record
with the user-provided data, getting the new autonumber value. It
then creates a sister record in TableB, using the new autonumber
value as the PK for the TableB record, and completing two fields in
TableB that are required, based on user-entered data.

In this case, as noted, after the record was created, according to the
history logs, the user only used Form1, which only accesses TableA.
Yet somehow the two fields that were completed by default when the
TableB record was created in the back end were changed to contain
data from a different record. Since TableB was never accessed by the
user of either record, I don't see how that could be possible except
through some glitch in the back end or ODBC driver.

TableB It contains 20 memo type fields ("text" type in SQL Server)
that are used to store RTF data, along with about 30 or so other
fields. It could be that the large number of memo fields is creating
a problem?
Any thoughts or ideas would be appreciated.
Click to expand...

You have, based on your description, an unusual and rather convoluted method
for entering your records and simultaneously suffer from an unusual and
convoluted problem with your data.

I suggest that eliminating the former might also eliminate the latter.
 
You have, based on your description, an unusual and rather convoluted
method
for entering your records and simultaneously suffer from an unusual and
convoluted problem with your data.

I suggest that eliminating the former might also eliminate the latter.
Click to expand...

What, specifically, is "unusual and convoluted"? The user enters a few
required pieces of information; and records are created in the back end via
stored procedure in both the main and sister table, which share a one-to-one
relationship. What's convoluted about that?

Furthermore, the problem happens long after the record is created. At some
point in the editing process some data (though not all) in one record is
replaced with data in another record. This happens well after the records
are created.
 
Neil said:
What, specifically, is "unusual and convoluted"? The user enters a few
required pieces of information; and records are created in the back
end via stored procedure in both the main and sister table, which
share a one-to-one relationship. What's convoluted about that?

Furthermore, the problem happens long after the record is created. At
some point in the editing process some data (though not all) in one
record is replaced with data in another record. This happens well
after the records are created.
Click to expand...

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.

Do you disallow edits to the tables by all other means besides your stored
procedures? 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.
 
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.
Click to expand...

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?
Click to expand...

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.
Click to expand...

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.
 
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.
Click to expand...

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.)
 
lyle fairfield said:
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.)
Click to expand...

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.

And, again, in case you missed it, there are two tables with a one-to-one
relationship, and two forms -- one that is bound to only one of the tables,
and another that is bound to both. According to the history logs, it was the
form that is only bound to one table (the one without the problem) that was
used both with the problem record and with the record the wrong data came
from. So the table in which this occurred was not even a part of the form
that was used with these records. And no one accesses the tables themselves
except me.
 
Neil said:
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.
Click to expand...

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.
 
Rick Brandt said:
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.

--
Click to expand...
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.
 
Neil said:
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
Click to expand...
 
hey, what's up.............................
Neil said:
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
Click to expand...
 
Jan Baird is out of the country until September 20. Every effort will be
made to respond to messages, but please be patient.
 
Back
Top