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.
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):
public class Utility
public const string AccessOleDbConnectionStringFormat =
public static bool CompactJetDatabase(string fileName)
string oldFileName =
string newFileName =
Guid.NewGuid().ToString("N") + ".mdb");
JetEngine engine =
Thanks to Roy Fine, Craig Starnes and Michael Brinkley for their earlier work on this issue in other languages.
- March 20, 2007 - First version published