Compact the CurrentDB

[Surjer]

Hello all and thanks in advance for any suggestions. My current project seems to be bloating itself rather fast. How do I implement this code to the current DB? Note that this opens a DB exclusively so I am sure it has to be ran while the DB is closed????

Public Function RepairDatabase(strSource As String, _
        strDestination As String) As Boolean
        ' Input values: the paths and file names of
        ' the source and destination files.

    ' Trap for errors.
    On Error GoTo error_handler

    ' Compact and repair the database. Use the return value of
    ' the CompactRepair method to determine if the file was
    ' successfully compacted.
    RepairDatabase = _
        Application.CompactRepair( _
        LogFile:=True, _
        SourceFile:=strSource, _
        DestinationFile:=strDestination)

    ' Reset the error trap and exit the function.
    On Error GoTo 0
    Exit Function

' Return False if an error occurs.
error_handler:
MsgBox Err.Number & Err.Description
    RepairDatabase = False

End Function

 

[NJudson]

I always run the compact/repair database utility from the Tools menu. I've never had a problem doing it this way and yes you can have the database open during the process. I guess I'm not sure why you want to set up code for it.

 

[Surjer]

The end user is not Access Knowledgeable plus why should they have to hit extra keystrokes to do this. I think I found a solution from a while back -

Send Keys (Yarrrr)

 

[Surjer]

Yeah amazing the INFO that is currently stored on this site - A little searching and WAMMMM

As I recall Rich posted a simple solution to this eons ago, and it was to use the dredded sendkeys command to do Tools-Database-CompactDatabase.

SendKeys "%tdc"

 

[NJudson]

Not sure if your interested but I found this sample database a while back. It's for A2k. I also have Access 97 and Access 2002 database as well if you like.

 

[Surjer]

The databases have to be closed in that example too... But that is a nice tool for backing data up. Good find :ThumbsUP