Corrupted BE (Access2000)

Topic

New Reply

After having spent a bunch of hours trying to save a corrupted database, I thought I’d mention what happened, in case anyone runs up against the same problem and won’t have to waste as much time as I did.

Compacting/Repairing the database didn’t work. It would get to about 90% then seemingly the screen would flash like it normally does and the database re-opens, but the progresss bar never went away. JetComp didn’t work either, it gave an error about halfway through.
Importing all tables into another db failed also; was able to import some tables, but then got “table in use by someone else” message on others.

I finally took an empty copy of the database, and started do append queries from the old database.

Eventually I found the corrupted table. There was no longer a PK specified, and opening the table and scanning through the records, I finally found a record with #ERROR in all fields. Couldn’t delete that record, though. I tried various means to copy the good records out, but kept getting stymied. Like just selecting a bunch of record and then doing a CTL-C to copy them. The progress bar would advance, then stop, and the process would seemingly be done (but progress bar was still there).

I tried opening a recordset and using DAO to read through the records. I got some records this way, but then would get a “An Error occurred and Access must close” error message.

I finally figured out that there was another record causing problems! With the table displayed, I placed my cursor in the record in question (which seemed OK), and tabbed through fields until I got to a memo field. When I tabbed into this field, I got a message something to effect “The field is too big to be edited”! I deleted this record, found another record with same problem and deleted it, and was finally able to then continue to extract the records up to and then after the #ERROR record.

I guess I got out all the data just in time, because then I could no longer find the code I had written to help diagnose and extract the data.

Of course, what made it really bad was that I had to do all this with a splitting headache after a trip to the dentist that morning!

Reply | Quote

Replies

You should have asked the dentist to drill out the rotten records too!

Reply | Quote

>>You should have asked the dentist to drill out the rotten records too! <<

After about 4 hours of screwing around with this, I would have accept help from a faith healer!

Reply | Quote

Mark,
I think this post serves as a reminder for troubleshooting techniques, as well as giving some options….I’m going to bookmark it just to have a few procedures to try before asking questions….

Thanks for the tips

Reply | Quote

This was the first time I’d ever hit that problem with the memo field. Hopefully my experience will help someone else, if they start running into these kinds of problems.

Reply | Quote

A technique that has been successful for me is to create an empty copy of the table and then create an append query that doesn’t bring over the memo fields, leaving you with all the records but none of the memo fields. Then an update query on the memo fields with the two table joined on their primary keys will usually bring over everything except the corrupted memo fields. Sometimes you just have to skip the records that have corrupt memo fields and only update the rest.

Reply | Quote

My big problem was that I didn’t know I even had a memo field problem for most of the day!

While the technique you suggested seemingly should have worked, I’m not sure it would have worked in this situation. It seems that I was getting an error when one of those records was in a query, whether or not the memo field itself was being used; I think at one point I was just trying to extract the field containing the unique ID and still had problems. Of course, the error might have been caused by the bad #ERROR record, so who knows. But checking for corrupted memo fields will definitely be high-up on my agenda now when I have db problems.

Reply | Quote

Since the memo “field” doesn’t really exist in the table, the pointer can go corrupt, especially if you get an occasional network connection drop. There’s usually a fair amount of hand tweaking rerquired when you get corruption in a memo field like that, so you generally try a variety of things to find one that works in that particular situation. Believe me, I’ve used them all.

Reply | Quote

I’m probably preaching to the choir here, but this is another argument for using SQL Server as your back-end when you can. And Microsoft has made it easier with SQL Server 2005 Express. Memo fields are one of the most common places for Access data to go corrupt – forms and reports are also stored in a similar data structure, thus their propensity for corruption. Memo fields can also be an issue in SQL Server on rare occasions, but you have much larger text fields (8K) in SQL Server so you don’t need the memo structure as often.

Reply | Quote

Yet another idea…

I this place.

Reply | Quote

Lots of good information already posted, but I thught I could add. I have had the same problem on a few occaisions and at work we had a software recovery tool called easy recovery. It opened and recovered the databases very easily. I don’t know what the cost of the product is, but it has a very straight forward interface and I have used it on 4 different occasions and it worked when compact and repair and jet compact would not. Wondering if anyone else has used this tool?

Carla

Carla

Reply | Quote