Visual Basic Online Courses DataGridView CRUD SQL Server

MS Access 2003/2007/2010/2016 Compact and Repair

VB.NET Compact & Repair Database – MS Access

Neither ADO nor ADO .NET give you a built-in Compact/Repair—use the JRO JetEngine COM library (MDAC 2.x) or the DAO interop for ACCDB files. ADO .NET can consume these via the Interop layer.

Requirements

• VB .NET (any IDE: VS 2010+)
• For *.mdb: Microsoft Jet and Replication Objects 2.x (COM reference)
• For *.accdb: Microsoft.Office.Interop.Access.Dao (NET reference)

Setup Steps

1. Create a Console (or WinForms) VB .NET project.
2. Right-click References → Add Reference:
   • MDB: COM → “Microsoft Jet and Replication Objects 2.x Library”
   • ACCDB: .NET → “Microsoft.Office.Interop.Access.Dao”
3. Accept the wrapper generation if prompted.
4. Open your Module1.vb, remove default code, and paste one of these examples:

'--- Compact 2000–2003 MDB via JRO
Module Module1
  Sub Main()
    Dim jro As New JRO.JetEngine()
    jro.CompactDatabase(
      "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Old.mdb",
      "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\New.mdb;Jet OLEDB:Engine Type=5")
    MsgBox("Database compacted successfully!")
  End Sub
End Module

'--- Compact 2007+ ACCDB via DAO
Module Module1
  Sub Main()
    Dim engine = New Microsoft.Office.Interop.Access.Dao.DBEngine()
    Dim tmp = "C:\Temp\New.accdb"
    engine.CompactDatabase(
      "C:\MyData.accdb",
      tmp, , , ";pwd=YourDbPassword")
    My.Computer.FileSystem.DeleteFile("C:\MyData.accdb")
    Rename(tmp, "C:\MyData.accdb")
    MsgBox("ACCDB compacted successfully!")
  End Sub
End Module

Pitfalls & Best Practices

• The engine needs exclusive access. Wrap in Try…Catch to handle “file in use” errors.
• Always compact to a new file, then swap—never run against the live database.
• Schedule off-hour runs (Task Scheduler) to avoid downtime.

Update April 2020

After upgrading to Office 2016, ensure you reference the v15.0 DAO interop DLL (not the older v12.0). If you still see v12.0, download the Office 2016 Database Engine redistributable from Microsoft and re-add the reference.

♥  Here are some online Visual Basic lessons and courses:

📘 Desktop application Development - VB.NET WebView2 🌐 Desktop application Development - Internet connectivity 📩 Desktop application Development - POP3 mail Server 🎲 Desktop application Development - VB.NET Strings 🔊 Desktop application Development - VB.NET Sound and Music 📊 Desktop application Development - VB.NET ProgressBar Control 🚫 Desktop application Development - TroubleShooting 💾 Microsoft Access Developer - VB.NET Best Practice Guide 🔁 Desktop application Development - VB.NET DataReader 📚 Database Developer - SyBase Advantage 🌄 Desktop application Development - Images ⌨️ Desktop application Development - VB.NET KeyPress vs KeyDown 💻 Desktop App Developer - Install SSL on Windows 🔬 Programming - Object Oriented Programming OOP 💬 Desktop App Developer - Text Files 🎁 Desktop App Developer - VB.NET DeopBox API 🚗 Desktop App Developer - VB.NET Google Drive API

Sitemap for this BLOG