Repairing Databases

Accidents happen. Users reboot workstations, power goes out, and disk drives go bad. If any of these occur while Microsoft Jet is writing data, then Microsoft Jet may mark the database as potentially corrupt.

When Microsoft Jet begins a write operation, it sets a flag, and resets the flag when the operation is complete. If a write operation is interrupted, the flag remains set. When you try to open that database again, Microsoft Jet determines that the flag is set and reports that the database is corrupt. In most cases, the data in the database is not actually corrupt, but the set flag alerts Microsoft Jet that corruption may have occurred.

You can use the RepairDatabase method to check for corruption. This method takes an inventory of the objects in the database, checking each one for integrity. If no corruption has occurred, the RepairDatabase method resets the write flag. If corruption has occurred, then Microsoft Jet attempts to repair the database.

Important Before using the RepairDatabase method, take the following items into consideration:

• Always make a back-up copy of the database.
  
  
• Do not use the RepairDatabase method on a replicated database. If you do, the database is no longer replicable and you will not be able to synchronize it with other replicas in the replica set. If you suspect a replica is corrupted, delete the replica and create a new replica from another replica in the set. The RepairDatabase method modifies certain system tables in the .mdb file. If the database were to remain a member of the replica set after this modification of the system tables, the other replicas in the set would become corrupt. The RepairDatabase method prevents this possibility by stopping the database from being replicable, and thus preventing synchronizations with other replicas.
  
  
• In Microsoft Jet 2.x, it was recommended that you repair a database before compacting it. This is because the RepairDatabase method and the corresponding menu command in that version can recover truncated records of data. However, this is not true for Microsoft Jet 3.0 and 3.5 file formats. For this reason, you should only repair a Microsoft Jet 3.0 or 3.5 database if an error message indicates that this is necessary.

The syntax for the RepairDatabase method is:

DBEngine.RepairDatabase databasename

In this syntax, databasename is the path and file name of the database to be repaired.

The following code attempts to open a database. If the database is marked as corrupt, the procedure calls the RepairDatabase method. In this example, strDbPath is the path to the database.

Function RepairAndOpen(strDbPath As String) As Boolean
	Dim dbs As Database
	Const conDatabaseCorrupt As Integer = 3049

	On Error Resume Next
	Set dbs = OpenDatabase(strDbPath)
	If Err.Number <> 0 Then
		' If it is a corruption error, then attempt to repair.
		If Err = conDatabaseCorrupt Then
			MsgBox "Database is corrupt. Attempting repair..."
			DBEngine.RepairDatabase strDbPath
			Err.Clear
			' Attempt to open database again.
			Set dbs = OpenDatabase(strDbPath)
			' Check whether another error occurred.
			If Err.Number <> 0 Then
				MsgBox "Database cannot be opened." & vbCrLf _
					& Err.Number & ": " & Err.Description
				RepairAndOpen = False
				Exit Function
			End If
		Else
			MsgBox "Database cannot be opened." & vbCrLf _
				& Err.Number & ": " & Err.Description
			RepairAndOpen = False
			Exit Function
		End If
	End If
		
	dbs.Close
	Set dbs = Nothing
	RepairAndOpen = True	Err
End Function

Note In Microsoft Jet 3.0, using the RepairDatabase method or command before compacting the database could result in a database that could no longer be opened. This problem, which happens only rarely due to duplicate indexes on a database’s system tables, has been resolved in Microsoft Jet 3.5. An updated release of Microsoft Jet 3.0 that corrects this problem is available on http://www.microsoft.com/kb/articles/q151/1/86.htm for users of Microsoft Access 95 and other programs that use Microsoft Jet 3.0.