Re: HELP!!! Corrupt data table

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Allen Browne (AllenBrowne_at_SeeSig.Invalid)
Date: 10/06/04 

Date: Wed, 6 Oct 2004 22:39:48 +0800

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 Browne - Microsoft MVP.  Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Dale Fye" <dale.fye@nospam.com> wrote in message
news:33ff01c4abae$da0b2840$a501280a@phx.gbl...
> 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.

Relevant Pages

  • Re: Memo field truncates on report
    ... >a Memo data type is always truncating when running the ... >to output the query results to that table. ... >report off of that table, which I populated by running the ... using the memo field in such a way that Access has to ...
    (microsoft.public.access.reports)
  • Re: HELP!!! Corrupt data table
    ... > The table stores only a pointer to the location where the memo field is ... Create a query into your table. ... Open the new table in design view, and add the memo field. ... > General tips on recovering from corruption: ...
    (microsoft.public.access.formscoding)
  • TransferSpreadsheet still truncates the memo field
    ... I am trying to export a query result as a comma delimited text file on ... When I use the export wizard, it trims the memo field down to 256 ... fields are still exported up to 256 characters. ...
    (microsoft.public.access.externaldata)
  • Re: Crosstab Memo
    ... Firstly, you cannot perform aggregate functions on the memo, so presumably ... Typically when a Text or Memo field appears as strange characters, ... View, on View menu, in query design.) ...
    (microsoft.public.access.queries)
  • Re: Upgrade to Access 2007?
    ... Can you leave the memo field out of the query on one side? ... database is compacted and repaired every day. ... Tony Toews has an excellent web page on database corruption. ...
    (microsoft.public.access.tablesdbdesign)