Compact a Microsoft Access database from .NET

Over time, a database becomes fragmented when it is used. This makes it grow in size and perform badly. Not the two qualities you want for your mission-critical data. Luckily, a lot of databases such as MS SQL Server and Oracle can compact it self, or at least have an interface for compacting so you can do it manually if you choose that approach.

Microsoft Access also has this feature called “Compact and repair”. However, this function can only be called from within the Access interface and not by a command you can call through ADO.NET. This is not desirable in an ASP.NET application, where you cannot open the database without first downloading it from the web server, compact it manually, and then upload it again. What we really want is a way to do this entire automatically.

Here is a method that does just that and it does it without taking you database offline. It copies the .mdb file and then compact the copy. When it’s done compacting, it replaces the original .mdb file with the compacted one. The only thing to remember is that you must have write-permissions to the directory of the Access .mdb file.

Shared Sub Compact(ByVal fileName As String)
   Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & fileName
   Dim objJRO As Object = System.Activator.CreateInstance(System.Type.GetTypeFromProgID("JRO.JetEngine"))

   Dim oParams As Object() = New Object() {connectionString, "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & fileName & ".tmp ;Jet OLEDB:Engine Type=5"}
   objJRO.GetType().InvokeMember("CompactDatabase", System.Reflection.BindingFlags.InvokeMethod, Nothing, objJRO, oParams)

   System.IO.File.Delete(fileName)
   System.IO.File.Move(fileName & ".tmp", fileName)

   System.Runtime.InteropServices.Marshal.ReleaseComObject(objJRO)
   objJRO = Nothing
End Sub

You can then call the method like this:

CompactDatabase.Compact("c:\temp\database.mdb")