Recipe 10.10 Compacting an Access Database

Problem

You need to compact or repair an Access database.

Solution

Use COM interop to the compact the Access database using JRO, or the Process.Start( ) method to compact the database using a command line switch.

You'll need a reference to the Microsoft Jet and Replication Objects 2.6 Library from the COM tab in Visual Studio .NET's Add Reference Dialog.

The sample code contains one event handler and a single method:

Compact Button.Click

Allows the user to specify the Access database to compact and the filename for the compacted Access database. It then calls the CompactAccessDatabase( ) method in the sample to compact the database.

CompactAccessDatabase( )

This method uses ADOX through COM interop to compact the specified Access database to the specified location.

The C# code is shown in Example 10-10.

Example 10-10. File: CompactAccessForm.cs

// Namespaces, variables, and constants
using System;
using System.Windows.Forms;

//  . . . 

private void compactButton_Click(object sender, System.EventArgs e)
{

    // Create the open file dialog object to get
    // the Access database to compact.
    OpenFileDialog ofd = new OpenFileDialog( );
    ofd.InitialDirectory = System.IO.Path.GetTempPath( );
    // Set the filter for Access databases.
    ofd.Filter = "Microsoft Access (*.mdb)|*.mdb";

    // Open the dialog.
    if (ofd.ShowDialog( ) != DialogResult.OK)
        // Return, if not user is not OK.
        return;

    String sourceFileName = ofd.FileName;

    // Create the save file dialog object to get
    // the filename for the compacted database.
    SaveFileDialog sfd = new SaveFileDialog( );
    sfd.InitialDirectory = System.IO.Path.GetTempPath( );
    // Set the filter for Access databases.
    sfd.Filter = "Microsoft Access (*.mdb)|*.mdb";

    // Open the dialog.
    if (sfd.ShowDialog( ) == DialogResult.OK)
    {
        // If OK selected, compact the Access database.
        resultTextBox.Text = "Beginning compact." +
            Environment.NewLine;
        resultTextBox.Refresh( );

        String destFileName = sfd.FileName;

        // Compact the database.
        try
        {
            CompactAccessDatabase(sourceFileName, destFileName);

            resultTextBox.Text += "Finished compact.";
        }
        catch (System.Exception ex)
        {
            resultTextBox.Text += "ERROR: " + ex.Message;
        }
    }
}

private void CompactAccessDatabase(String sourceFileName,
    String destFileName)
{
    // Define connection string for original and compacted database.
    String sourceConnection =
        @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
        sourceFileName + ";";
    String destConnection =
        @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
        destFileName + ";";

    // Create new Jet and Replication Objects (JRO) JetEngine object.
    JRO.JetEngine je = new JRO.JetEngine( );

    // Compact the database.
    try
    {
        je.CompactDatabase(sourceConnection, destConnection);
    }
    finally
    {
        je = null;
    }
}

Discussion

Neither ADO.NET nor ADO provides a way to compact or repair an Access database. To isolate functionality specific to JET from the more generic ADO library, the Jet OLE DB Provider and Replication Objects (JRO) library was created. JRO can be used from .NET through COM interop to compact, repair, or create a replica from an Access database. The CompactDatabase( ) method of the JRO JetEngine object compacts an Access database. It takes two arguments: the connection strings to both the source and destination databases. In both cases, the connection string needs the path and filename of the Access database as well as any additional connection properties for security or encryption information.

An alternative to JRO is available. The .NET framework provides a Process class in the System.Diagnostics namespace to allow access to local and remote processes and to allow you to start and stop local processes. The Start( ) method starts a process and has three overloads. The one that you need to compact an Access database takes two arguments: the filename of the application to run and the command-line arguments to pass when starting the process. So, to compact an Access database located in c:\ named MyDB.mdb into the database MyDBCompact.mdb in the same directory, the following statement could be used:

System.Diagnostics.Process.Start(
    @"C:\Program Files\Microsoft Office\Office10\msaccess.exe",
    @"C:\MyDB.mdb /compact C:\MyDBCompact.mdb");

With either technique, the JET Engine requires exclusive access to the database file to compact it. Otherwise, an exception will be raised .