Programing an automatic compact and repair | Access forum

I have a Database (Db1) which is the main database I need to compact and repair regularly. Since a number of people regularly work away from the office it has proved impractical to set the database to compact on close since if their connection is slow then it tends to crash rather than compact.

I looked into how to set up an automatic Compact and Repair that would be timed through the Windows Scheduled Tasks function, and executed though another Database (CompactDB) specifically set up with only enough information to open Db1 and run a compact and repair, and the close itself and Db1.

For this I found some VBA code (originally for an older Access verison though) on the web that said it would work - it does seem to work, however it actually creates a NEW compacted Db1 file (named by date), and leaves the original Db1 in an uncompacted state.

I have tried to understand the code to remove the part telling it to create a new file - but I really am at a loss. Here is the code:

Private Sub Form_Timer()
'==================================================================
'The Timer event runs this code every minute. It compares your
'system time with the StartTime variable. When they match, it
'begins to compact all databases in the DBNames table.
'==================================================================
Dim StartTime As String
' Set this variable for the time you want compacting to begin.
StartTime = "04:51 PM"
' If StartTime is now, open the DBNames table and start compacting.
If Format(Now(), "medium time") = Format(StartTime, _
"medium time") Then
Dim RS As Recordset, DB As Database
Dim NewDBName As String, DBName As String
Set DB = CurrentDb()
Set RS = DB.OpenRecordset("DBNames")
On Error Resume Next
RS.MoveFirst
Do Until RS.EOF
DBName = RS("DBFolder") & "\" & RS("DBName")
' Create a new name for the compacted database.
' This example uses the DBID plus the current date, which
' falls in DOS 8.3 file name limits for DBID = 1 to 99.
NewDBName = RS("DBFolder") & "\" & RS("DBID") & Format(Date, _
"MMDDYY") & ".mdb"
DBEngine.CompactDatabase DBName, NewDBName
RS.MoveNext
Loop
' Close the form, and then close Microsoft Access.
DoCmd.Close acForm, "CompactDB"
DoCmd.CloseDatabase
End If
End Sub


Please let me know how to resolve this - or any other suggestions for an automatic compact and repair welcome!

Thanks,
Sarah