Looking to take your VBA skills further?...

Discover twinBASIC — a powerful new development platform that expands on VBA and VB6 with advanced features, modern tools, and enhanced compatibility. Perfect for those ready to elevate their projects or transition from VBA, twinBASIC lets you build on what you already know and take your applications to the next level!

Try out twinBASIC Community Edition - it's free!

Steps to recover from Access database corruption

        1 votes: *****     2,19 views      No comments
by Allen Browne, 20 May 2009    (for Access v2+)

Microsoft Access Tips for Serious Users

Provided by Allen Browne, March 2008.  Last updated: April 2010.


Recovery sequence

There are many causes for corruption, and recovery steps for specific symptoms, but here is a standard sequence that deals with the most common forms of corruption in an Access database.

Try this sequence in order:

  1. Backup.
    Make a backup copy of the bad database, without overwriting any existing backups. Whatever has gone wrong, you don't want to make it worse, and a backup gives you multiple attempts at recovery.
  2. Uncheck the Name AutoCorrect boxes.
    In Access 2007:  Office Button | Access Options | Current Database.
    In previous versions:  Tools | Options | General.
    For an explanation of why, see Failures caused by Name Auto-Correct.
  3. Compact.
    In Access 2010:  Compact and Repair Database on the Database Tools ribbon.
    In Access 2007:  Office Button | Manage | Compact/Repair.
    In previous versions:  Tools | Database Utilities | Compact/Repair.
  4. Decompile.
    Close Access, and enter something like this at the command prompt:
        "c:\Program Files\Microsoft office\office\msaccess.exe" /decompile "c:\MyPath\MyDatabase.mdb"
    Use the path to where msaccess.exe is found on your computer, and include the quotes.
  5. Compact again.
    Open Access (holding down the Shift key if you have any startup code), and compact again.
    Do this twice: one compact does not work in all versions (unless you close Access between step 4 and 5.)
  6. Set minimal references.
    Open a code window.
    Choose References from the Tools menu.
    Uncheck any references you do not need.
    For a list of the ones you typically need in your version of Access, see Solving Problems with Library References.
  7. Compile.
    Still in the code window, choose Compile from the Debug menu.
    Fix any errors, and repeat until the code compiles okay.
  8. Set code options.
    Still in the code window, choose Options on the Tools menu.
    On the General tab, make sure Error Trapping is set to "Break on Unhandled Errors", and Compile On Demand is unchecked.

At this point, you should have a database where:

  • the Name-AutoCorrect errors are gone,
  • the indexes are repaired,
  • inconsistencies between the text- and compiled-versions of the code are fixed,
  • reference ambiguities are resolved,
  • the code syntax is compilable, and
  • the VBA options are set to show errors and avoid this kind of corruption.

If you are still stuck, try the undocumented SaveAsText/LoadFromText.

Hope this helps give your sick database a quick recovery.


Home Index of tips Top

Rate this article:  Your rating: PoorYour rating: Not so goodYour rating: AverageYour rating: GoodYour rating: Excellent


This is a cached tutorial, reproduced with permission.

Have your say - comment on this article.

What did you think of 'Steps to recover from Access database corruption'?

No comments yet.

Why not be the first to comment on this article?!

Have your say...

Name
E-mail (e-mail address will be kept private)
Comments


Comments require approval before being displayed on this page (allow 24 hours).