[AccessD] ACC97 - Repair and Compact

Hi Tony

> I have a program designed for a client that runs basically 24 hours a
> day, 7 days a week collecting and processing data. Is it possible to
> schedule a once weekly repair and compact operation on both the frontend
> and backend (or even for now just the backend). There is a lag/idle time
> between 1AM and 4AM, when no data is collected or processed. Any
> direction to available code, ideas or programs would be appreciated.
> Thank you.

1. If you write protect the front end database file (before launching
it) there's no need to compact it as, in that case, Access's temporary
data always will be written to external files.

2. If you don't have any open connections from the front end to the
back end, you can safely open the back end in a separate workspace and
compact it. To make sure that no other app has open connections to the
back end, let your compacting app open the back end exclusively; if
this fails don't continue.

3. Don't apply Repair for routine maintenance. This is a general
advice not invented by me, but I've never seen a need for it except
for corrupted database files and then JetComp.exe is a better first
attempt.

Re. 1. Write protecting an app will cause a message about this to pop
up at launch. This can be avoided by running the app from a runtime
installation.

Re. 2. The difficult part is not to compact the database file - it's a
couple of code lines only. The critical part is to create and keep
track of backup files in case something fails. 

Here's a function which handles this. It is a part of a solution which
has run for several years and also handles archiving to a cab or zip
file and ftp'ing this to a remote server. The backup routine is
triggered by a timer function on the main form.

<code>

Public Function DatabaseCompact( _
  ByVal strDataFile As String) As Boolean
  
  Const cbooDbOpenExclusive As Boolean = True
  Const cbooDbOpenReadOnly  As Boolean = True
  Const cbytDbFileExtLength As Byte = 3
  Const cstrDbFileExtBackup As String * cbytDbFileExtLength = "mdk"
  Const cstrDbFileExtTemp   As String * cbytDbFileExtLength = "tmp"
  
  Dim wks             As Workspace
  Dim dbs             As Database
  
  Dim strBackFile     As String
  Dim strTempFile     As String
  Dim strDataFilePath As String
  Dim strDataFileName As String
  Dim intDataFileName As Integer
  Dim booClosed       As Boolean
  Dim booSuccess      As Boolean
  
  On Error GoTo Err_DatabaseCompact
  
  strDataFileName = Dir(strDataFile, vbNormal)
  intDataFileName = Len(strDataFileName)
  If intDataFileName = 0 Then
    ' No data file to process.
  Else
    ' Data file exists.
    strDataFilePath = Left(strDataFile, Len(strDataFile) - intDataFileName)
    strBackFile = Left(strDataFile, Len(strDataFile) - cbytDbFileExtLength) & cstrDbFileExtBackup
    strTempFile = Left(strDataFile, Len(strDataFile) - cbytDbFileExtLength) & cstrDbFileExtTemp
    Set wks = DBEngine(0)
    Set dbs = wks.OpenDatabase(strDataFile, cbooDbOpenExclusive, cbooDbOpenReadOnly)
    ' No error. Database file can be opened exclusively, thus no open connections.
    ' Keep it open to prevent access from other users while compacting it.
    If Len(Dir(strBackFile, vbNormal)) > 0 Then
      ' Delete old backup file.
      Kill strBackFile
    End If
    If Len(Dir(strBackFile, vbNormal)) > 0 Then
      ' Old backup file could not be deleted.
    Else
      ' Create copy for further processing.
      FileCopy strDataFile, strBackFile
      If Len(Dir(strBackFile, vbNormal)) > 0 Then
        If Len(Dir(strTempFile, vbNormal)) > 0 Then
          ' Kill left-over temp file.
          Kill strTempFile
        End If
        If Len(Dir(strTempFile, vbNormal)) > 0 Then
          ' Old temp file could not be deleted.
        Else
          ' Compact backup file to a temporary file.
          DBEngine.CompactDatabase strBackFile, strTempFile, dbLangNorwDan
          If Len(Dir(strTempFile, vbNormal)) = 0 Then
            ' Compact failed somehow.
          Else
            ' Temporary file was created.
            ' Close database and delete current data file leaving backup file.
            dbs.Close
            booClosed = True
            Kill strDataFile
            If Len(Dir(strDataFile, vbNormal)) > 0 Then
              ' Current data file was not deleted.
              ' Clean up.
              Kill strTempFile
            Else
              ' Current data file is gone.
              ' Rename temporary data file as new data file.
              Name strTempFile As strDataFile
              If Len(Dir(strDataFile, vbNormal)) > 0 Then
                ' Temporary file was renamed successfully.
                ' Check that the data file can be opened and recognized.
                Set dbs = wks.OpenDatabase(strDataFile, cbooDbOpenExclusive, cbooDbOpenReadOnly)
                booClosed = False
                dbs.Close
                booClosed = True
                booSuccess = True
              End If
            End If
          End If
        End If
      End If
    End If
    If booClosed = False Then
      dbs.Close
    End If
    If booSuccess = False Then
      ' For some reason something failed.
      If Len(Dir(strDataFile, vbNormal)) = 0 Then
        ' Data file is lost.
        ' Copy back the backup file if it exists.
        If Len(Dir(strBackFile, vbNormal)) > 0 Then
          FileCopy strBackFile, strDataFile
        End If
      End If
    End If
  End If
  
  Set dbs = Nothing
  Set wks = Nothing
  
  DatabaseCompact = booSuccess
  
Exit_DatabaseCompact:
  Exit Function
  
Err_DatabaseCompact:
  MsgBox CStr(Err.Number) & ": " & Err.Description, vbCritical + vbOKOnly, "Database Compact"
  Resume Exit_DatabaseCompact
  
End Function

</code>

As always, beware of line breaks.

/gustav