Corrupt Table & compact/repair not working

[Guest]

[I apologize in advance if this got posted twice - my browser erred the first
time]

I have an MDB that appears to work fine with the exception of one table - if
I try to access the table beyond 1500ish records is abruptly closes access.
I *think* I know it's this table because I imported all my objects into a new
MDB - and that table won't go.

I can't compact and repair, I can't export it, I can't copy from it. I
minimally improved the situation when I removed all the indexes from the
table; I can at least browse the whole table now but still can't
compact/repair, export, copy from, ...

Any thoughts on how to salvage this data?

Thanks
David

 

[John W. Vinson]

[I apologize in advance if this got posted twice - my browser erred the first
time]

I have an MDB that appears to work fine with the exception of one table - if
I try to access the table beyond 1500ish records is abruptly closes access.
I *think* I know it's this table because I imported all my objects into a new
MDB - and that table won't go.

I can't compact and repair, I can't export it, I can't copy from it. I
minimally improved the situation when I removed all the indexes from the
table; I can at least browse the whole table now but still can't
compact/repair, export, copy from, ...

Any thoughts on how to salvage this data?

Create a new, empty database. Use File... Get External Data... Import to
import all the tables EXCEPT this one, along with all your forms, queries,
reports etc.

Create a new table with the same field definitions.

Use File... Get External Data... Link to *link* to the damaged table (don't
import it!)

Then run two or more append queries to append all the records *except* the
corrupt record. E.g. if you have an Autonumber ID, and can determine that
viewing the record with ID 1508 is the corrupt one, use a criterion

<= 1507 OR >= 1509

to select all the other records. Avoid anything that would "touch" the corrupt
record.

See http://www.granite.ab.ca/access/corruptmdbs.htm for a thorough discussion
of database corruption, its prevention and cure.

John W. Vinson [MVP]

 

[Guest]

At times like this, nothing beats a good backup. In fact make a complete
backup of your database now and put it away for safe keeping.

Question: Does that table have a memo field? If so you just might be in
luck. Below is an extract from a paper I wrote also listed below:

Memo and OLE Object fields in tables.
An Access record can only be about 2,000 characters total. A memo field can
be 64K and OLE Object, such as a bitmap, can be very large. The trick is that
these fields aren’t actually stored in the table. Rather, they are linked
from a hidden table. Therefore, memo and OLE objects are readily corrupted.
If you are still having problems, check your Memo and OLE fields at the
table level. You need to find which record(s) is causing the problem. In
addition, if you have linked tables, you need to check each linked table
until you find which one has the bad record.
Check which fields in the table are memos and then scroll down through all
of the records in that field. You will get an error when you find the bad
field.
If you have many records, you could try using queries to check a few records
at a time until you narrow the field. Don’t be surprised if there is more
than one bad record.
Once you find the bad record(s), copy and paste the table's structure only.
Next created an append query and moved all the records except for the bad
one(s) to the new table.
Then modify the append query to include only the bad record(s) but excluded
the memo field.
If this works you will be able to recover the table with only one field of a
few records missing.
You may need to redo the table relationships in the Relationship window.

Here's some code that can help find the bad records fasts thanks to Tony
Toews:

http://www.granite.ab.ca/access/corruption/corruptrecords.htm#Locatingdamageviacode

Below are some other resourses.

Tony Toews has an excellent web page on database corruption.
http://www.granite.ab.ca/access/corruptmdbs.htm

Allen Brown also has excellent info on corruption.
http://allenbrowne.com/ser-47.html

I have a white paper in a Word document named Fix Corrupt Access Database
towards the bottom this page:
http://www.rogersaccesslibrary.com/OtherLibraries.asp
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

[I apologize in advance if this got posted twice - my browser erred the first
time]

I have an MDB that appears to work fine with the exception of one table - if
I try to access the table beyond 1500ish records is abruptly closes access.
I *think* I know it's this table because I imported all my objects into a new
MDB - and that table won't go.

