TESTING AND REPAIRING CORRUPTED JET BACK-END DATABASES

Section 6 of the ap_AppInit() function deals solely with handling the situation of repairing the back end if it requires it:

Case apErrDBCorrupted1, apErrDBCorrupted2

   '-- Section 6: Backend Corrupted. Compact/Repair?
   Beep
   If MsgBox("The Backend Database is Corrupted." & vbCrLf & _
        vbCrLf & "Would you like to log users out and " & _
        " attempt to compact/repair it?", vbYesNo + vbCritical, _
        "Corrupted Backend!") = vbYes Then

      DoCmd.OpenForm "ap_CompactDatabase", acForm

   Else

      flgLeaveApplication = True

   End If

End Select

The section starts with handling the situation that the value stored in intCurrError is equal to apErrDBCorrupted1 or apErrDBCorrupted2, the constants used for corrupted database errors. Next, a message box appears to inform users of the situation and asks whether they want to try to repair it (see Figure 26.12).

After displaying the message box in Figure 26.12, the ap_RepairDatabase form is opened. In the OnLoad event of ap_RepairDatabase, the first item checked is whether anyone has already created the LogOut.flg file. If someone has, the routine exits from the system. Listing 26.11 shows the OnLoad procedure for ap_RepairDatabase.

Listing 26.11. VideoApp.mdb: Attempting to Repair the Database

Private Sub Form_Load()

   Dim strBackEndPath As String
   Dim strBackEndName As String
   Dim intCurrError As Integer

   '-- Grab the backend and path
   strBackEndPath = ap_GetDatabaseProp(CurrentDb(), _
                "LastBackEndPath")
   strBackEndName = ap_GetDatabaseProp(CurrentDb(), _
                "BackEndName")

   '-- If something else is up exit
   If ap_LogOutCheck(strBackEndPath) Then
      Beep
      MsgBox "Somebody has already requested users " & _
             "to log out." & vbCrLf & vbCrLf & _
             "All users are requested to logout " & _
             "at this time.", vbOKOnly + vbCritical, _
             "Logging Out for Maintenance"
      GoTo Exit_Form_Load
   End If

   '-- Create the flag file to log users out
   ap_LogOutCreate

   On Error GoTo Error_Form_Load

   DoCmd.SelectObject acForm, "ap_CompactDatabase"

   intCurrError = -1

   '-- Keep trying until no error
   Do While intCurrError <> 0

      DoCmd.Echo True, _
            "Attempting To Compact BackEnd Database..."

      '-- This lets windows catch up
      DoEvents

      '-- This flag is set in the cmdCancel click event
      If intCancel Then
         GoTo Exit_Form_Load
      End If
      On Error Resume Next

      '-- Try to compact into a temp file
      DBEngine.CompactDatabase strBackEndPath & _
                strBackEndName, strBackEndPath & _
                "CompTemp.mdb"

      intCurrError = Err.Number

      '-- if no error, continue on
      If intCurrError = 0 Then

         On Error GoTo Error_Form_Load

         '-- Delete the original database,
         '-- then rename the temp to the original name
         Kill strBackEndPath & strBackEndName
         Name strBackEndPath & "CompTemp.mdb" As _
                    strBackEndPath & strBackEndName

      End If

   Loop

   '-- Delete the flag file
   ap_LogOutRemove

   MsgBox "Backend Compacted/Repaired Successfully!"

Exit_Form_Load:

   DoCmd.Close acForm, "ap_CompactDatabase"
   Exit Sub

Error_Form_Load:

   MsgBox "The backend has not been compacted. " & _
          "The request for users to log out will " & _
          "be canceled!" & vbCrLf & vbCrLf & _
          "Please notify the system administrator.", _
          vbCritical, "Compact Canceled"

   ap_LogOutRemove

   Resume Exit_Form_Load

End Sub

The Form_Load event procedure for ap_CompactDatabase ends up doing double duty in that, in addition to testing for and creating the flag file (if necessary), it allows attempts to repair the database. Figure 26.13 shows the ap_CompactDatabase form.

It takes approximately five minutes for users to be logged out from the system after the logout flag file is created. It might take a few tries before the system can perform the compact/repair command on the back end. The following section of code attempts to repair the database:

intCurrError = -1

'-- Keep trying until no error
Do While intCurrError <> 0

   DoCmd.Echo True, _
         "Attempting To Compact BackEnd Database..."

   '-- This lets windows catch up
   DoEvents

   '-- This flag is set in the cmdCancel click event
   If intCancel Then
      GoTo Exit_Form_Load
   End If

   On Error Resume Next

   '-- Try to compact into a temp file
   DBEngine.CompactDatabase strBackEndPath & _
             strBackEndName, strBackEndPath & _
             "CompTemp.mdb"

   intCurrError = Err.Number

   '-- if no error, continue on
   If intCurrError = 0 Then
      On Error GoTo Error_Form_Load

      '-- Delete the original database,
      '-- then rename the temp to the original name
      Kill strBackEndPath & strBackEndName
      Name strBackEndPath & "CompTemp.mdb" As _
                 strBackEndPath & strBackEndName

   End If

Loop

In the loop, error handling is set to Resume Next so that the error can be examined and the routine can keep trying to compact/repair the back end. After the database is repaired successfully, the system removes the logout flag file with a call to the subroutine ap_LogOutRemove, explained earlier in the section “Setting the Flag File to Log Users Out of the Back End.”

If any other errors occur in the routine, a message box appears, explaining that the back-end database wasn't repaired. The logout flag file is then erased so that others can have the chance to correct the problem. The user is then sent out of the application.

The only other routine used for the ap_CompactDatabase form is the OnClick event of the btnCancel command button. If users decide not to wait any longer, they can click Cancel and get the same message given in the error routine of the OnCurrent event (see Figure 26.14).

The next two sections of the ap_AppInit() function are pretty straightforward, as you can see from the following code:

'-- Section 7: Leave the application if requested
If flgLeaveApplication Then
   Application.Quit
   Exit Function
End If

On Error Resume Next

'-- Section 8: Let's try and open the first table again.
dynSharedTables.MoveFirst

Set dynTestTable = _
   dbLocal.OpenRecordset(dynSharedTables!TableName, dbOpenDynaset)

intCurrError = Err.Number
strCurrError = Err.Description

Loop

In section 7, the flgLeaveApplication variable has been set in the prior sections to see whether leaving the application is needed. Section 8 rechecks the initial table in tblSharedTables to see whether the error loop can be exited safely.

The next section of real interest is section 9, where the user is kept up-to-date with the current version of the application.