Compact and Repair code problem

[glxman]

Hi

I obtained code to help me with this, but I cannot get it work as the backend database is locked when it tries to run the compact and repair. It will close all forms but the backend .ldb file is still resident while the code runs even after all forms are closed therefore moves to the error. If I manually close all forms then run the function it runs through fine - any ideas (simple ones preferably!), Thanks

Function CompactDB()

InfoBox ("This will close all open database objects and initialise the compact and repair of the CB database backend tables.")

Dim intx As Integer
Dim intCount As Integer

intCount = Forms.Count - 1

For intx = intCount To 0 Step -1

DoCmd.Close acForm, Forms(intx).Name

Next

MsgBox ("wait 10 seconds then click ok")

    On Error GoTo CompactDB_Err
    Const conFilePath = "W:\DII Finance Delivery\Journal Back\DII Cost Deferral DB\Backends\"

    DBEngine.CompactDatabase conFilePath & "EDSDeferralDatabase2008R_be.mdb", conFilePath & "EDSDeferralDatabase2008R_be.tmp"

    If Dir(conFilePath & "EDSDeferralDatabase2008R_be.bak") <> "" Then
        Kill conFilePath & "EDSDeferralDatabase2008R_be.bak"
    End If

    Name conFilePath & "EDSDeferralDatabase2008R_be.mdb" As conFilePath & "EDSDeferralDatabase2008R_be.bak"
    Name conFilePath & "EDSDeferralDatabase2008R_be.tmp" As conFilePath & "EDSDeferralDatabase2008R_be.mdb"

InfoBox ("CB Database Backend Compact and Repair completed successfully!")

DoCmd.OpenForm "frmUSL"

Exit_CompactDB:
    Exit Function

CompactDB_Err:

    CritBox ("An error occurred possibly due to the database backend being opened exclusively by another user - the following message will confirm!")
    
    MsgBox Err.Description
    
    DoCmd.OpenForm "frmUSL"
    
    Resume Exit_CompactDB


End Function

 

[Craig0201]

Simple.....

Someone had the db open. Look in the .ldb file to find out who.

C

 

[glxman]

There is no one else logged on - it is my frontend that is locking it - but I assumed that if I could get it to close all forms then this would unlock it.

Even at the point of msgbox, I can see the code has closed all my forms but the .ldb for the backend is still resident,and so continuing goes to error.

If I close all forms manually prior to running the code the .ldb vanishes and all the code completes successfully.

I just don't get it...