How can I auto compact the back end?

[Douglas J. Steele]

Don't know whether you can do it using a macro, but it's pretty simple to do
using VBA.

Here's what I typically use:

Function CompactDatabase(DatabaseName As String) As Boolean
' Renames the existing backend database from .MDB to .BAK
' Compacts the backup copy to the "proper" database
'
' Returns True if successful, False otherwise

On Error GoTo Err_CompactDatabase

Dim booStatus As Boolean
1Dim strBackupFile As String

booStatus = True

' Make sure that DatabaseName exists
If Len(Dir$(DatabaseName)) > 0 Then

' Figure out what the backup file should be named
If StrComp(Right$(DatabaseName, 4), ".mdb", vbTextCompare) = 0 Then
strBackupFile = Left$(DatabaseName, Len(DatabaseName) - 4) &
".bak"

' Determine whether the backup file already exists,
' and delete it if it does.
If Len(Dir$(strBackupFile)) > 0 Then
Kill strBackupFile
End If

Name DatabaseName As strBackupFile

' Do the actual compact
DBEngine.CompactDatabase strBackupFile, DatabaseName
End If

End If

End_CompactDatabase:
CompactDatabase = booStatus
Exit Function

Err_CompactDatabase:
booStatus = False
MsgBox Err.Number & ": " & Err.Description
Resume End_CompactDatabase

End Function

Pass the name of the back-end database to the function as its parameter.

 

[Michael]

I have searched the files and only found one thread that pertained to auto
compact. I understand to use the compact on close will only compact the
front end, is there a way to run a macro to auto compact on the backend?
Michael

 

[Michael]

thank you very much Douglas..
Michael

Don't know whether you can do it using a macro, but it's pretty simple to
do using VBA.

Here's what I typically use:

Function CompactDatabase(DatabaseName As String) As Boolean
' Renames the existing backend database from .MDB to .BAK
' Compacts the backup copy to the "proper" database
'
' Returns True if successful, False otherwise

On Error GoTo Err_CompactDatabase

Dim booStatus As Boolean
1Dim strBackupFile As String

booStatus = True

' Make sure that DatabaseName exists
If Len(Dir$(DatabaseName)) > 0 Then

' Figure out what the backup file should be named
If StrComp(Right$(DatabaseName, 4), ".mdb", vbTextCompare) = 0 Then
strBackupFile = Left$(DatabaseName, Len(DatabaseName) - 4) &
".bak"

' Determine whether the backup file already exists,
' and delete it if it does.
If Len(Dir$(strBackupFile)) > 0 Then
Kill strBackupFile
End If

Name DatabaseName As strBackupFile

' Do the actual compact
DBEngine.CompactDatabase strBackupFile, DatabaseName
End If

End If

End_CompactDatabase:
CompactDatabase = booStatus
Exit Function

Err_CompactDatabase:
booStatus = False
MsgBox Err.Number & ": " & Err.Description
Resume End_CompactDatabase

End Function

Pass the name of the back-end database to the function as its parameter.