Compact on close

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I wish to regularly compact a linked mdb file and I have found a few sample
solutions for this.

However in Tool/Options/General there is the option to compact on close.
Is there any reason to not use this? My linked file would be about 15mb
after compacting.

Thanks in advance.
 
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
 
Compact on Close will only compact the MDB in which the flag is set.
That means the Front End. It won't deal with the Back End/linked
tables. Even if that flag is set in the Back End the Open and Close
events don't happen unless you open that MDB directly.

HTH
 
Back
Top