Know Dot Net Article

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