Click here to Skip to main content
15,860,972 members
Articles / Web Development / ASP.NET

Compact and Repair an Access Database Programmatically Using C#

Rate me:
Please Sign up or sign in to vote.
3.84/5 (14 votes)
20 Mar 2007CPOL2 min read 112.9K   1.9K   40   11
A simple method that compacts and repairs a Microsoft Access database file using the Microsoft Jet library and C# COM interop. The example code is meant to be integrated into an ASP.NET web site.

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):

C#
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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Web Developer
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionCan't use this example Pin
Grunf1629-Jan-16 11:01
Grunf1629-Jan-16 11:01 
Questiondont forget to add the using JRO by... Pin
yakirmanor5-Dec-13 1:43
yakirmanor5-Dec-13 1:43 
GeneralThanks. Pin
emre200830-Nov-09 7:47
emre200830-Nov-09 7:47 
GeneralRe: Thanks. Pin
pvatanpour2-Feb-10 17:58
pvatanpour2-Feb-10 17:58 
Questionhow can we implement the same (compact & repair an access database) when we are using ACCESS-2007 Pin
vishal nikam8320-Nov-08 22:32
vishal nikam8320-Nov-08 22:32 
AnswerRe: how can we implement the same (compact & repair an access database) when we are using ACCESS-2007 Pin
Jeremy T. Fuller21-Nov-08 7:17
Jeremy T. Fuller21-Nov-08 7:17 
GeneralRe: how can we implement the same (compact & repair an access database) when we are using ACCESS-2007 Pin
vishal nikam8327-Nov-08 19:03
vishal nikam8327-Nov-08 19:03 
AnswerRe: how can we implement the same (compact & repair an access database) when we are using ACCESS-2007 Pin
Yogesh_Verma16-Dec-09 0:02
Yogesh_Verma16-Dec-09 0:02 
QuestionHey do you have any licecening requirements? Pin
nothx onname17-Jun-08 4:03
nothx onname17-Jun-08 4:03 
AnswerRe: Hey do you have any licecening requirements? Pin
Jeremy T. Fuller17-Jun-08 7:44
Jeremy T. Fuller17-Jun-08 7:44 
Generalthanks Pin
M.S. Babaei16-Nov-07 23:27
M.S. Babaei16-Nov-07 23:27 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.