how to close a database after delay

[melanie_s_m]

Firstly, many thanks to all the unsung heros of this site who offer their time & expertise to the VB challenged.

However, after months of using this site - I finally need to post.

I have an mde database that our staff access to look up clients & run reports (it's a split front/back end).
Long story - but need to close this database at 12.45pm everyday to update some back end tables (due to odd network, there's no way around this).

The person who runs this update has been having problems of people remaining in the database & so can't update it.
I fixed this by using the OnTimer event which fires from 12.45pm to 1.00pm asking people to exit out of the database.

Ok, I thought I'd fixed it. We now have people leaving the database open & going out to lunch ....

I would like it to now display a message saying that it's going to close the database & give a delay of a few seconds & then close it. Therein lies my problem.

I've searched the forum & cobbled together some code that I almost understand & almost works.

It happily brings up the message box at the correct time, but the delay & the closing of the database happens ONLY if the user presses OK.

I need to close it if the user has pressed OK
AND
I need to close if if the user is not there to press OK.​

I know that my problem is that the message box is waiting for a response before it proceeds, but I don't know how to get around this.

I have tried a couple of things, including trying to force the response, but no joy.

Help.

Private Declare Function timeGetTime Lib "Winmm.dll" () As Long

Private Sub Form_Timer()
    Static blnInitialized  As Boolean
    [INDENT]' Defaults to 0 (Zero, False) on Form Open.[/INDENT]
    Static lngFormOpenTime As Long     
    [INDENT]' Will hold the Form Open Time.[/INDENT]
    Dim MyTime
    MyTime = Time
[INDENT]' Will hold the Form Open Time.[/INDENT]
           
    If MyTime > TimeSerial(12, 45, 0) And MyTime < TimeSerial(13, 0, 0) Then
    MsgBox "Closing for update. Please try again in 15 minutes", vbMsgBoxSetForeground

        If   Not blnInitialized Then
             blnInitialized = True
             Me.TimerInterval = 1000
             lngFormOpenTime = timeGetTime()
        End If
     
    Application.Quit
    
   End If
    
End Sub

 

[Karma]

Hi,

If you do need a delay open a pop-up form with the message in a label rather than the message box. Then on the pop-up forms timer event put the application.quit command.

HTH

K.

 

[ghudson]

timed message box

This thread has everything you need to do what you want with closing the db down and also how to call a time message box that closes after X seconds which will allow your code to continue and close the idle db. Remote shutdown of a shared DB

 

[melanie_s_m]

Thanks!
In spite of searching (I promise!) I obviously missed yet another helpful bit of code.

In case anybody else has a similar problem:
I am using Access 2000 on Windows 2000.

I placed ghuson's code in a new module:

'Used with a timed message box
Public Property Get oMsgBox() As Object
    Set oMsgBox = CreateObject("WScript.Shell")
End Property

And the following code in my form's OnTimer event.
This form always remains open in my database, so I didn't need to create a new form to hold this.
This finds out the current system time & only runs this (eg message, & closing of dbase) if the current time is between 12.45pm & 1.00pm.
I had to modify the actual oMsgBox code (from ghuson's link), as it didn't seem to work with my system (it happily brought up the code, it just didn't close the dbase).

Private Sub Form_Timer()
Dim MyTime
'Find current system time
MyTime = Time

'Work out if the current time is during the update period
If MyTime > TimeSerial(12, 45, 0) And MyTime < TimeSerial(13, 0, 0) Then

'If so, alert user.  If user clicks OK, then dbase closes immediately
'Otherwise, dbase closes in 30 seconds
    oMsgBox.PopUp "This database needs to close, so we can update the files" & Chr(13) & "If you click 'Close' it will close immediately - otherwise it will automatically close in thirty seconds." & Chr(13) & "Please stay out of the database for 20 mintues.", 30, "Force close", vbMsgBoxSetForeground
 
    DoCmd.RunCommand acCmdExit
End If

End Sub

Once again, thanks to all - that will try & teach me to think on a Friday night!