Compacting an Access Database While Using ADO.NET Backup to DAO by Les Smith

How do I compact and repair a Microsoft Access Database (.MDB) when using ADO.NET?  ADO.NET does not support the compacting and repairing of an Access Database, so the only answer that I have found is to drop back to DAO (remember DAO, back before ADO!)

For all of the great new features of ADO.NET, it does not support the compacting and repairing of an MDB.  So, we simply go back and include DAO in our project and use it just for the needed functionality.

First, include a reference to DAO360.DLL in you project references.  Then call something like the VB.NET method shown below.

Private Sub mnuRepairCompact_Click(ByVal sender As System.Object, _       ByVal e As System.EventArgs) Handles mnuRepairCompact.Click       Dim db As DAO.DBEngine       Dim sUFN As String       Dim sFN As String = _          System.IO.Path.GetFileNameWithoutExtension(DatabaseName)       Try          sUFN = AppPath & "\" & sFN & Format(Now, "MMddyyyyHHmmss") & _             ".mdb"          Rename(DatabaseName, sUFN)          db = New DAO.DBEngine()          db.CompactDatabase(sUFN, AppPath & "\" & sFN)       Catch ex As System.Exception          StructuredErrorHandler(ex)       End Try    End Sub