CODEWITHSUNDEEP
ms-accesscompact-databaseautoexec
james ellis
james ellis

Reputation: 23

Call an auto compact after event

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

Answers (0)

Related Questions