Compact and repair mdb

For example with dao.DBEngine, like in this thread : https://video2.skills-academy.com/en-us/answers/questions/137775/this-code-results-in-error-operation-not-allowed-o

Hi @StewartBW ,

You can try using the Microsoft.Jet.OLEDB.4.0 provider and the JRO (Jet and Replication Objects) library, which provides methods for compacting and repairing Access databases.

Right-click on your project in Visual Studio and select "Add Reference". Go to the "COM" tab. Find and select Microsoft Jet and Replication Objects 2.6 Library

Imports System.Runtime.InteropServices
Imports JRO


        Dim sourceFile As String = "C:\path\to\your\database.mdb"
        Dim tempFile As String = "C:\path\to\your\temp_database.mdb"

        Try
            CompactAndRepair(sourceFile, tempFile)
            Console.WriteLine("Database compacted and repaired successfully.")
        Catch ex As Exception
            Console.WriteLine($"Error: {ex.Message}")
        End Try
    End Sub

    Public Sub CompactAndRepair(sourceFile As String, tempFile As String)
        Dim jetEngine As New JRO.JetEngine()

        Dim connectionString As String = $"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={sourceFile}"
        Dim tempConnectionString As String = $"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={tempFile}"

        jetEngine.CompactDatabase(connectionString, tempConnectionString)

        ' Replace the original file with the compacted one
        System.IO.File.Delete(sourceFile)
        System.IO.File.Move(tempFile, sourceFile)

Best Regards.

Jiachen Li

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.