I wrote this subroutine to compact the database
Code:
Sub CompactDB()
strOldDatabase = "c:\db\daycare.mdb"
strNewDatabase = "c:\db\tempdb.mdb"
DBEngine.CompactData strOldDatabase, strNewDatabase
'delete the temp db
KillProperly strOldDatabase
'rename new db
RenameFile strNewDatabase, strOldDatabase
End Sub



'This will kill the file in the path given :
'KillProperly "c:\temp\temp.doc"
Public Sub KillProperly(Killfile As String)
    'Check that file exists
    If Len(Dir$(Killfile)) > 0 Then
        'First remove readonly attribute, if set
        SetAttr Killfile, vbNormal
        'Then delete the file
        Kill Killfile
    End If
End Sub


Sub RenameFile(oldName, newName As String)
'You can change the path and file name
    Name oldName As newName
End Sub
The original db was 1,045KB and after compacting using this code, it came down to about 1, 032KB. But then I use the menu option in MS Access 2007 to compact and Repair the backup db and it reduced to 920KB. What is the difference?



Thanks for any help in advance.