Compacting DBs

  • Thread starter Thread starter kcomer
  • Start date Start date
K

kcomer

I am using Access 2002.


I am in acctg and have quite a few large databases. I
have gone through these and run some delete queries to
clean them up a bit. Now they all need to be compacted.
I know I can do them one at a time, but is there a way I
can set something up to compact them one after the other?
This way them can compact overnight or during lunch.
Maybe a macro?

Thanks

Ken
 
Ken,

Make a new blank database, create a module an paste the
following code:

Sub compact_all_databases()
Dim fs, f, f1
fldr = "C:\Temp\"
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(fldr)
Set f1 = f.Files
For Each f2 In f1
oldnam = fldr & f2.Name
If Right(oldnam, 4) = ".mdb" Then
On Error GoTo next_db
Debug.Print f2.Name
DBEngine.CompactDatabase oldnam, fldr
& "Temp.mdb"
On Error GoTo 0
newnam = fldr & "Temp.mdb"
Kill oldnam
Name newnam As oldnam
End If
next_db:
Next
End Sub

Change the folder from C:\Temp to whatever you want it to
be and run it. It will do the job for all .mdb's in that
folder.
Word of caution: make a new folder, copy your databases
there and run the code on the copies! I wouldn't risk
running code on my originals - by the time you realize
something's gone wrong it's too late... When it's done and
everything is OK, you can copy the compressed databases
back to their original location.

HTH,
Nikos
 
Back
Top