make a blank access db, then put in the code to scan the folder and compact each db....
Code:
'--------------
Public Sub CompactAll()
'--------------
cycleThruAllFilesInDir "c:\Data\Access apps\"
End Sub
'--------------
Public Sub cycleThruAllFilesInDir(ByVal pvDir)
'--------------
Dim FSO, oFolder, oFile, vFile
Dim sTxt As String, sFile As String
Dim acc As Access.Application
Dim control As Office.CommandBarControl
Dim bRun As Boolean
Dim i As Integer
On Error GoTo errGetFiles
Set FSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = FSO.GetFolder(pvDir)
Set acc = New Access.Application
For Each oFile In oFolder.Files
bRun = False
If (InStr(oFile.Name, ".accdb") > 0) Or (InStr(oFile.Name, ".mdb") > 0) Then
'verify its a db file
If (InStr(oFile.Name, ".accdb") > 0) Then
i = InStr(oFile.Name, ".accdb")
If i = Len(oFile.Name) - 5 Then bRun = True
Else
i = InStr(oFile.Name, ".mdb")
If i = Len(oFile.Name) - 3 Then bRun = True
End If
'compact file here
If bRun Then
vFile = pvDir & oFile.Name
acc.OpenCurrentDatabase (vFile)
acc.Visible = True
acc.SetOption ("Auto Compact"), 1
acc.Quit acQuitSaveNone
End If
End If
Next
endit:
Set oFile = Nothing
Set oFolder = Nothing
Set FSO = Nothing
Set acc = Nothing
Set control = Nothing
MsgBox "Done"
Exit Sub
errGetFiles:
MsgBox Err.Description, , Err
Resume endit
End Sub