Table corruption in Access 97 
Author Message
 Table corruption in Access 97

I have had a strange occurrence which I am broadcasting to see if
anyone else has encountered this.  The situation: I have a pair of
databases, one for code, one for data.   I wrote a sub in the code
database  which reads a master file sequentially and for each record
accesses 4 other tables by means of findfirst/movenext until
primarykey.nomatch, in  which the master file primary index data forms
either a foreign key or a part of the composite primary key.  Data
from these attached tables is stored in an output table to flatten out
a bunch of 1toM relationships for reporting purposes.

Routine checking revealed that the output file was missing data from
one of these relationships.  I stepped through the code and discovered
that there were three pertinent records with matching key data; I used
a findfirst on the subsidiary table to position myself at the
appropriate key value, which worked fine.  I manipulated the data, and
issued a movenext command.  To my surprise, the next record presented
was not the next one in the table as shown in table view, but the one
just after it.  There was no way to make Access read the intervening
record.  Nowhere else in the process did this happen:  all other
master file records were matched with all their corresponding
subordinate records except for this single record.

After scratching my head, and numerous repair/compacts, I eventually
recreated the table by pulling the data off with a make-table query,
after which I deleted the original table, reset the primary index, and
recreated the relationships.  I was then able to access this record.

My question is:  how much care need I take to guard against this?
While the consequences in this case were not too serious, there are
times when missing a record inappropriately could cause serious
problems.  Is this a potential problem with attached tables?  I would
appreciate hearing from others about this.
Peter L. Reader, posting from sunny Portland, Oregon

---------------



Thu, 13 Jul 2000 03:00:00 GMT  
 Table corruption in Access 97

The issue here might be that you can NEVER assume the order of a recordset
unless you explicltly sort it yourself. You can certainly never count on it
being in datasheet view order, ever (ask anyone working with combo boxes who
don't sort their data about that!).

Michael

Quote:

>I have had a strange occurrence which I am broadcasting to see if
>anyone else has encountered this.  The situation: I have a pair of
>databases, one for code, one for data.   I wrote a sub in the code
>database  which reads a master file sequentially and for each record
>accesses 4 other tables by means of findfirst/movenext until
>primarykey.nomatch, in  which the master file primary index data forms
>either a foreign key or a part of the composite primary key.  Data
>from these attached tables is stored in an output table to flatten out
>a bunch of 1toM relationships for reporting purposes.

>Routine checking revealed that the output file was missing data from
>one of these relationships.  I stepped through the code and discovered
>that there were three pertinent records with matching key data; I used
>a findfirst on the subsidiary table to position myself at the
>appropriate key value, which worked fine.  I manipulated the data, and
>issued a movenext command.  To my surprise, the next record presented
>was not the next one in the table as shown in table view, but the one
>just after it.  There was no way to make Access read the intervening
>record.  Nowhere else in the process did this happen:  all other
>master file records were matched with all their corresponding
>subordinate records except for this single record.

>After scratching my head, and numerous repair/compacts, I eventually
>recreated the table by pulling the data off with a make-table query,
>after which I deleted the original table, reset the primary index, and
>recreated the relationships.  I was then able to access this record.

>My question is:  how much care need I take to guard against this?
>While the consequences in this case were not too serious, there are
>times when missing a record inappropriately could cause serious
>problems.  Is this a potential problem with attached tables?  I would
>appreciate hearing from others about this.
>Peter L. Reader, posting from sunny Portland, Oregon

>---------------



Fri, 14 Jul 2000 03:00:00 GMT  
 Table corruption in Access 97

On Mon, 26 Jan 1998 07:24:37 -0800, "Michael Kaplan"

Quote:

>The issue here might be that you can NEVER assume the order of a recordset
>unless you explicltly sort it yourself. You can certainly never count on it
>being in datasheet view order, ever (ask anyone working with combo boxes who
>don't sort their data about that!).

>Michael

Agreed -- but I was trying to traverse the recordset in primary key
order, which happened also to be the datasheet view order.  If you
can't trust a movefirst/readnext in primary key order??????
Peter L. Reader, posting from sunny Portland, Oregon

---------------


Fri, 14 Jul 2000 03:00:00 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. database corruption of access 97 from windows 2000

2. Access 97: Module corruption

3. Access 97 DB Corruption Recurring Problem

4. Access 97 Database Corruption

5. Corruption, Access 97 & Visual Basic 32-Bit

6. Corruption, Access 97 & Visual Basic 32-Bit

7. Access 97 Database Corruption

8. Access 97/ 2000 Database corruption

9. vba code to copy table from access 97 to excel 97

10. Saving a Word 97 Document in a Access 97 table

11. Can you pass a table to Word 97 from Access 97

12. Import from Excel 97 to Access 97 table

 

 
Powered by phpBB® Forum Software