compacting dbase

[steve]

Can someone suggest how i can run a macro on the front end dbase that will
compact the backend dbase. The macro action "compact dbase" doesn't allow
me to direct it to which dbase i want compacted and won't compact a dbase
while it's open.

Tks

 

[Douglas J. Steele]

My normal routine is something like:

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
Dim 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 & "occurred in
CompactDatabase"
Resume End_CompactDatabase

End Function

You need to pass the full-qualified path to the backend database as
DatabaseName.