Compact and Repair

[joeythelips]

Hi,

i have been doing searches all morning to try and find the best way to solve my problem.

Basically what i want to do is automate my multi user access database to shutdown at a certain time every day. I also need to compact and repair the db at this stage. Finally i need to make sure that the data is cleanly backed up i.e i need to make sure that if some users have not logged off, that data is not lost.

Any ideas out there?

Joe

 

[FancyPrairie]

Check out this thread thread702-528470.

 

[joeythelips]

Hi FancyPrairie,

regarding your tip in the thread you pointed me to, what is the best way to check if the Shutdown flag is set? Or for that matter, what is a shutdown flag?

Thanks for your help

"As far as booting user's off your database, here's how I do it (actually a simplified version of what I do). At startup, open a form (i.e. frmTimer) whose visible property is set to false. Set the TimerInverval property to, say, 1 minute (60000 ms). In the OnTimer event, open a table and check to see if the ShutDown flag is set. If it is, then issue the command Application.Quit. You might consider issuing the Save Record command prior.

Also, you might want to give the user time to exit your application. If so, this table could also contain a DisplayMsg flag. If the DisplayMsg flag is set, then frmTimer would display a message (i.e. "Please exit the database&quot. This gets a little more complicated, because you will need to reset the msg flag so the user doesn't see the msg every 1 minute until the system shuts down. If you need more detail on how this works, let me know."

 

[FancyPrairie]

A shutdown flag is anything you want it to be. It could be a simple yes/no field in your table. For example, ysnShutDown. When you want to boot users off the database, simply set the ysnShutDown flag to "Y". When the OnTimer event runs (via the form frmTimer) on the user's machine it will open the table and check to see if the ysnShutDown flag is set to "Y". If so, then issue the Application.Quit command. If it's set to "N", then simply close the table and exit out of the OnTimer event.

Here's an example, (need to set a reference to Microsoft DAO 3.6 Object Library)

Private Sub Form_Timer()

dim dbs as DAO.Database
Dim rst as DAO.Recordset

On Error Goto ErrHandler

Set dbs = Currentdb
Set rst = dbs.OpenRecordset("Select ysnShutDown from YoutTable;", , dbReadOnly)
If (rst!ysnShutDown) Then Application.Quit
rst.Close

'********************
'* Exit Procedure *
'********************

ExitProcedure:

Exit Sub

'****************************
'* Error Recovery Section *
'****************************

ErrHandler:

MsgBox Err.Description, vbExclamation

Resume ExitProcedure

End Sub