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