Tuesday, May 24, 2005

Fixing a Corrupt MS Access Database

If you suspect your MS Access database is corrupt, try the following fixes in this order:

1. Compact and Repair the Database. It is advisable to create a backup of the database before running Compact and Repair, in the event the operation goes wrong.

2. Check that .mdb compiles without error.

3. Make a backup copy of the original .mdb and use the undocumented /decompile switch to decompile the .mdb, by creating a shortcut to the .mdb on these lines:

"C:\Program Files\Microsoft Office 2003\OFFICE11\msaccess.exe"
"C:\MyDocuments\MyDBase.mdb /decompile

4. Use the undocumented SaveAsText and LoadFromText methods of the Application object.

a. Export all objects, except tables, in the database to text files using the following procedures:

Application.SaveAsText(acQuery, "<ObjectName>", "<Path\File Name>")
Application.SaveAsText(acForm, "<ObjectName>", "<Path\File Name>")
Application.SaveAsText(acReport, "<ObjectName>", "<Path\File Name>")
Application.SaveAsText(acDataAccesPage, "<ObjectName>", "<Path\File Name>")
Application.SaveAsText(acMacro, "<ObjectName>", "<Path\File Name>")
Application.SaveAsText(acModule, "<ObjectName>", "<Path\File Name>")

where <ObjectName> is the name of an existing object, and
<Path\File Name> is the path to and the file name of the text file to be created.

b. Create a new database and import all the database objects into the new database.

  • With the new database open, point to Get External Data on the File menu, and then click Import. In the Import dialog box, locate and click on the damaged database, and then click Import. On the Tables tab in the Import Objects dialog box, click Select All. Click OK to import the selected tables.
  • Import each of the exported text files to the new database by using the following procedures:

    Application.LoadFromText(acQuery, "<ObjectName>", "<Path\File Name>")
    Application.LoadFromText(acForm, "<ObjectName>", "<Path\File Name>")
    Application.LoadFromText(acReport, "<ObjectName>", "<Path\File Name>")
    Application.LoadFromText(acDataAccesPage, "<ObjectName>", "<Path\File Name>")
    Application.LoadFromText(acMacro, "<ObjectName>", "<Path\File Name>")
    Application.LoadFromText(acModule, "<ObjectName>", "<Path\File Name>")

    where <ObjectName> is the name of the object, and
    <Path\File Name> is the path to and the file name of the text file to be imported.

For more info about database corruption, refer to:

  1. How to Troubleshoot/Repair a Damaged Jet 4.0 Database
  2. Microsoft Access Corruption FAQ

No comments: