Compact all DB's in a specific Directory VBA

  • Thread starter Thread starter jon.ingram
  • Start date Start date
J

jon.ingram

Is there any VBA code I could use in access form that would take
inventory of all the MS Access DB's in the current directory and
compact them all? This would be a big help. TIA.
 
I think there might be some at
http://www.mvps.org/access/resources/downloads.htm at "The Access Web".

Realistically, though, it shouldn't be too difficult to automate. Check the
following untested aircode

Dim strPath As String
Dim strFile As String

' Specific the folder.
' (make sure there's a terminating slash)
strPath = "C:\MyFiles\"

' We're going to use temp.xxx as a temporary file.
' Delete it if it exists
If Len(Dir(strPath & "temp.xxx")) > 0 Then
Kill strPath & "temp.xxx"
End If

' Get each mdb file in the folder
strFile = Dir$(strPath & "*.mdb")
Do While Len(strFile) > 0
DBEngine.CompactDatabase strPath & strFile, strPath & "temp.xxx"
Kill strPath & strFile
Name strPath & "temp.xxx" As strPath & strFile
Loop

(Note that I don't really recommend the above: I'd actually rename each file
as *.bak rather than *.mdb, for instance, and compact the renamed file to
the "actual" file name)
 
Back
Top