Fixing corrupt Access files

Microsoft Access, whilst in the Poorhouse's view is often a superfast and supereasy way to get useful results from data sources, is kind of notorious for buckling under pressure. Some of this might be elitist "it makes it easy therefore it can't be any good" type nonsense, but on the other hand it most certainly get a little weak at times when several people are accessing large amounts of data with it. Whilst most often it recovers well and the only serious effect is slowness and annoyance, rarely the database .mdb file itself can become corrupt and, ostensibly, unusable.

Symptoms can include anything from it not opening at all, it only opening in "bypass startup stuff mode" (hold the shift key down whilst double-clicking the .mdb file to open it to try this), it giving out piles of fatal errors for no good reason that cause it to shutdown and much much more. Recently, the Poorhouse found one that kept saying "The form name 'Form1' you entered in either the property sheet or a macro is misspelled or refers to a form that doesn't exist". Sure enough, it did refer to a form that doesn't exist, but, no matter how hard one tried to remove all references to it, it wouldn't do so and became trapped in a loop of "you can't remove this reference because it doesn't exist but you should remove it because it doesn't exist" type Microsoft logic.

Here, therefore, are a few tips and tricks that should it happen to you might allow you to get the database back into good working order. Between them they have fixed pretty much all problems the Poorhouse has encountered, but if they don't help you then a restore-from-backup - because of course we all keep backups right? - might be your only option.

If you can load the database file at all:

  • try the built-in "Compact and Repair" option. Once loaded, go to the "Tools" menu, choose "Database Utilities", then "Compact and Repair Database". It will do its best to obey your command and restart a nice new fixed version of your database for you.
  • If it has significant VBA code in it, recompile it all via loading your database, choosing the Tools -> Macro -> Visual Basic Editor menu options. Pick the Debug menu in that screen, and choose the Compile option.

If it won't open at all:

  • Attempt to start it whilst bypassing all the startup code. To do this, locate the database file on your computer and hold down the Shift key. Double click the file. Access should load the file and dump you straight into a nice view of the Database window.
  • Try starting it with the undocumented "decompile" option. To do this, you need to go to the command line prompt (Start -> Run -> cmd -> OK in Windows XP) and load your database in the following way.
    msaccess.exe /decompile nameofyourdatabasefile.mdb

    where quite obviously replace nameofyourdatabasefile with the name of your database file. Shocks.
  • Import all the database objects into a fresh file. Create a new empty database, and go to the File -> Get External Data -> Import option. Locate the damaged file and all being well it will ask you what bits to import. Go through the tabs selecting everything you want. Hopefully it can all be imported into this nice new file ready for action. If not, it may be worth trying to import the objects one at a time, in the hope that you can rescue at least most of your uber-important data stuff.

Sometimes it seems to take multiple attempts at several of these options in several different orders to work...but so far between them all pretty much all the Poor-databases have been rescued. Until the next time it got corrupt.

Of course nothing beats a good, safe and regular backup. Except a holiday.


Comments

great tips

It is a great tips, easy-to-use and useful, Thanks.

A tool that might be useful

Hi,

Thanks for the post, it is very useful. Besides, somethimes we can't handle the database recovery by ourselves and we can try some tools to recover. I think a utility called Advanced Access Repair is a good choice. It works rather well for my corrupt Access MDB files. Its web address is http://www.datanumen.com/aar/ If necessary, we can have a try.

Alan

Post new comment

The content of this field is kept private and will not be shown publicly.
  • Allowed HTML tags: <a> <em> <strong> <cite> <code> <ul> <ol> <li> <dl> <dt> <dd> <blockquote> <del> <p>
  • Lines and paragraphs break automatically.
  • You may post code using <code>...</code> (generic) or <?php ... ?> (highlighted PHP) tags.
  • You may use [acidfree:xx] tags to display acidfree videos or images inline.
  • Images can be added to this post.

More information about formatting options

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
9 + 7 =
Solve this simple math problem and enter the result. E.g. for 1+3, enter 4.