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

ODSCm

New Member
Joined
Dec 18, 2020
Messages
13
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,059
Office Version
  1. 365
Platform
  1. Windows
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:
VBA 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:
VBA Code:
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

New Member
Joined
Dec 18, 2020
Messages
13
Office Version
  1. 2016
Platform
  1. Windows
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:
VBA 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:
VBA Code:
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

Well-known Member
Joined
Jun 3, 2015
Messages
2,059
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
Aug 12, 2008
Messages
236
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:

VBA Code:
[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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,829
Messages
5,627,143
Members
416,223
Latest member
RichardHell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top