Is there a problem with stability when one uses too many text (memo) fields?
I'm having a problem with data from one record occasionally ending up in
another record, though apparently not through any user interaction.
I'm using SQL 7 and accessing the tables through the ODBC driver. There are
two tables with a one-to-one relationship -- TableA and TableB. TableB is
the one that's having this occasional problem. TableB has about 30 text
fields out of a total of about 50 fields in the table. The problem is
usually with one or two text fields containing data from a different record,
one that was created close to when the problem record was created.
Example: TableB has fields 1-9, say. In one record, field 1 has A, field 2
as B, field 3 has C, and so on.
In another record (created shortly after the other one), field 1 has AA,
field 2 has BB, field 3 has CC, and so on.
The user works with the records, everything's fine. Then one day the user
notices that in the second record, field 1 has AA, field 2 has BB, but field
3 has C instead of CC. In other words, all data's fine, except for one,
maybe two fields, that have data from a previously-created record.
At first glance this seems to be a user-interaction thing, that somehow the
user inadvertantly placed data from the older record into the newer one,
either through a shortcut, or by having that data on the clipboard, or
whatever. But a recent incident opposes that theory.
I have two forms in the front end for editing records (the forms are bound
to the ODBC table links). Form1 is bound only to TableA (the one that
doesn't have the problem); Form2 is bound to a query that is TableA joined
with TableB.
In the recent incident where data shifted, both the record that was affected
and the record from which the data came were both only edited with Form1. In
other words, TableB never came into play; yet its data was somehow affected.
When a record is created, the user completes a few fields in a form, and
then a stored procedure creates a record in TableA and then a sister record
in TableB (using the TableA record's autonumber primary key as its primary
key). A couple of user entered values are entered into the TableB record.
But if the user is using Form1, they never see the TableB record.
In this case, the TableB record's two fields got changed to fields from an
earlier record (one which was created a little earlier the same day), even
though both records were only edited in Form1 (according to the history
log), which doesn't touch TableB.
Thus, I'm wondering if there's a possibility that either the SQL database or
the ODBC driver somehow shifted the data from one record into another. That
seems far-fetched. But, at this point, since a table that the user didn't
touch somehow had its data changed to data from a different record, I'm
trying to explore all possibilities.
Thanks for any insight!
Neil
