Access 2002 corruption

[Chuck Van Den Corput]

I have deployed an Access 2002 app in the standard way: individual
A2002 front-ends, one shared A2002 back-end.

A corruption occurred in a particular table in the back-end. I saw a
row whose fields were all #Deleted. I was unable to delete this row. I
did a compact/repair which moved this corrupted record to the very top
of the table, substituting the #Deleted's with ######'s. I was able to
delete this row.

From this point on, I witnessed some odd behaviour. Let's the say
table was called tblA.

* Going directly into tblA, I was able to edit and add records.

* Via a joined query between tblA and tblB (attempted in both the FE
and BE), I was *not* able to edit the contents of tblA. By changing
the query type from Dynaset to Dynaset (Inconsistent Updates) I was
able to.

I tried creating a new copy of the BE by importing all the tables from
the once-corrupt BE and tblA still presents the problems cited above.

Something has happened to tblA that continues to linger, even now that
the corrupted record has been removed and the BE compacted/repaired.

I am at a loss as to what to try next. Any advice?

[bruce]

Chuck Van Den Corput wrote:

I have deployed an Access 2002 app in the standard way: individual
A2002 front-ends, one shared A2002 back-end.

A corruption occurred in a particular table in the back-end. I saw a
row whose fields were all #Deleted. I was unable to delete this row. I
did a compact/repair which moved this corrupted record to the very top
of the table, substituting the #Deleted's with ######'s. I was able to
delete this row.

From this point on, I witnessed some odd behaviour. Let's the say
table was called tblA.

* Going directly into tblA, I was able to edit and add records.

* Via a joined query between tblA and tblB (attempted in both the FE
and BE), I was *not* able to edit the contents of tblA. By changing
the query type from Dynaset to Dynaset (Inconsistent Updates) I was
able to.

I tried creating a new copy of the BE by importing all the tables from
the once-corrupt BE and tblA still presents the problems cited above.

Something has happened to tblA that continues to linger, even now that
the corrupted record has been removed and the BE compacted/repaired.

I am at a loss as to what to try next. Any advice?

I assume that you were able to update tblA via a join query _before_
the corruption happened...

If this is a corruption issue, I'd create a new empty copy of the table
from scratch, i.e. duplicate the design manually in table design view
or via VBA or SQL, and then copy the records from the old to the new
copy (again, by VBA or SQL, or by exporting to an external file and
back again) and see if you're still having the same problem. Even
better still, I'd create this 'identical' copy of tblA in a fresh .mdb
file, and copy all of the other necessary tables to the new .mdb file
as well and see if the problem persists.

HTH,
Bruce

[Chuck Van Den Corput]

>I assume that you were able to update tblA via a join query _before_

the corruption happened...
Yes.
If this is a corruption issue, I'd create a new empty copy of the table
from scratch, i.e. duplicate the design manually in table design view
or via VBA or SQL, and then copy the records from the old to the new
copy (again, by VBA or SQL, or by exporting to an external file and
back again) and see if you're still having the same problem. Even
better still, I'd create this 'identical' copy of tblA in a fresh .mdb
file, and copy all of the other necessary tables to the new .mdb file
as well and see if the problem persists.

Great advice. Here's what I did that worked. Not sure if any of these
steps were unnecessary.

1. created an empty database
2. copied in tblA from a corrupt-free backup of database
3. emptied tblA
4. copied in tblA from corrupted database, calling it tblB
5. loaded contents of tblB into tblA, including AutoNumber field (this
was important because of tblA's parent-child relationship with other
tables)
6. deleted tblA from corrupted database
7. imported "good" tblA from new database

Thanks for suggestion.
Chuck