Compacting File.

[BillBee]

I have an application in which data is deleted each Year. For example exhibitors entries and fees are deleted. These are removed with a command buttons attached to an update queries. I would like to know if someone could provide some code that could also be attached to another control that would run the Compact and Repair action for the current Database. Many thanks.

 

[Ancalima]

Well I've tried two methods to perform the compact:

docmd.RunCommand acCmdCompactDatabase

And:

Application.CommandBars("Tools").Controls("Database Utilities").Controls("Compact And Repair Database...").Execute

Both methods, however return the same error:

"You can't compact the open database while running a macro or Visual Basic code. Instead of using a macro or code, on the Tools menu, point to Database Utilities, and then click Compact/Repair Database."

So I'm going to assume that you cannot execute the compact and Repair utility through code. You may have better luck using these methods if you use a different version of Access than I am using. I am running Access 2000.

However, if your intentions are to simply keep the database from getting too large. Then I have a code that I have been using which sets the auto compact option for the database in the exit button on a form, based on the current size of the DB. In this way it will compact on close if the size is too large. Here is the code to accompish this:

Private Sub cmdQuit_Click()
On Error GoTo Err_Quit_Click
 
    If FileLen(CurrentDB.Name) > 1500000000 Then
        MsgBox "Database size has increased over 1.5 GB" & vbCrLf & _
               "      Database will now auto-compact.", vbExclamation, "Size Limit Exceeded"
        SetOption "Auto Compact", 1
    Else
        SetOption "Auto Compact", 0
    End If
    DoCmd.Quit
Exit_Quit_Click:
    Exit Sub
Err_Quit_Click:
    MsgBox Err.Description
    Resume Exit_Quit_Click
 
End Sub

This checks for an approximate size of 1.5 GB's but this number can be adjusted to fit your needs. I hope this solves your problem.

 

[BillBee]

Thanks will try your suggestion. My application is approx 4MB. It is simply that with the data deleted each year I wanted to tidy up the whole file thinking that it may assist with better performance and possibly reduce errors creeping in.

 

[boblarson]

And an even easier way:
http://www.btabdevelopment.com/main/CodeSnippets/CompactDatabase/tabid/122/Default.aspx

 

[BillBee]

This seems better method. Just wondered Bob, Your post shows two samples.The second bottom line in each is different. One says TDC and the other FMC. Could you explain. Just realised they are for different versions Access

 

[boblarson]

This seems better method. Just wondered Bob, Your post shows two samples.The second bottom line in each is different. One says TDC and the other FMC. Could you explain. Just realised they are for different versions Access

One is for versions of Access from 2000-2003 and the other works with 2007. There are differences in their menus so you have to use different keystrokes.