Compact Database

[D. Lott]

-----Original Message-----
I would like to know the VBA command to compact (and
repair) an Access database. My program currently builds
many temporary tables, and the database needs to be
cleaned up (compacted) regularly. I would like to assign
this event (subroutine) to a button. Any ideas on the
VBA code to do the compacting?

Thanks
.

The following was copied from the Access Help icon (also
under the pull-down menu for Tools, General TAB, select
the compact on close option):

CompactRepair Method
See Also Applies To Example Specifics
Compacts and repairs the specified database (.mdb) or
Microsoft Access project (.adp) file. Returns a Boolean;
True if the process was successful.

expression.CompactRepair(SourceFile, DestinationFile,
LogFile)

expression Required. An expression that returns one of
the objects in the Applies To list.

SourceFile Required String. The full path and filename of
the database or project file to compact and repair.

DestinationFile Required String. The full path and
filename for where the recovered file will be saved.

LogFile Optional Boolean. True if a log file is created
in the destination directory to record any corruption
detected in the source file. A log file is only created if
corruption is detected in the source file. If LogFile is
False or omitted, no log file is created, even if
corruption is detected in the source file.

Remarks
The source file must not be the current database or be
open by any other user, since calling this method will
open the file exclusively.

Example
The following example compacts and repairs a database,
creates a log if there's any corruption in the source
file, and returns a Boolean value based on whether the
recovery was successful. For the example to work, you must
pass it the paths and file names of the source and
destination files.

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:
RepairDatabase = False

End Function