ms-access How to troubleshoot Access crashes Rebuild the entire database

Example

This is a lot of work, so do this as a last resort after exhausting all other options. You only need to do this if the problem is occurring for different users, on different machines. If it isn't occurring for all users, then most likely it is not a corrupt database container.

Similar to the steps in removing binary data, you are going to rebuild your database from scratch. This process is a little ritualistic, but if done meticulously with care not to "preserve" any possible corruption, then the process is highly effective.

Create a new access database container.

• In Access, on the File Tab, you can select "New". Create a new, empty database in ACCDB format.

Move all objects to the new container

Do not use the Import / Export functions within Access to move the objects, and do not simply click and drag. Doing this can copy the corrupt items over to the new container.

Tables:

• For each table in the old access container, create a new table in the new container.
• From design view, copy/paste the field definitions.
• Check the table properties to ensure they match in both databases
• Move any Data Macros over as well (see the Macros section for how to do this)
• To move the data, export the old data to XML or CSV, and then import from that format.

Queries:

• Load each query into SQL view.
• Copy / Paste the SQL text.
• Paste into the new database.
• Compare Query properties to ensure they match.

Forms / Reports:

• For each Form / Report, use the Application.SaveAsText function to export the forms/reports to a text file.
• Remove the Binary Data (see Remove Binary Data from Form documentation to acquaint yourself with this process)
• Use the Application.LoadFromText function to reimport the objects into the new database

Macros

You have three methods of moving the Macros.

1. Recreate each macro by hand in the new database container.
2. Use the Application.SaveAsText / Application.LoadFromText method with the acMacro parameter.
3. Copy/Paste Macro definitions for each macro
   • Select All (Control + A) to select all macro elements. Then Copy (Control + C).
   • Open a blank Notepad document and Paste (Control + V) the Macro XML.
   • Create a new blank macro in the new database container.
   • In Notepad, Select All text (Control + A). Then Copy (Control + C)
   • In the blank macro, Paste (Control + V). The Macro should appear. Save it.

Modules

• For each module, select all code (Control + A) and paste (Control + V) into the new database container.
• Be sure to check the Database Properties (In VBA Window, go Tools -> Client Properties)

Data Macros

For each Data Macro, use the SaveAsText / LoadFromText methods.

1. Go into the VBA Immediate Window (Control + G)
2. Type Application.SaveAsText acTableDataMacro, "MyTableName", CurrentProject.Path & "\MyTableName.txt" (Replace MyTableName with the name of the table containing the data macros)
3. Review the file for any signs of corruption
4. In the new database container, load the definition using Application.LoadFromText acTableDataMacro, "MyTableName", CurrentProject.Path & "\MyTableName.txt"

As previously mentioned, this is a LOT of work, but it has results. This method should also be used when migrating an Access 97 database to 2000, or an Access 2000 database to 2003.