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.

Answer 3

Here is a VB Script that will compact and repair a MDB file. I created this many years ago and have used it quite a bit.

Option Explicit

'remark for debugging, unremark to enable error catching

On Error Resume Next

Dim oArgs, oJetEng, oFSO

Dim src, tmp, eng

Set oArgs = WScript.Arguments

chkArgs 'require appropriate number of arguments

Set oFSO = CreateObject("Scripting.FileSystemObject")

OnErr "Creating 'Scripting.FileSystemObject' object" _

& vbCrLf & "Is Windows Scripting Host installed?", True

Set oJetEng = CreateObject("JRO.JetEngine")

OnErr "Creating 'JRO.JetEngine' object" & vbCrLf _

& "Is Microsoft Jet and Replication Objects Library installed?", True

src = Trim(oArgs(0))

'Set DB engine version here

eng = 5

'append the file extension if necessary

If (InStr(src, ".") = 0) Then src = src & ".mdb"

If chkMoveMDB Then

'Compact the database

Wscript.Echo "Writing/Compacting data to " & src

oJetEng.CompactDatabase "Data Source=" & tmp & ";" & _

    "jet OLEDB:Database Password=", "Data Source=" & src & ";" & _

    "jet OLEDB:Database Password=" & ";" & " jet OLEDB:Engine Type=" & eng



'check for error and exit if unsuccessful

OnErr "Error compacting database file." & src, True



Wscript.Echo "-----------------------------------------------------------------"

Wscript.Echo "Compact/Repair completed successfully."

Wscript.Echo

getFileSize src, "New"

Wscript.Echo

Wscript.Echo "You may delete " & tmp & " to save space, if desired."

End If

'Exit succesfully

Wscript.Quit(0)

'*******************************************************************************

Sub chkArgs()

If (oArgs.Count < 1) Then 'too few args

    usage

    Abort "Too few command line arguments."

ElseIf (oArgs.Count >= 2) Then

    usage

    Abort "Too many command line many arguments."

End If

End Sub

'*******************************************************************************

Function chkMoveMDB()

'rename the original file so we can create a new one during compress/repair

If (Not oFSO.FileExists(src)) Then

    Abort "File " & src & " NOT found."

    chkMoveMDB = False

Else

    Wscript.Echo "-----------------------------------------------------------------"

    Wscript.Echo "Target MDB: " & src

    'Wscript.Echo "Jet Engine Version: " & eng

    Wscript.Echo "-----------------------------------------------------------------"

    tmp = src & ".bak"

    If (oFSO.FileExists(tmp)) Then

        Abort "Temp file '" & tmp & "' already exists."

        chkMoveMDB = False

    Else

        getFileSize src, "Current"

        Wscript.Echo "Renaming original database to " & tmp

        'Rename the file back to the original name

        If oFSO.FileExists(src) Then oFSO.MoveFile src, tmp

        chkMoveMDB = True

    End If

End If

End Function

'*******************************************************************************

Sub getFileSize(fileName, fileState)

Dim file, sizeInBytes, sizeInMB

Const MB_COVERSION = 1048576



Set file = oFSO.GetFile(fileName)

sizeInBytes = file.Size

sizeInMB = FormatNumber(sizeInBytes / MB_COVERSION, 2)

Wscript.Echo fileState & " file size is: " & sizeInMB & " MB"

End Sub

'*******************************************************************************

'logs str to a file and/or console

'requires a handle to a file open for writing in logFile

Sub Warn(str)

Wscript.Echo str

End Sub

'*******************************************************************************

'shows a message box with str and aborts

Sub Abort(str)

Wscript.Echo "ERROR: " & str

Wscript.Quit(-1) 'exits with error

End Sub

'*******************************************************************************

'builds an error message from available information and clears Err object

'returns "Source/Error # (0x#): Description/Unknown str"

Function GetErr(str)

Dim sErr

Select Case Err

    Case -2147217843

        sErr = "Incorrect password."

    Case -2147467259

        sErr = "File in use or unable downgrade database."

    Case Else

        sErr = "ERROR"

If Trim(Err.Source) <> "" Then sErr = Trim(Err.Source) 'source

    sErr = sErr & " " & Err.Number & " (0x" & UCase(Hex(Err.Number)) & "): " 'number



If Trim(Err.Description) = "" Then

    sErr = sErr & " Unknown"

Else

    sErr = sErr & Trim(Err.Description)

End If

    if Trim(str) <> "" then sErr = sErr & vbCrLf & vbCrLf & Trim(str)

End Select

Err.Clear

GetErr = sErr

End Function

'*******************************************************************************

'aborts/warns on wsh error

Sub OnErr(str, fatalErr)

If Err.Number <> 0 Then

    If fatalErr Then

        Abort GetErr(str)

    Else

        Warn GetErr(str)

    End If

End If

End Sub

'*******************************************************************************

Sub usage()

WScript.Echo "Compacts and repairs MDB database file." & VbCrLf & _

             "Usage: Cscript " & Wscript.ScriptName & " filename" & VbCrLf & VbCrLf & _

             "filename = Path and name of MDB file. (e.g. D:\QS\MSAccess\database.mdb)" & VbCrLf

Wscript.Quit(-1)

End Sub

Answer 4

Actually, yes, the compact option is part of the database engine.

However, it is not part of the oleDB provider from .net (nor is it part of the ODBC provider either).

This code will work for a accDB, or a mdb file. I also do NOT recommend using the replication object, since it's been long depreciated, and of course it will not work for accDB files.

Note that doing a compact makes a copy of the file. Hence, in practice, we compact to a new file name. And if no errors? Then we can (should) re-name the original file to say .bak, and then re-name our new temp file to the original name. And if ANY errors, then of course we stop, and you at least have the original file.

Hence, this code should do the trick:

Imports System.IO
Imports Microsoft.Office.Interop.Access

        Dim sFile As String = TextBox1.Text

        ' set backup name
        Dim strBackup As String =
            Path.ChangeExtension(sFile, ".bak")

        ' set temp working file
        Dim strTemp As String =
            Path.ChangeExtension(sFile, ".tmp")

        'lblMsg.Text = "Compacting database - please wait"
        'lblMsg.Refresh() ' -- optional label on form - shows working...

        ' create instance of db engine.
        Dim dbEngine As New Dao.DBEngine
        Try

            File.Delete(strTemp)    ' delete temp working file
            File.Delete(strBackup)  ' delete .bak file

            dbEngine.CompactDatabase(sFile, strTemp)

            ' we get here - no errors. So, rename original to .bak
            FileSystem.Rename(sFile, strBackup)

            ' rename our temp working file back to origional file

            FileSystem.Rename(strTemp, sFile)
            'lblMsg.Text = "done"
        Catch ex As Exception
            lblMsg.Text = "error = " & ex.Message
            lblMsg.BackColor = Color.Red
        End Try

The above will (should) work for both accDB files, and that of mdb files.

So, the Access data engine DOES provide compact + repair operations, but it's not exposed to the oleDB provider in .net. I would as noted, not introduce the replication library, since that's not part of the newer version of JET, which is now called ACE, and arrived on the scene in 2007 (a whopping 18 years ago!).