Shutdown Microsoft Access database automatically
Step by step instructions on how shutdown Microsoft Access database automatically by adding code to the ‘On Timer’ event on a hidden form.
First, we need a form that is open all the time. It does not need to have focus or visible, it would be best if it is hidden, so it does not interfere with what else is going on.
Create the form
On the Ribbon: Create > Blank Form
Change the view to Design View
Add a label, this is just so that when you save the form, it will save. If a form does not have any content it is not saved. I added the text ‘Auto Shut down form’ but it does not matter what text you add.
Close the form to save it. Call it: frmAutoShutdown
AutoExec Macro
Next, we are going to create a Macro called AutoExec. If you name a Macro AutoExec, then this Macro will fire when the database first opens.
On the Ribbon: Create > Macro
Add New Action > Open Form
Form Name > Choose: frmAutoShutdown from the dropdown list
Window Mode = Hidden
This will hide the form when it opens.
Close and Save the Macro making sure you call it AutoExec.
Add code to the timer event to shutdown Microsoft Access database automatically
Reopen frmAutoShutdown in Design View.
Open the properties of the form if they are not already open (Right click on the little black square in the upper left-hand corner of the form and select properties).
Select the Event tab and next to the On Timer Event:
Click on the 3 dots.
Note: The image above may refer to a different event, ignore this, use the event mentioned previously in the text.
Select 'Code Builder' from the drop down list.
To the code already there add the following:
If Time >= "21:00" Then
Quit acQuitSaveAll
End If
The completed code should look like the below:
Private Sub Form_Timer()
If Time >= "21:00" Then
Quit acQuitSaveAll
End If
End Sub
The code above, checks the time and if it is later than 2100 hrs (9pm) then the code will shutdown the Microsoft Access database automatically.
Note: You can change the time but set if for a time that most will have finished working in the database by i.e. not too early in the day.
|
Tip: When cutting and pasting code, beware of talking marks "" or '', once the code is copy and pasted it is best to delete the talking marks and re-add them in as Microsoft Access sometimes does not recognise those copied. |
Close the coding window by clicking on the most outer right X.
The ‘On Timer’ event does not work on clock time, it just runs the code every so often, depending on what you set in the Timer Interval section. If the Timer Interval is set at 0 the code will not run.
The interval can be between 0 and 2,147,483,647 milliseconds.
The table below gives an idea for how many milliseconds you will need for different time intervals. Do not include the commas. They are included in the table below, so you can easily see how many zeros to add.
You may want to check that this is running as it should. If so, add a time in your code that is about 5 minutes in the future and change the Timer Interval to 300000 (5 minutes). DO NOT forget to change it back. :0). Do not leave it in Design View as the code will not run, open the form. The form will not be hidden, as it is only hidden when the database is opened and the AutoExec Macro has been run.
Go and have a break and come back and see if the database has been shut down automatically.
In frmAutoShutdown: Change the time in the code and the Timer Interval back to meet your requirements.
Congratulations you have learnt to shutdown Microsoft Access database automatically.