I can't compact and repair, I can't export it, I can't copy from it. I
minimally improved the situation when I removed all the indexes from the
table; I can at least browse the whole table now but still can't
compact/repair, export, copy from, ...

Any thoughts on how to salvage this data?

Thanks
David

 

[Guest]

Yep, that's what I had to do. Thanks.

[I apologize in advance if this got posted twice - my browser erred the first
time]

I have an MDB that appears to work fine with the exception of one table - if
I try to access the table beyond 1500ish records is abruptly closes access.
I *think* I know it's this table because I imported all my objects into a new
MDB - and that table won't go.

I can't compact and repair, I can't export it, I can't copy from it. I
minimally improved the situation when I removed all the indexes from the
table; I can at least browse the whole table now but still can't
compact/repair, export, copy from, ...

Any thoughts on how to salvage this data?

Create a new, empty database. Use File... Get External Data... Import to
import all the tables EXCEPT this one, along with all your forms, queries,
reports etc.

Create a new table with the same field definitions.

Use File... Get External Data... Link to *link* to the damaged table (don't
import it!)

Then run two or more append queries to append all the records *except* the
corrupt record. E.g. if you have an Autonumber ID, and can determine that
viewing the record with ID 1508 is the corrupt one, use a criterion

<= 1507 OR >= 1509

to select all the other records. Avoid anything that would "touch" the corrupt
record.

See http://www.granite.ab.ca/access/corruptmdbs.htm for a thorough discussion
of database corruption, its prevention and cure.

John W. Vinson [MVP]

 

[Guest]

Thanks Jerry, some of this was very helpful, too.

Removing the indexes and primary key from the table offered some relief.

At times like this, nothing beats a good backup. In fact make a complete
backup of your database now and put it away for safe keeping.

Question: Does that table have a memo field? If so you just might be in
luck. Below is an extract from a paper I wrote also listed below:

Memo and OLE Object fields in tables.
An Access record can only be about 2,000 characters total. A memo field can
be 64K and OLE Object, such as a bitmap, can be very large. The trick is that
these fields aren’t actually stored in the table. Rather, they are linked
from a hidden table. Therefore, memo and OLE objects are readily corrupted.
If you are still having problems, check your Memo and OLE fields at the
table level. You need to find which record(s) is causing the problem. In
addition, if you have linked tables, you need to check each linked table
until you find which one has the bad record.
Check which fields in the table are memos and then scroll down through all
of the records in that field. You will get an error when you find the bad
field.
If you have many records, you could try using queries to check a few records
at a time until you narrow the field. Don’t be surprised if there is more
than one bad record.
Once you find the bad record(s), copy and paste the table's structure only.
Next created an append query and moved all the records except for the bad
one(s) to the new table.
Then modify the append query to include only the bad record(s) but excluded
the memo field.
If this works you will be able to recover the table with only one field of a
few records missing.
You may need to redo the table relationships in the Relationship window.

Here's some code that can help find the bad records fasts thanks to Tony
Toews:

http://www.granite.ab.ca/access/corruption/corruptrecords.htm#Locatingdamageviacode

Below are some other resourses.

Tony Toews has an excellent web page on database corruption.
http://www.granite.ab.ca/access/corruptmdbs.htm

Allen Brown also has excellent info on corruption.
http://allenbrowne.com/ser-47.html

I have a white paper in a Word document named Fix Corrupt Access Database
towards the bottom this page:
http://www.rogersaccesslibrary.com/OtherLibraries.asp
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

[I apologize in advance if this got posted twice - my browser erred the first
time]

I have an MDB that appears to work fine with the exception of one table - if
I try to access the table beyond 1500ish records is abruptly closes access.
I *think* I know it's this table because I imported all my objects into a new
MDB - and that table won't go.

I can't compact and repair, I can't export it, I can't copy from it. I
minimally improved the situation when I removed all the indexes from the
table; I can at least browse the whole table now but still can't
compact/repair, export, copy from, ...

Any thoughts on how to salvage this data?

Thanks
David