Reputation: 23
I have some code, which works perfectly fine, that compacts an Access database when the database is opened.
However, I'm a little stuck as to how to do the next bit.
What I want to happen is that when an item within the DB table gets marked for archive and subsequently archived, I want this code to run almost like an AutoExec.
Could you please suggest the best way of doing this?
Thanks in advance
Option Compare Database
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
'starts to compact all databases in the DBNames table.
'==================================================================
Dim StartTime As String
' Set this variable for the time you want compacting to start.
StartTime = Now()
' 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, "DDMMYY") & ".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
Upvotes: 0
Views: 66