Compact and Repair MS Accesss DB with VB app

[Rachel30]

Hi,

I would like to write a small VB app that would open an ms access database and run compact and repair. Then I will schedule this vb app to open of a night and automatically run a compact and repair and close the db. I have not got a clue where to start as I mainly use VBA. Any help I would be greatfull. Thanks Rachel

 

[HarleyQuinn]

A quick search in this forum for 'Compact and Repair' turned up this example (thread222-559719) posted by Swi, sourced from

http://www.freevbcode.com/ShowCode.Asp?ID=2879

The code posted is as below.

Function CompactAndRepairDB(sSource As String, _
    sDestination As String, _
    Optional sSecurity As String, _
    Optional sUser As String = "Admin", _
    Optional sPassword As String, _
    Optional lDestinationVersion As Long) As Boolean

    Dim sCompactPart1   As String
    Dim sCompactPart2   As String
    Dim oJet            As JRO.JetEngine

    On Error GoTo errhandler

    ' Put together the provider string for the source database
    sCompactPart1 = "Provider=Microsoft.Jet.OLEDB.4.0" & _
        ";Data Source=" & sSource & _
        ";User Id=" & sUser & _
        ";Password=" & sPassword
    
    ' If the database has a user-level security file, add the
    ' details
    If sSecurity <> "" Then
        sCompactPart1 = sCompactPart1 & _
            ";Jet OLEDB:System database=" & sSecurity & ";"
    End If
    
    ' Put together the provider string for the destination
    ' database
    sCompactPart2 = "Provider=Microsoft.Jet.OLEDB.4.0" & _
        ";Data Source=" & sDestination
    
    ' The destination database will end up in the latest version
    ' of jet, unless a specific version has been requested;
    ' 1 = Jet 1.0, 2 = Jet 1.1, 3 = Jet 2.x, 4 = Jet 3.x,
    ' 5 = Jet 4.x etc
    If lDestinationVersion <> 0 Then
        sCompactPart2 = sCompactPart2 & _
            ";Jet OLEDB:Engine Type=" & lDestinationVersion
    End If
    
    ' Compact and repair the database
    Set oJet = New JRO.JetEngine
    oJet.CompactDatabase sCompactPart1, sCompactPart2
    Set oJet = Nothing
    
    CompactAndRepairDB = True

Exit Function
errhandler:
If Err.Number = "-2147467259" Then
    MsgBox Err.Description, vbCritical, "Compact/Repair"
    CompactAndRepairDB = False
    Err.Clear
    Exit Function
End If
End Function

Hope this helps

HarleyQuinn
---------------------------------
Help us to help you,
read FAQ222-2244 before posting.

 

[Rachel30]

Hi,

Thanks for your help I have got it to work the only problem is it renames it to another file name I would like it to over write the existing one. This is what I have so far.

Private Sub Form_Load()

Call CompactAndRepairDB("C:/Musicguard.mdb", "C:/Musicguard2.mdb")

Kill ("C:/Musicguard.mdb")


End Sub

 

[HarleyQuinn]

How about changing it to:

Private Sub Form_Load()

Call CompactAndRepairDB("C:/Musicguard.mdb", "C:/Musicguard2.mdb")

Kill "C:/Musicguard.mdb"
FileCopy  "C:/Musicguard2.mdb", "C:/Musicguard.mdb"
Kill "C:/Musicguard2.mdb"

Hope this helps

HarleyQuinn
---------------------------------
Help us to help you,
read FAQ222-2244 before posting.

 

[Rachel30]

Thanks it's working how do I get it to close after I have run it.

 

[HarleyQuinn]

If you mena the VB Project you can use:

Unload Me

After your second KILL statement.

Hope this helps

HarleyQuinn
---------------------------------
Help us to help you,
read FAQ222-2244 before posting.

 

[Hypetia]

HarleyQuinn, may I propose a small modification in your code?

Instead of copying and killing...

[tt]Kill "C:/Musicguard.mdb"
FileCopy "C:/Musicguard2.mdb", "C:/Musicguard.mdb"
Kill "C:/Musicguard2.mdb"[/tt]

I suggest renaming the file...

[tt]Kill "C:/Musicguard.mdb"
Name "C:/Musicguard2.mdb" As "C:/Musicguard.mdb"[/tt]

 

[dilettante]

Also consider executing JetComp.exe, see:

http://support.microsoft.com/kb/273956

 

[HarleyQuinn]

Hypetia - Thanks, any suggestions for optimization of code are much appreciated. I'll have to be honest, I've used copying and killing for pretty much all of my programming career. The name...as is definitely something worth having in my knowledge base for future applications.

Cheers

HarleyQuinn
---------------------------------
Help us to help you,
read FAQ222-2244 before posting.

 

[mp9]

You might also find the freewware Handy Access Launcher (HAL) of interest. It's a little tool written in VB6 that does schedules compacts, repairs, macro runs, etc, at set times and with recurring date options. It compacts to the same filename too.

You can find HAL at

http://www22.brinkster.com/accessory/resources/001.shtml