* VB-CODE (3)
Tip 81: Repairing and Compressing a Microsoft Access Database from
        Visual Basic

May 8, 1995

Abstract
Within a Visual Basic® application, you can compress and repair a
Microsoft® Access® database (.MDB) file. This article explains how you
can accomplish these two tasks in Visual Basic.

Repairing and Compacting Access Files
On occasion, a Microsoft® Access® database file can become damaged.
For example, a database can become damaged if the computer system is
powered down without first closing the database file. The
RepairDatabase statement provided in Visual Basic® can be used to
repair a previously corrupted database file. You need only pass the
name of the .MDB file to the statement to repair it.

When records are deleted from a database file, the file can become
defragmented. You can compress a defragmented database file by using
Visual Basic's CompactDatabase statement. CompactDatabase's main
purpose is to compress a Microsoft Access file, but it can also be
used to change the database's sort order, encrypt/decrypt the
database, or create a Microsoft Access 1.0 compatible file.

The CompactDatabase statement requires four arguments, as follows:

SourceFile  The database's complete path and filename.
DestFile    The database's new path and filename.
Locale      The sorting order to be used.
Options     Set to one of the following values:
            DB_ENCRYPT    Encrypt database.
            DB_DECRYPT    Decrypt database.
            DB_VERSION10  Create a compatible Access 1.0 database
                          file.

When the CompactDatabase statement is executed, it copies each valid
record from the original database file to the new database file. Note
that these two filenames must be different and that the security
settings of the original file are automatically transferred to the
new file.

Example Program
The program below shows how to repair and/or compact a Microsoft
Access database (.MDB) file. To repair a damaged database file, click
the "Repair" command button; to compress (remove deleted records) a
database file, click the "Compact" command button.

 1. Create a new project in Visual Basic. Form1 is created by default.
 2. Add a Common Dialog control to Form1. CommonDialog1 is created by
    default.
 3. Add a Command Button control to Form1. Command1 is created by
    default. Set its Caption property to "Repair".
 4. Add the following code to the Click event for Command1:

Private Sub Command1_Click()
    On Error GoTo Repair_Error
    Dim MDB_Name As String

    CommonDialog1.Filter = "Access (*.mdb)|*.mdb"
    CommonDialog1.Flags = &H1000
    CommonDialog1.FilterIndex = 1
    CommonDialog1.Action = 1

    If CommonDialog1.FileName <> "" Then
        Screen.MousePointer = 11
        MDB_Name = CommonDialog1.FileName
        RepairDatabase (MDB_Name)
        Screen.MousePointer = 0
        MsgBox "Database repaired successfully", 64, "Repair"
    End If
    Screen.MousePointer = 0
    Exit Sub
Repair_Error:
    MsgBox "Error when repairing database", 16, "Error"
    Screen.MousePointer = 0
    Exit Sub
End Sub

 5. Add a second Command Button control to Form1. Command2 is created
    by default. Set its Caption property to "Compact".
 6. Add the following code to the Click event for Command2:

Private Sub Command2_Click()
    On Error GoTo Compact_Error

    Dim MDB_Name As String
    Dim MDB_NewName As String
    Dim MDB_Local As String
    Dim MDB_Options As String

    MDB_NewName = "c:\dummy.mdb"
    CommonDialog1.Filter = "Access (*.MDB)|*.mdb"
    CommonDialog1.Flags = &H1000
    CommonDialog1.FilterIndex = 1
    CommonDialog1.Action = 1

    If CommonDialog1.FileName <> "" Then
        MDB_Name = CommonDialog1.FileName
        CompactDatabase MDB_Name, MDB_NewName & MDB_Local & MDB_Options
        Kill MDB_Name
        Name MDB_NewName & MDB_Local & MDB_Options As MDB_Name
        MsgBox "Database compressed OK", 64, "Compact"
    End If
    Exit Sub
Compact_Error:
    MsgBox "Unable to compress database", 16, "Error"
    Exit Sub
End Sub