How Do I ................

[simon mills]

Good morning everyone. Im stuck and wondered if anyone could help.

I have a database that opens other database at set time of the night. When the databases open at specific times they run lines of code designed to keep day to day admin to a minimum and use the quiet networks as they as fairly slow and cumbersome. At the end of the modules I want to have the database compact and then close. Due to limited hard drive space they cant stay open.

I have tried send keys ( "%FX" and "%tdc" ) but as the databases running dont always have focus they dont work, I have also tried docmd.application.close ......... but this has errored. Can anyone help and give me lines of code that will compact the database and then close it. Im wary that a compact will restart the code aswell. I could get the calling database to compact if I knew the code .... but I have to admit to being completely stumped now.

Thanx in advance for any help.
Si

 

[allan57]

Check out 'CompactDatabase' under ACCESS help

 

[Big2]

strDB = "D:\RoutineBackupData.mdb"
    
    ' Create new instance of Microsoft Access.
    Set Appaccess = CreateObject("Access.Application")
    
    ' Open database in Microsoft Access window.
    Appaccess.OpenCurrentDatabase strDB
    
    'import the two tables into the backup mdb, so that we can store historical files
    Appaccess.DoCmd.TransferDatabase acImport, "Microsoft Access", "D:\unitydata_MUA.mdb", acTable, "Tbl_POLMUASub", RoutineRunDate & " Tbl_POLMUASub - Mort"
    Appaccess.DoCmd.TransferDatabase acImport, "Microsoft Access", "D:\unitydata_MUA.mdb", acTable, "Tbl_POLMUASubHist", RoutineRunDate & " Tbl_POLMUASubHist - Mort"
    Appaccess.DoCmd.TransferDatabase acImport, "Microsoft Access", "D:\unitydata_MUA.mdb", acTable, "Tbl_POLMUAEXMort", RoutineRunDate & " Tbl_POLMUAEXMort - Mort"
    
    '//- If the backup database exceeds 250mb in size compact it
    
    DBFILESIZE = FileLen(strDB)
    
    If DBFILESIZE > 250000000 Then
        Appaccess.Application.SetOption "Auto Compact", True
    Else
        Appaccess.Application.SetOption "Auto Compact", False
    End If

    Appaccess.CloseCurrentDatabase 'close the backup database

 

[ghudson]

Just turn on the Compact On Close option. Search the Access help files if you need more info.

 

[Big2]

Only problem with autocompact is that it can get annoying if it compacts every time you close. So as you can see from the code above that if the database exceeds a certain size then it will compact it.

 

[ghudson]

Only problem with autocompact is that it can get annoying if it compacts every time you close. So as you can see from the code above that if the database exceeds a certain size then it will compact it.

I agree. I did not even notice the bottom of your code. I also do that and I have posted my compact on close code all over this forum.
Auto Compact OnClose