Compact and Repair an Access Database Programmatically Using C#

• Download source - 1.28 KB

Introduction

As many of you are aware, Microsoft Access databases do not automatically shrink when data is deleted. Instead, Access files keep growing until the user asks the application to Compact and Repair the database. (You can tell Access to automatically Compact and Repair when the last user closes the database, but that doesn't solve the problem presented here.) What if you need to manage Access files programmatically? Our company's software writes data to Access files on the fly and allows the user to download them. There was just one problem — every time we wrote to the file, even if we truncated the tables first, it kept growing! My task was to find a way to programmatically Compact and Repair the database without involving the user or running Access. After some searching, I was able to locate several examples of doing this in classic ASP and/or VB6. I took those examples, migrated the code to managed C#, brought it into an ASP.NET web site, and abstracted the process into a simple utility function.

Background

Microsoft provides a way to Compact and Repair databases programmatically through its JET framework. JET is also the means through which applications are able to read from and write to various file formats in the Microsoft Office ecosystem, including Access and Excel files. Our application uses SQL Server Integration Services to export data through the JET provider from SQL Server to an Access database, which the user then downloads through HTTP.

Using the Code

The code consists of one static method placed in a class that should be in the App_Code folder of an ASP.NET web site. The code could just as easily be adapted to function within a desktop application — you'd just need to remove the path mapping logic.

There is one critical step to take before this code will compile (don't worry, it's easy). Add a reference to your web site (or desktop application). In the Add Reference dialog, go to the COM tab. Find Microsoft Jet and Replication Objects Library (the DLL file is msjro.dll), and add it. If you're working on a web site, you'll notice that Visual Studio will automatically create a couple Interop assemblies in your BIN folder. These are used to allow your managed code to talk to the COM objects involved. The process is completely transparent — do a search for .NET COM Interop to learn more about it.

Note: Compacting a database in this way cannot necessarily be done concurrently. It is up to you to implement concurrency and thread safety as needed.

Here is the code including XML comments (you can also download it above):

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Text;
using System.IO;
using JRO;

/// <summary>
/// Encapsulates small static utility functions.
/// </summary>
public class Utility
{
 /// <summary>The connection to use to connect to
 /// an Access database using JET.</summary>
 public const string AccessOleDbConnectionStringFormat =
  "Data Source={0};Provider=Microsoft.Jet.OLEDB.4.0;";

 /// <summary>
 /// Compacts an Access database using Microsoft JET COM
 /// interop.
 /// </summary>
 /// <param name="fileName">
 /// The filename of the Access database to compact. This
 /// filename will be mapped to the appropriate path on the
 /// web server, so use a tilde (~) to specify the web site
 /// root folder. For example, "~/Downloads/Export.mdb".
 /// The ASP.NET worker process must have been granted
 /// permission to read and write this file, as well as to
 /// create files in the folder in which this file resides.
 /// In addition, Microsoft JET 4.0 or later must be
 /// present on the server.
 /// </param>
 /// <returns>
 /// True if the compact was successful. False can indicate
 /// several possible problems including: unable to create
 /// JET COM object, unable to find source file, unable to
 /// create new compacted file, or unable to delete
 /// original file.
 /// </returns>
 public static bool CompactJetDatabase(string fileName)
 {
  // I use this function as part of an AJAX page, so rather
  // than throwing exceptions if errors are encountered, I
  // simply return false and allow the page to handle the
  // failure generically.
  try
  {
   // Find the database on the web server
   string oldFileName =
    HttpContext.Current.Server.MapPath(fileName);

   // JET will not compact the database in place, so we
   // need to create a temporary filename to use
   string newFileName =
    Path.Combine(Path.GetDirectoryName(oldFileName),
    Guid.NewGuid().ToString("N") + ".mdb");

   // Obtain a reference to the JET engine
   JetEngine engine =
    (JetEngine)HttpContext.Current.Server.CreateObject(
    "JRO.JetEngine");

   // Compact the database (saves the compacted version to
   // newFileName)
   engine.CompactDatabase(
    String.Format(
     AccessOleDbConnectionStringFormat, oldFileName),
    String.Format(
     AccessOleDbConnectionStringFormat, newFileName));

   // Delete the original database
   File.Delete(oldFileName);

   // Move (rename) the temporary compacted database to
   // the original filename
   File.Move(newFileName, oldFileName);

   // The operation was successful
   return true;
  }
  catch
  {
   // We encountered an error
   return false;
  }
 }
}

Contributors

Thanks to Roy Fine, Craig Starnes and Michael Brinkley for their earlier work on this issue in other languages.

History

• March 20, 2007 - First version published