Suggestions for regular automatic compacting of backend DB

[rivereridanus]

Hello everyone,



I have created and manage an Access 2010 DB with a split backend and multiple front-ends for multiple users. My users leave their front ends open 24x7 (it's a 24x7 work environment) including when they leave for multiple days. For this reason I have implemented a timer which checks for a flag in a table, and the flag is raised when a user clicks a button to kick all the users out. I use this when something goes wrong in the backend db and I need to compact and repair it (and in the process break all the users' connections).

Up until now this has worked out well. In fact it still does- but I have to be there to click the button to start the process, and have to remember to do it whenever it needs to be done.

I want to figure out a way to automatically compact and repair the backend db (maybe 1x a week?), as well as back up the db without my intervention.
Any suggestions beyond putting in a really long timer event?

As always, Thanks for your help!

[ranman256]

I use a form timer to execute things just like that. I start the timer app and the default time is Set.

  'get the current backend db
sourceFile = vBackEndDb
   
   'compact
Application.CompactRepair sourceFile, destinationFile
   
   'rename sourcefile as archive
Name sourceFile As sourceFile & "_arc" & Format(Date, "yyyymmdd") & ".mdb"
   
    'rename current backend file
Name destinationFile As vBackEndDb

[rivereridanus]

Alright, sounds like this is the best way to go then. Thanks for your input!