Compact and repair mdb

Question

Hello

I use Access Database Engine and OleDbReader to work with mdb files.

If my memory serves me, I've been told, compact (and repair if necessary) of mdb files is not provided in Access Database Engine?

If so, which component and command to use, when Office is not installed on target systems, so must ship the dll with app.

Thanks.

Answer 1

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

Answer 2

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.