Everytime I have to do some kind of BE updates, e.g. table updates (add new fields, change fields properties) I am unable tot do so because the BE is locked. I have to run around the building sutting down all front ends, in order to unlock the back end.
I figured some kind of internal shutdown feature for each FE would be handy.
The idea is very simple, struck me a couple of days ago when I was doing a on timer function. Works in my case on computers using the same FE and with the pc clock no more than 3 minutes off the pcc clock of the admin's pc
I built a table called shutdown with 3 fields.
TRIGGER
DATESTAMP
SHOWMSG
I linked a form to that table that controls the fields.
it has all ALLOWS to false with the exception of ALLOWEDIT
KEEP LIVE button marks TRIGGER with NO; DATESTAMP = NULL AND SHOWMSG =NO
SHUTDOWN BUTTON marks TRIGGER with YES; DATESTAMP =NOW(); SHOWMSG = YES
In order for the shutdown mechanism to work there must be a constantly open form on the FE, that never closes down. It can be hidden, not hidden whatever, as long as it is open.
In the ONTIMER (set timer to approx 1000) of that form write a code that analyzes the data from that table similar to this:
If SHUTDOWN() = -1 Then
If DLookup("[SHOWMSG]", "TBLSHUTDOWN"
= -1 Then
MsgBox "A Maitenance Shutown of the database will be automatically performed in less than 3 minutes"
End If
End If
If (Now() - SHUTDOWNTIME() > 0.0020832) And (Now() - SHUTDOWNTIME() < 0.0035) And (SHUTDOWN() = 0) Then
DoCmd.Quit acQuitSaveAll
End If
WHERE
SHUTDOWN() is a fucntion returning the value of the TRIGGER FIELD
SHUTDOWNTIME() is a function returning the value of the DATESTAMP field
0.0020832 = 3 minutes
And (Now() - SHUTDOWNTIME() < 0.0035) is a timeout safety, so that you don't lock yourself out of the database via automatic shutdown if Now() - SHUTDOWNTIME() > 3 minutes
And do not forget one thing:
in ONTIMER of the SHUTDOWN form, with the KEEPLIVE and SHUTDOWN Buttons put the following
Private Sub Form_Timer()
If SHOWMSG = -1 Then
SHOWMSG = 0
End If
DoCmd.Requery
End Sub
so that the MsgBox "A Maitenance Shutown of the database will be automatically performed in less than 3 minutes" does not pop up every second.
LET ME KNOW WHAT YOU THINK OF THIS. I KNOW THAT IT IS A LITTLBE BIT LIMITED AND RUDIMENTARY, BUT IT'S A QUICK AND DIRTY SHUTDOWN THAT ACTUALLY WORKS
Good luck,
Kuzz
"Time spent debating the impossible subtracts from the time during which you
can try to accomplish it."
I figured some kind of internal shutdown feature for each FE would be handy.
The idea is very simple, struck me a couple of days ago when I was doing a on timer function. Works in my case on computers using the same FE and with the pc clock no more than 3 minutes off the pcc clock of the admin's pc
I built a table called shutdown with 3 fields.
TRIGGER
DATESTAMP
SHOWMSG
I linked a form to that table that controls the fields.
it has all ALLOWS to false with the exception of ALLOWEDIT
KEEP LIVE button marks TRIGGER with NO; DATESTAMP = NULL AND SHOWMSG =NO
SHUTDOWN BUTTON marks TRIGGER with YES; DATESTAMP =NOW(); SHOWMSG = YES
In order for the shutdown mechanism to work there must be a constantly open form on the FE, that never closes down. It can be hidden, not hidden whatever, as long as it is open.
In the ONTIMER (set timer to approx 1000) of that form write a code that analyzes the data from that table similar to this:
If SHUTDOWN() = -1 Then
If DLookup("[SHOWMSG]", "TBLSHUTDOWN"
= -1 ThenMsgBox "A Maitenance Shutown of the database will be automatically performed in less than 3 minutes"
End If
End If
If (Now() - SHUTDOWNTIME() > 0.0020832) And (Now() - SHUTDOWNTIME() < 0.0035) And (SHUTDOWN() = 0) Then
DoCmd.Quit acQuitSaveAll
End If
WHERE
SHUTDOWN() is a fucntion returning the value of the TRIGGER FIELD
SHUTDOWNTIME() is a function returning the value of the DATESTAMP field
0.0020832 = 3 minutes
And (Now() - SHUTDOWNTIME() < 0.0035) is a timeout safety, so that you don't lock yourself out of the database via automatic shutdown if Now() - SHUTDOWNTIME() > 3 minutes
And do not forget one thing:
in ONTIMER of the SHUTDOWN form, with the KEEPLIVE and SHUTDOWN Buttons put the following
Private Sub Form_Timer()
If SHOWMSG = -1 Then
SHOWMSG = 0
End If
DoCmd.Requery
End Sub
so that the MsgBox "A Maitenance Shutown of the database will be automatically performed in less than 3 minutes" does not pop up every second.
LET ME KNOW WHAT YOU THINK OF THIS. I KNOW THAT IT IS A LITTLBE BIT LIMITED AND RUDIMENTARY, BUT IT'S A QUICK AND DIRTY SHUTDOWN THAT ACTUALLY WORKS
Good luck,
Kuzz
"Time spent debating the impossible subtracts from the time during which you
can try to accomplish it."
