HELP!!! Corrupt data table

  • Thread starter Thread starter Dale Fye
  • Start date Start date
D

Dale Fye

I've been using Access for 7 years and have never run into
this problem before.

I have a data table where the data in some records of a
memo field has become corrupt. The actual value that
shows up in the field is "#Error". Every time I try to
access this field (either through a form or in table view)
from one of the corrupt records I get the following
message, although there are not other users currently
logged into the system.

"The Microsoft JET database engine stoped the process
because you and another user are attempting to change the
same data at the same time."

Anybody know how to resolve this issue? How to get the
data back that was in those fields displaying the error?
What could be causing these errors?

There are no LDB files visible on the user or network
machines.
 
7 years with no problem: not a bad record. :-)

The table stores only a pointer to the location where the memo field is
stored. If the pointer is invalid, you get garbage. Typically this happens
when a write is interrupted (e.g. power glitch). The actual location where
the data is stored is lost, and not obviously retrievable.

If only the memo is corrupted:
1. Create a query into your table.
2. Drag all fields except the memo into the grid.
3. Change it to a Make Table query (Make Table on Query menu).
4. Run the query. Supply a new name for the table.
5. Open the new table in design view, and add the memo field.

6. Write down the primary key value for all rows that have a corrupted memo.
7. Change the query into an Update query (Update on Query meny). Answer that
you want to update teh new table created at step 4.
8. Drag the memo field into the grid.
9. Remove all other fields from the grid except the primary key.
10. Clear the Update row under the primary key (so it does not try to update
this).
11. In the Criteria row under the primary key, choose the records before the
first corrupted memo. For example, if records 28 has a corrupted memo, use
Criteria of:
<=27
It is important not to refer to the corrupted row.
11. Run the query. This updates the (blank) memo field for these records.
12. Repeat for the next group. For example if #54 is corrupted, update:
Between 29 And 53
13. Repeat until you have updated the memo field for all valid rows.

14. Locate a backup of your mdb file that does not have the corrupted memo.
15. Attach this table: File | Get External | Link
16. Create a query into the newly attached table.
17. Change it to an Update query.
18. Enter the criteria for the primary key numbers that had the corrupted
memos, e.g.:
IN (28, 54)
19. Run the query to update the table with the memo from the backup.

This gives you the valid data for all records where the data is known, and
restores the previous value of the memo field form the backup for the
corrupted rows.

After this, you will need to:
1. Break all relationships this table is invovled in (Relationship on Tools
menu).
2. Delete the old damaged table.
3. Compact the database.
4. Rename the new table to the old one.
5. Recreate the relationships.

General tips on recovering from corruption:
http://members.iinet.net.au/~allenbrowne/ser-47.html
and preventing corruption:
http://members.iinet.net.au/~allenbrowne/ser-25.html
 
Allen,

I didn't say no problems, just never encountered this one before.

Thanks, I'll give it a try when I get into the office on Friday. Let you
know how everything turns out.

Dale
 
Back
Top