Hello
I was testing a code from Microsoft website: http://support.microsoft.com
I was successful but I needed to add 2 more lines of code without user intervention.
a) After compacting the database, I want to delete the old/original database.
b) Rename the new compacted database to the original name.
Does anyone know the right syntax to accomplish this?
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 compacting all databases in the DBNames table.
'===========================================
Dim StartTime As String
' Set this variable for the time you want compacting to begin.
StartTime = "12:00 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 old name plus the current date.
NewDBName = Left(DBName, Len(DBName) - 4)
NewDBName = NewDBName & " " & Format(Date, "MMDDYY") & ".mdb"
DBEngine.CompactDatabase DBName, NewDBName
RS.MoveNext
Loop
' Close the form, and then close Microsoft Access
DoCmd.Close acForm, "CompactDB", acSaveYes
RS.Close
DoCmd.Quit acSaveYes
End If
End Sub
And the macro with the following action:
Action
--------
OpenForm
Action Arguments
--------------------
Form Name: CompactDB
View: Form
Data Mode: Read Only
Window Mode: Normal
Thank You!
I was testing a code from Microsoft website: http://support.microsoft.com
I was successful but I needed to add 2 more lines of code without user intervention.
a) After compacting the database, I want to delete the old/original database.
b) Rename the new compacted database to the original name.
Does anyone know the right syntax to accomplish this?
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 compacting all databases in the DBNames table.
'===========================================
Dim StartTime As String
' Set this variable for the time you want compacting to begin.
StartTime = "12:00 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 old name plus the current date.
NewDBName = Left(DBName, Len(DBName) - 4)
NewDBName = NewDBName & " " & Format(Date, "MMDDYY") & ".mdb"
DBEngine.CompactDatabase DBName, NewDBName
RS.MoveNext
Loop
' Close the form, and then close Microsoft Access
DoCmd.Close acForm, "CompactDB", acSaveYes
RS.Close
DoCmd.Quit acSaveYes
End If
End Sub
And the macro with the following action:
Action
--------
OpenForm
Action Arguments
--------------------
Form Name: CompactDB
View: Form
Data Mode: Read Only
Window Mode: Normal
Thank You!
