Compact on cl;ose only compacts the front-end database that you are in. For
the back end, use something like this (I haven't tested it but others have
used it):
Function CompactDatabase() 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
Const conBackEndFile = "F:\SomeFolder\SomeFile.mdb"
Dim booStatus As Boolean
Dim strBackupFile As String
booStatus = True
' Make sure that Back End File exists
If Len(Dir$(conBackEndFile)) = 0 Then
MsgBox "File " & conBackEndFile & " not found.", vbExclamation,
"Error"
booStatus = False
Else
' Figure out what the backup file should be named
If StrComp(Right$(conBackEndFile, 4), ".mdb", vbTextCompare) = 0
Then
strBackupFile = Left$(conBackEndFile, Len(conBackEndFile) - 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 conBackEndFile As strBackupFile
' Do the actual compact
DBEngine.CompactDatabase strBackupFile, conBackEndFile
End If
End If
End_CompactDatabase:
CompactDatabase = booStatus
Exit Function
Err_CompactDatabase:
booStatus = False
MsgBox Err.Description & " (" & Err.Number & ")", _
vbOkOnly + vbCritical
Resume End_CompactDatabase
End Function
--
Arvin Meyer, MCP, MVP
Free MS-Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com