VB 2010 MS Access Compact and Repair Database

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

VB.Net Compact and Repair Database - MS Access

Neither ActiveX Data Objects (ADO) nor ADO .NET provide the means to compact or repair Microsoft Access databases. However, you can accomplish this task by using the Microsoft Jet OLE DB Provider and Replication Objects (JRO) that was introduced with Microsoft Data Access Components (MDAC) version 2.1. ADO .NET allows the use of COM-based object libraries through the Interop layer.

This article demonstrates how to compact an Access database by using Visual Basic .NET.

Requirements

• Microsoft Visual Basic .NET
• Microsoft Jet and Replication Objects 2.1, 2.5, or 2.6 Library - For *.mdb file (200-2003)
• Microsoft.Office.interop.access.dao Assembly - For *.accdb file (2007/2010/2016)

Steps to build example

1. Open a new Visual Basic .NET console application.
2. In the Solution Explorer window, right-click the References node and select Add Reference.

If you are targeting ms access 2000-2003 (*.mdb) then,

• In the Add Reference dialog box, click the COM tab, and then select Microsoft Jet and Replication Objects 2.x Library. Click Select to add it to Selected Components. Click OK

If you are targeting ms access 2007/2010/2016 (*.accdb) then,

• In the Add Reference dialog box, click the NET tab, and then select Microsoft.Office.interop.access.dao. (Run-time version : v1.1.4322) (File version : 12.0.0.0 means ms access 2007, version : 14.0.0.0 means ms access 2010, version : 15.0.0.0 means ms access 2016 'For more information about DAO Versions Click Here') Click Select to add it to Selected Components. Click OK

Picture from VS 2015 Project uses ms-access 2016 .net 4.6.1

In case of working with ms access 2000-2003 ,

1. Note In this step, x is a placeholder for the actual version number of the Microsoft Jet and Replication Objects Library component.
2. A warning will be displayed if there is no wrapper found for the selected library. Click Yes to generate a wrapper. Microsoft ActiveX Data Objects Library (ADODB) and JRO references will be added to the project's References.
3. In the Solution Explorer window, right-click Module1.vb and click View Code.
4. Delete all of the code from the code window.
5. Copy the following code and paste it into the code window: 
 

 

1. Change the path to the Source and Destination .mdb files as appropriate. Press F5 to build and run the project.
   
   The compacted database will be in Access 2000 (Jet 4.0) format. For a different Jet format, see "References."

Pitfalls

In order to compact a database, the Jet Database Engine requires exclusive access to the database file. Attempting to compact a database file that is currently in use will result in an exception. This exception can be caught using a Try...Catch structure.

Update April/2020

I had a problem with Visual Basic 2015 after installing Microsoft Office 2016, I could never successfully manage to execute Compact and Repair Database programmatically ever since, though it was fine when I used to have Office 2010 with both Visual Basic 2010 and Visual Basic 2015.

After searching for hours I found out that I don't have the proper DatabaseJetEngine for Office 2016, and my Dll file for Microsoft.Office.Interop.Access.Dao was actually version 12.0 still.

After installing DatabaseJetEngine for Office 2016 I noticed that the version of the Dll file of Microsoft.Office.Interop.Access.Dao changed from 12.0 to 15.0.

I deleted the Reference to it (12.0), though and added the Proper one (15.0) and then rebuilt my project and then everything went fine.

Now I have  :
Microsoft.Office.Interop.Access.Dao.dll, version 12.0
Microsoft.Office.Interop.Access.Dao.dll, version 14.0
Microsoft.Office.Interop.Access.Dao.dll, version 15.0

You can download your corresponding DataBaseJetEngine file from Here