Compact and Repair Database (db1.mdb?) RRS feed

  • Question

  • When compacting and repairing a database, Access doesn't actually compact the current copy of the database, it just saves it as db1.mdb? Am I correct?

    If so:

    I am building an application which there will not be an admin present to compact and repair. I am using a function to this automatically at program start. How would I go about replacing the current copy of the application with the compacted one?
    Sunday, August 9, 2015 7:02 AM


  • You can use MDB Repair Kit designed for restore the content of corrupted Microsoft Access database. Microsoft Access MDB repair tool helps to repair *.MDB files.

    For more:

    • Marked as answer by PeterLong969 Monday, August 10, 2015 10:32 AM
    Monday, August 10, 2015 8:23 AM

All replies

  • I am building an application which there will not be an admin present to compact and repair. I am using a function to this automatically at program start. How would I go about replacing the current copy of the application with the compacted one?

    Hi Peter,

    My experience with Compact and Repair (A2003) is that the current version is compacted, and stays the current version. Prior to the Compact action I make a copy of the database.

    I am not such a frequent compactor. I only compact (manually) just before the installation of a new production version.


    Sunday, August 9, 2015 11:15 AM
  • Peter is correct. When Access compacts a database, it actually does the following:

    1. Create a compacted copy of the current database, with a generic name such as Database1.mdb or Database1.accdb.
    2. Close the current database.
    3. Delete the current database.
    4. Give the compacted copy the name of the original database.
    5. Open the new database.

    You can set a database to Compact on Close, but that is a risky feature in a multi-user environment, I wouldn't recommend using it. (If user A is the only user, and A closes it, the process mentioned above will start. If user B opens the database before step 3, Access cannot delete the database, so you'll be left with the original uncompacted database and a compacted copy with a generic name).

    It is not possible to compact the current database programmatically, you must do it from outside, while the database is not in use.

    You could use the Task Scheduler built into Windows to compact the database at a time that nobody is likely to use the database, for example in the middle of the night, or on a weekend day.

    Regards, Hans Vogelaar (

    Sunday, August 9, 2015 12:18 PM
  • Hi Peter. I am not sure I understand your question. Is your database application not split? If it was, which file are you trying to compact? If the FE, why do you think it is necessary? Are you using temporary tables? If you're using techniques that bloat the FE, then replacing it with a copy of the master FE should be sufficient. You can do that at startup. Otherwise, setting the compact on close shouldn't hurt the FE much. If it gets corrupted, you can just replace it with a copy of the master. If you're trying to compact the BE, then you could take a look at this code: Backup and Compact BE. Hope that helps...
    Sunday, August 9, 2015 3:36 PM
  • That sounds about right.  Here is a VBA script that I got on this DG a long time ago.  See is this helps you . . .

    Option Compare Database
    Option Explicit
    '   Declare an enumeration of long integer
    '   constants, to be used as the return values
    '   for the RepairDatabase() function.
    '   As Access's CompactRepair() method returns
    '   TRUE or FALSE, the Enum uses -1 (TRUE) for
    '   success and 0 for failure.
    Public Enum ryCompactResult
        cmpCompactSuccessful = -1
        cmpCompactFailed = 0
        cmpErrorOccurred = 1
        cmpSourceFileDoesNotExist = 2
        cmpInvalidSourceFileNameExtension = 3
        cmpDestinationFileExists = 4
    End Enum
    Private Sub TestRepair()
        Dim strSource As String
        Dim strDestination As String
        Dim lngRetVal As ryCompactResult
        strSource = "C:\MyFolder\db1.mdb"
        strDestination = "C:\MyFolder\db2.mdb"
        '   Call the function:
        lngRetVal = RepairDatabase(strSource, strDestination)
        '   Examine the return value from the function
        '   and display appropriate message:
        Select Case lngRetVal
        Case cmpCompactSuccessful
            MsgBox "Compact & repair successful.", _
                vbOKOnly + vbInformation, _
                "Program Information"
        Case cmpSourceFileDoesNotExist
            MsgBox strSource & vbNewLine & vbNewLine _
                & "The above file does not exist.", _
                vbOKOnly + vbExclamation, _
                "Program Finished"
        Case cmpInvalidSourceFileNameExtension
            MsgBox strSource & vbNewLine & vbNewLine _
                & "The above file has an invalid filename " _
                & "extension.", vbOKOnly + vbExclamation, _
                "Program Finished"
        Case cmpDestinationFileExists
            MsgBox strDestination & vbNewLine & vbNewLine _
                & "The above destination file exists. " _
                & vbNewLine _
                & "Please delete the above file or " _
                & "use a different destination filename.", _
                vbOKOnly + vbExclamation, "Program Finished"
        Case cmpErrorOccurred
            '   The RepairDatabase() function has
            '   already displayed an error message.
        End Select
    End Sub
    Function RepairDatabase( _
        strSource As String, _
        strDestination As String) As ryCompactResult
        ' IN:
        '   strSource:
        '       The full path to the database that is
        '       to be compacted.
        '   strDestination:
        '       The full path to the resultant database
        '       after strSource has been compacted.
        ' OUT:
        '   This function returns one of the values in
        '   the ryCompactResult Enum.
        Dim lngRetVal As ryCompactResult
        Dim strFileName As String
        Dim strFileNameExtn As String
        Dim lngPos As Long
    On Error GoTo Error_RepairDatabase
        '   See if source file exists:
        strFileName = Dir(strSource)
        If Len(strFileName) = 0 Then
            lngRetVal = cmpSourceFileDoesNotExist
            GoTo Exit_RepairDatabase
        End If
        '   See if source filename has appropriate
        '   filename extension (mdb or accdb).
        '   First, see if filename contains a period:
        lngPos = InStr(strFileName, ".")
        If lngPos = 0 Then
            '   Period not found in filename;
            '   i.e. no filename extension found.
            lngRetVal = cmpInvalidSourceFileNameExtension
            GoTo Exit_RepairDatabase
            '   Get filename extension:
            strFileNameExtn = Mid(strFileName, lngPos + 1)
            strFileNameExtn = LCase(strFileNameExtn)
            Select Case strFileNameExtn
            Case "mdb", "accdb"
                '   Correct filename extension found.
                '   We can proceed with compact & repair.
            Case Else
                '   Invalid filename extension found.
                lngRetVal = cmpInvalidSourceFileNameExtension
                GoTo Exit_RepairDatabase
            End Select
        End If
        '   Destination file must not exist:
        strFileName = Dir(strDestination)
        If Len(strFileName) > 0 Then
            lngRetVal = cmpDestinationFileExists
            GoTo Exit_RepairDatabase
        End If
        '   Compact and repair database:
        lngRetVal = Application.CompactRepair( _
                    strSource, strDestination, True)
        RepairDatabase = lngRetVal
        Exit Function
        lngRetVal = cmpErrorOccurred
        MsgBox "Error No: " & Err.Number _
            & vbNewLine & vbNewLine _
            & Err.Description, _
            vbOKOnly + vbExclamation, _
            "Error Information"
        Resume Exit_RepairDatabase
    End Function

    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Monday, August 10, 2015 3:22 AM