How to automatically run VBA code between specific hours for each week in MS Access application

[ODSCm]

I have a MS Access application that is used in multiuser environment. I want to make maintanence every thursday between 15:00 and 16:00. At this point I have two issues to consider.

Firstly, The users who may entered to application before 15:00.
Secondly, the users who will be entering between 15:00-16:00

For the second scenario, I need to prevent my users to access the MS Access application. For this purpose, I have written the following code into the load event my beginning form.

Private Sub Form_Load()
MsgBox "Between 15.00 and 16.00 on Thursdays access is not available due to maintenance", vbOKOnly*
DoCmd.CloseDatabase
End Sub

It worked in a way I would like. The only thing is now to restrict it to work between specific hours as I described. How can I integrate time parameter to my code?

And for first scenario, For all users in the system wherever they are, close database function should be working and Application should be closed automatically.

I appreciate any of help. Thanks in advance.

 

[jackd]

Using Google with criteria ms access kick user off for maintenance example for examples.

ms access kick user off for maintenance example - Google Search

 

[Micron]

Not sure that answers part 1, so...
I would not use a message box because if the user does not click OK the db will never close. The message box function will suspend code until user dismisses it. Instead, open a message form that you create (in my example that is frmMessage). All it needs is a label with your message - make it modal and a dialog so that it cannot be minimized or closed by the user. In the opening form code:

Private Sub Form_Open()
If Time > #02:59:59 PM# And Time < #04:00:01# Then
  DoCmd.OpenForm "frmMessage"
  Pause 5 '<< see pause function below and remove this comment
  DoCmd.CloseDatabase
End If

End Sub

Time values could be dependent on your regional settings, so adjust accordingly. I have coded the times to the second.

Place this timer function in a standard module so that you can use it from anywhere:

Function Pause(intSecs As Integer)
Dim sngStart As Single

sngStart = Timer
Do While Timer < sngStart + intSecs
Loop

End Function

You can make this function more complicated if need be. Note that this isn't fool proof. If I can alter my system time I can still open the db. System time can also get out of sync.

 

[ODSCm]

Not sure that answers part 1, so...
I would not use a message box because if the user does not click OK the db will never close. The message box function will suspend code until user dismisses it. Instead, open a message form that you create (in my example that is frmMessage). All it needs is a label with your message - make it modal and a dialog so that it cannot be minimized or closed by the user. In the opening form code:

Private Sub Form_Open()
If Time > #02:59:59 PM# And Time < #04:00:01# Then
  DoCmd.OpenForm "frmMessage"
  Pause 5 '<< see pause function below and remove this comment
  DoCmd.CloseDatabase
End If

End Sub

Time values could be dependent on your regional settings, so adjust accordingly. I have coded the times to the second.

Place this timer function in a standard module so that you can use it from anywhere:

Function Pause(intSecs As Integer)
Dim sngStart As Single

sngStart = Timer
Do While Timer < sngStart + intSecs
Loop

End Function

You can make this function more complicated if need be. Note that this isn't fool proof. If I can alter my system time I can still open the db. System time can also get out of sync.

Hello Sir Micron, many thanks for the answer. I will apply it and share the result with you. But before that to get confirmed, Then I need to create a new form which will take a messagebox role. am I right?

And second, I did not get pause function. What is it doing here? after message with form camed to screen, is it providing a delay or?

 

[Micron]

Yes, but I suggested a form because a message box will lock up the db if no one clicks it. That might cause a delay in closing the db.
The function simply runs a loop for the amount of time you specify. The loop does nothing.
Without seeing your new code I cannot comment on your experience with it.

 

[apr pillai]

Use Windows Shell Script to show the message and automatically close the database after the specified time period. In the following example the time specified is 5 seconds:

[code]
Private Sub test()
Dim objWshShell As Object

Set objWshShell = VBA.CreateObject("WScript.Shell")
objWshShell.PopUp "After 5 seconds this database will be closed.", 5, "Title"
DoCmd.CloseDatabase
End Sub

It displays the OK Command Button. If clicked it closes immediately otherwise it will close after the specified time.

 

[jackd]

Depending on the details of your request, you may be able to use Windows Task Scheduler.

But you may have to kick users out of the system in order to gain single use control.