How to compact a Microsoft Access database by using Visual Basic .NET

Article translations Article translations
Article ID: 306287 - View products that this article applies to.
This article was previously published under Q306287
Expand all | Collapse all

On This Page

SUMMARY

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

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.
  3. 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.

    Note In this step, x is a placeholder for the actual version number of the Microsoft Jet and Replication Objects Library component.
  4. 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.
  5. In the Solution Explorer window, right-click Module1.vb and click View Code.
  6. Delete all of the code from the code window.
  7. Copy the following code and paste it into the code window:
    Module Module1
    
        Sub Main()
    
            Dim jro As JRO.JetEngine
    
            jro = New JRO.JetEngine()
    
            jro.CompactDatabase("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\nwind.mdb", _
            "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\NewNwind.mdb;Jet OLEDB:Engine Type=5")
    
            MsgBox("Finished Compacting Database!")
        End Sub
    
    End Module
    					
  8. 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.

REFERENCES

For more details about the .NET Framework and the COM Interop layer, see:
Exposing COM Components to the .NET Framework
http://msdn2.microsoft.com/en-us/library/z6tx9dw3(vs.71).aspx
For more information about using ADO and JRO to compact Access Databases, click the following article number to view the article in the Microsoft Knowledge Base:
230501 How to compact Microsoft Access database via ADO

Properties

Article ID: 306287 - Last Review: May 16, 2007 - Revision: 2.3
APPLIES TO
  • Microsoft Visual Basic .NET 2003 Standard Edition
  • Microsoft Visual Basic .NET 2002 Standard Edition
Keywords: 
kbhowtomaster KB306287

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com