[RESOLVED] [2005] Compact And Repair

[shakti5385]

Hi all
How to compact and repair the access database at run time.
Thanks

[RobDog888]

You can use several methods. Depending on your app one will be best for your situation. If you are using the Access Object Model then see my FAQ. If you are not using the Access Object Model then use JRO.

'Add a ref to MS Jet and Replication Objects X.X library...
Dim jro As jro.JetEngine
Set jro = New jro.JetEngine
jro.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\nwind2.mdb;Jet OLEDB:Database Password=test", _ 
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\abbc2.mdb;Jet OLEDB:Engine Type=4;Jet OLEDB:Database Password=test"

[aldrean]

Take a look a these and give it a try.I will try to attach the dll file so that you can use this code.

Private Sub CompactAccessDatabase()
        Try
 
            Dim File_Path, compact_file As String
            'Original file path we are about to compact
            File_Path = "C:\Your File Name.mdb"
            'compact file path, a temp file
            compact_file = "C:\Any Name You Want.mdb"
            If File.Exists(File_Path) Then
                Dim db As New DAO.DBEngine
                db.CompactDatabase(File_Path, compact_file)
            End If
            'Restore the original file from the compacted file
            If File.Exists(compact_file) Then
                File.Delete(File_Path)
                File.Move(compact_file, File_Path)
            End If
            MessageBox.Show("Compacting database successfully completed", "Compact", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1)
 
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Compact and Repair Error", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1)
        End Try
    End Sub

[aldrean]

Here's the attach dll file, make it as your reference.

[shakti5385]

@aldrean
Send ame the zip file I donot have rar install
Thanks

[aldrean]

I had change it as you requested.Hope it helps.

[shakti5385]

@aldrean
Have you any idea about the restore the databse

[RobDog888]

Why use all that extra complication? See post #2. Simple and sweet.

[aldrean]

There is Rollback sub routine in there, try to explore a little bit.I had not specifically used it since the CompactDatabase routine was the one I used.

[shakti5385]

This Code Restore the Database

''' <summary>
    ''' This sub is used for the database restore
    ''' 1) Add the Open File Dialog Control
    ''' 2) Imports System.IO
    ''' </summary>
    ''' <param name="dataBaseName">Here the database Name is the Name of the Databse you are passing</param>
    ''' <remarks></remarks>
    Sub FileRestore(ByVal dataBaseName As String)
        Try
            Me.OpenFileDialog.ShowDialog()
            If String.Equals(IO.Path.GetFileName(Me.OpenFileDialog.FileName), dataBaseName) Then
                If MessageBox.Show("Do you want to Restore the Database?", "Message", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.No Then Exit Sub
 
                IO.File.Delete(IO.Path.Combine(Application.StartupPath,  dataBaseName)) 'Deleting The Old File
                IO.File.Copy(Me.OpenFileDialog.FileName, IO.Path.Combine(Application.StartupPath, dataBaseName)) ' Making  A Copy Of That File
                MessageBox.Show("Database Successfully Restored", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Else
                MessageBox.Show("Incorrect File Name", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information)
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Contact IFW Creations", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub