Unanswered: Help --> Corrupt table -- cannot / will not repair
I've got a crazy problem that I hope someone has seen before. My description is rather long, so please be patient.
BE/FE data structure
BE is on a server, FE on each users desktop
Last week a user reported a problem opening a screen.
Upon closer examination, I found a single row in a table that had "#ERROR" in every field. The BE table would open OK, but
when this record scrolled to the screen, I would get all sorts of wierd behavior. I could not select and/or delete the offending record.
First Repair attempt:
So...I closed the database and used LDBVIEW to make sure no one else was using it. I then opened it back up and tried "Tools - Database Utilitles - Compact / Repair". Didn't work!
The repair process (which you all know usually blanks the screen and shows the progress meter) would hang about halfway through and put the database window back into view. The other tables would open but any attempt to close a datasheet view screen would generate the error "This will reset the code in break mode" so it appears the repair was still attempting to run.
Second Repair attempt:
Since the regular method failed, I decided to make a new empty database and import everything from the bad one. Seems straight forward right? Well, as soon as the process got the table with the bad row, it would fail again with all sorts of oddities.
Third repair attempt:
Copy the BE data file to a new file name. Perhaps disk error is the problem. Retried all aforementioned repair techniques using the new copy. All failed.
So I restored a backup BE file from several days prior to the offending record. The forms started working again and the user simply re-entered the few new records that were missing....
--- Think the problems over? Think again ---
Last night I decided to open the BE data file and compact it. The same behavior mentioned above re-surfaced.
Now remember, this file has been restored from backup and the offending record (that caused the problem above) does not exist.
Here is everything I have done so far TODAY (2/7/02): (not to mention the things already mentioned above)
1.) Make a copy of the bad BE datafile onto my local machine
2.) Create a new empty database and try to import all objects -> fail
3.) Delete all objects from the new database and re-import all EXCEPT the table that had the problems above -> success
4.) Compact / Repair the new database to check integrity -> success
5.) Open the bad database and try to export (structure and data) that table to the new database -> fail
6.) Open the bad database and try to export (structure only) that table to the new database -> success
7.) return to #4 to check integrity again -> success
8.) Rename table in new database and "LINK" to the bad table. Run append query to move data from bad table to new table -> fail.
(the query would run successfully, but the new table would remain empty)
9.) Open the bad database and try to send the table to a mail recipient as "MS Excel" attachment. When finished, Excel would report that the file was invalid.
10.) Open the bad database and scroll through each record in datasheet view -> success (no errors of any kind)
11.) Open bad table in design view and insert new field right in the middle (to try and cause a rewrite) -> success
12.) Return to step #10 -> success (no errors of any kind)
13.) Return to steps 6,7,8 -> fail (new table remains empty)
14.) go get another cup of coffee, I'm outa' ideas........................
The only thing I have not tried is to write code the step through the bad table and try to move data to the good table.
If anyone has seen this before, please let me know. Thanks.
I managed to isolate and repair the problem. Here's how.
(read the first post to catch up)
I went ahead and tried to export the table to an excel spreadsheet. The export went OK until it was about 90% complete. Then it hung.
After closing Access I opened the new spreadsheet and found that it had stopped at a certain record. It was about 90% complete as far as record count was concerned (the table has 7092 records).
So the error must be the next record. So I created a query to effectively skip this record (include everything under it and then everything over it) and tried to export again. This time it made it further and stopped again.
So I modified my query to now skip yet another record. This time it worked all the way through. So I know exactly which two records are causing the problem. So I made a replica of the table (structure) and tried to copy each record and past-append to the replica table. Access crashed.
After rebooting, I tried again to copy the records to the new table, but access crashed again as my cursor got to a specific field to copy.
VIOLA!!!!! It turns out that these two records had munged memo fields!
So I copied everything EXCEPT the memo field (from these records) to the replica table, then I deleted them from the main table. Now I can copy the records from the replica and past-append back to the main table. Delete the replica.
Problem fixed (and the Compact/Repair now works correctly).