I have an Access application designed such that all data is in db A and all
forms, etc. are in db B. Obviously, only db B is explicitly opened by the
application user. I have the option set to "Compact on Close" for both dbs.
This works fine for db B but not for db A. I have tried to programatically
compact db A when db B is closed but I always get an error that db A is open
and cannot be compacted.
Make sure that there are no open forms or recordset objects that point to any
tables in "db A".
I would appreciate any help on how I can get db A compacted each time db B
is closed.
You can compact a back-end file from code in the "OnUnload" or "OnClose" event
procedure of a form (you can open a hidden unbound form for this, and other
"cleanup" activities when the database opens) but, of course, if any one else
has the database open, this will not work. The following sample function first
checks to see if the back-end file is open and, if not, will proceed with the
compact (usage example is in function header):
'**********EXAMPLE START
Public Function fncCompactMDB(strSource As String _
, strDestination As String) As Boolean
' Comments : Compacts database file - Requires DAO Reference
' Parameters: strSource - The database to compact
' strDestination - The filename to compact to
' Returns : Boolean - True if no errors, otherwise False
' Created : 11/05/03 18:53 BMT
' Modified :
' Usage : boolReturn = fncCompactMDB("C:\db\source.mdb" _
' , "c:\db\destination.mdb")
' --------------------------------------------------
'
'THIS CODE WAS WRITTEN SOLELY AS AN EXAMPLE FOR
'DEMONSTRATION PURPOSES IN RESPONSE TO A NEWSGROUP
'QUESTION. NO WARRANTIES ARE MADE, EITHER EXPRESS OR
'IMPLIED, AS TO THE SUITABILITY OF THE SAME FOR ANYTHING
'OTHER THAN THE PURPOSES STATED HEREIN. PROPER TESTING
'SHOULD BE EXERCISED BEFORE IMPLEMENTING ANY CODE
'IN A PRODUCTION ENVIRONMENT
'
On Error GoTo fncCompactMDB_ERR
Dim strTempDb As String
'Verify that source exists
If Len(Dir(strSource)) = 0 Then
MsgBox "The source database cannot be found.", _
vbOKOnly + vbInformation, "Invalid Path or Filename"
'Compact failed
fncCompactMDB = False
GoTo fncCompactMDB_EXIT
End If
'Make sure back end isn't being used by someone else
Dim dbeNew As PrivDBEngine
Dim db As DAO.Database
'Set reference to new instance of PrivDBEngine
Set dbeNew = New PrivDBEngine
'If back-end is open, the next line should generate an error
Set db = dbeNew.OpenDatabase(strSource, True)
'If you got this far, you can continue
db.Close
Set db = Nothing
'*************************
'Compact the database file
'*************************
'First, check to see whether source and destination are the same
If strSource = strDestination Then 'If both are same
'Create temporary filename
strTempDb = _
Left(strSource, Len(strSource) - Len(Dir(strSource))) _
& "DbTemp.mdb"
'Make sure temporary file doesn't already exist
If Len(Dir(strTempDb)) > 0 Then
VBA.Kill strTempDb
End If
'Compact to temporary file
DBEngine.CompactDatabase strSource, strTempDb
'Replace original database with temporary file
VBA.FileCopy strTempDb, strDestination
'Delete temporary file
VBA.Kill strTempDb
Else 'Destination filename is different from Source filename
'Compact to new file
'If destination filename already exists, prompt to continue
If Len(Dir(strDestination)) > 0 Then
If MsgBox("The specified destination file already exists." _
& "Is it okay to replace this file?" _
, vbYesNo + vbQuestion, "Destination Filename Exists") _
= vbYes Then
VBA.Kill strDestination
Else
'Compact failed
fncCompactMDB = False
GoTo fncCompactMDB_EXIT
End If
End If
'Perform the compact
DBEngine.CompactDatabase strSource, strDestination
End If
'Success
fncCompactMDB = True
fncCompactMDB_EXIT:
'Clean up
On Error Resume Next
db.Close
Set db = Nothing
Set dbeNew = Nothing
Exit Function
fncCompactMDB_ERR:
If Err = 3045 Or Err = 3356 Then 'If source file is in use
MsgBox "The source file is in use and cannot be compacted." _
, vbOKOnly + vbInformation, "Compact Operation Failed"
Else
MsgBox "Error " & Err.Number & " occurred in fncCompactMDB: " _
& Err.Description
End If
'Compact failed
fncCompactMDB = False
Resume fncCompactMDB_EXIT
End Function
'**********EXAMPLE END
This function should be copied to a standard module (a global module, not one
behind a form or report), or to the module in the form from which it will be
called. You must have a reference set to the appropriate DAO Object Library for
your version of Access in order for the function to work. For more information
on this, see:
ACC2002: References That You Must Set When You Work with Microsoft Access
http://support.microsoft.com/default.aspx?kbid=283115
Scroll down to the section entitled "Setting a Reference to DAO for different
versions of Microsoft Access.
