Results 1 to 11 of 11
  1. #1
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Corrupted BE (Access2000)

    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!
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Corrupted BE (Access2000)

    You should have asked the dentist to drill out the rotten records too! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  3. #3
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Corrupted BE (Access2000)

    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.... <img src=/S/clever.gif border=0 alt=clever width=15 height=15>

    Thanks for the tips <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Corrupted BE (Access2000)

    >>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!
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Corrupted BE (Access2000)

    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.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Corrupted BE (Access2000)

    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. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

  7. The Following User Says Thank You to charlotte For This Useful Post:

    hasse (2011-02-07)

  8. #7
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Corrupted BE (Access2000)

    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.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  9. #8
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Corrupted BE (Access2000)

    Yet another <img src=/S/clever.gif border=0 alt=clever width=15 height=15> idea... <img src=/S/salute.gif border=0 alt=salute width=15 height=20>

    I <img src=/S/love.gif border=0 alt=love width=15 height=15> this place. <img src=/S/yep.gif border=0 alt=yep width=15 height=15>
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  10. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Corrupted BE (Access2000)

    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. <img src=/S/sad.gif border=0 alt=sad width=15 height=15>
    Charlotte

  11. #10
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Corrupted BE (Access2000)

    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.
    Wendell

  12. #11
    4 Star Lounger
    Join Date
    Sep 2004
    Location
    Sacramento, California, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Corrupted BE (Access2000)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •