Data Shifting Between Records - 2

[Neil]

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.

Thanks!

Neil

[Rick Brandt]

Neil wrote:

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.

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.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[Neil]

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.

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.

[lyle fairfield]

"Neil" <no****@nospam. netwrote in
news:B0******** ***********@new ssvr14.news.pro digy.net:

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.)

[Neil]

"lyle fairfield" <ly******@yah00 .cawrote in message
news:Xn******** *************** *@216.221.81.11 9...

"Neil" <no****@nospam. netwrote in
news:B0******** ***********@new ssvr14.news.pro digy.net:

>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.)

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.