How to compact database using vb.net application programatically, I tried JRO engine but it's not working

52 views Asked by At

I created an application to store document files in a MS Access database .accdb file using OLE object type, but every time when I save data and delete it, the size of the database is increased, but after delete, the space remains the same. I want to compact the database file programatically, I tried using the JRO Engine but it's not working.

This is the code:

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click

        Dim databaseFilePath As String = "D:\db\FileStorage.accdb"
        Dim compactedTempFilePath As String = "D:\db\temp_compacted_db1.accdb"

        Try
            CompactDatabase(databaseFilePath, compactedTempFilePath)

            ' Replace the original file with the compacted file
            File.Copy(compactedTempFilePath, databaseFilePath, True)
            File.Delete(compactedTempFilePath)

            MessageBox.Show("Database compacted successfully.")
        Catch ex As Exception
            MessageBox.Show($"Error: {ex.Message}")
            If ex.InnerException IsNot Nothing Then
                MessageBox.Show($"Inner Exception: {ex.InnerException.Message}")
            End If
        End Try
    End Sub
    Sub CompactDatabase(sourcePath As String, destinationPath As String)
        Try
            Dim jetEngine As New JRO.JetEngine()
            jetEngine.CompactDatabase($"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={sourcePath}", $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={destinationPath}")
        Catch ex As Exception
            Throw New Exception("Error compacting the database.", ex)
        End Try

    End Sub

When I run the code, catch returns this exception:

inner exception: invalid argument

I don't know where the problem is. I am using MS Access 2016.

Please help

0

There are 0 answers