[RESOLVED] How to Compact Database

[rockadudele]

Is it possible to "compact and repair" an ms access database from a VB.net program. How could i do it? please advice.

Thanks

[dee-u]

There is something in the sample in my sig that tackles such although it's in C# but I believe the conversion would be easy...

[rockadudele]

Thanks Dee...but I'm not sure which file is the code in? could you please let me know which file or if you could paste the code here would help.

The sln and the csproj files dont open with my version of .net. I'm using VB.net 2002.

[ganeshmoorthy]

if you use dao then use this statement

DBEngine.CompactDatabase SourceDBName, DestinationDBName

[Shuja Ali]

In your VB.NET project, add a reference to Microsoft Jet and Replication Objects 2.x Library by opening Project Menu, select Add Reference and then select COM Tab. And you can use this class to Compact and repair the database

Imports JRO
Public Class JetReplication
    Public Shared Function compactRepairMDB(ByVal sourceMDBPath As String, ByVal targetMDBPath As String) As Boolean
        Dim jetReplicationObject As New JetEngineClass
 
        Try
            jetReplicationObject.CompactDatabase( _
                "Provider=Microsoft.Jet.OleDB.4.0;Data Source=" _
                & sourceMDBPath, "Provider=Microsoft.Jet.OleDB.4.0;Data Source=" _
                & targetMDBPath)
        Catch ex As Exception
            Return False
        End Try
        Return True
    End Function
End Class

Call the function like this

JetReplication.compactRepairMDB("C:\db1.mdb", "C:\db2.mdb")

[rockadudele]

Thank you So much Shuja. The code works perfectly well.


Just curious to know why was there a source and a target file. Cant it be Compacted to the same database?

[dee-u]

What you may do is after compacting the original database you would have to delete it then rename the compacted database as your original database...

[rockadudele]

yep..did that already..thanks