I'm using the following code:
The intent is to run automated back end maintenance activities while giving the user the option to delay it if they're in the middle of something.
My logic is as follows:
If it's between these two times, ask the user if the maintenance can run.
If user says yes, run maintenance.
If user says no shift the two times to the right and ask again in a few minutes.
As it is written, when I chose no I get an hourglass icon and if I try to do anything else Windows tells me Access isn't responding. I'm guessing that Access is hanging since I've initiated the Do Until and then stalled it by telling it to wait 3 minutes before it goes to the next step. I suspect that if I waited it out, when the new Startwindow hit I would be asked about running the maintenance again.
My issue is that I need the database to remain responsive to whatever the user needs to do while I wait to ask about running maintenance again. Therefore, I don't think Do Until is my answer - at least not the way I've implemented it.
Anyone have any suggestions to accomplish my goal? The will ultimately be set on a 10 minute timer interval and start/end window will be 01:00 and 01:15 AM respectively. The times you see above are there for testing so I can see what happens.
Code:
Private Sub Form_Timer()
Dim LocalTime, StartWindow, EndWindow As Date
LocalTime = Format(TimeValue(Now()), "hh:mm")
StartWindow = #11:46:00 AM#
EndWindow = #11:48:00 AM#
Dim Response
Response = MsgBox("Run scheduled maintenance?" & vbCrLf & vbCrLf & _
"Selecting YES will momentarily disable the database." & vbCrLf & _
"Select NO if you have unsaved work.", vbYesNo, "Scheduled Maintenance Required")
If LocalTime > StartWindow And LocalTime < EndWindow Then
Do Until Response = vbYes
'scheduled maintenance prompt
If Response = vbYes Then
'set logout flag - logout flag is monitored by other front ends to prevent back end connections
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryLogoutTrue"
DoCmd.SetWarnings True
'close frmHome - this disconnects front end from back end
DoCmd.Close acForm, "frmHome"
'display maintenance message
Me.Visible = True
'run the module to backup, compact and repair
CompactDB ("tblHotword")
'hide maintenance message
Me.Visible = False
'reset logout flag
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryLogoutFalse"
DoCmd.SetWarnings True
'reopen frmHome
DoCmd.OpenForm "frmHome"
Else
StartWindow = DateAdd("n", 3, StartWindow)
EndWindow = DateAdd("n", 3, EndWindow)
End If
Loop
End If
End SubThe intent is to run automated back end maintenance activities while giving the user the option to delay it if they're in the middle of something.
My logic is as follows:
If it's between these two times, ask the user if the maintenance can run.
If user says yes, run maintenance.
If user says no shift the two times to the right and ask again in a few minutes.
As it is written, when I chose no I get an hourglass icon and if I try to do anything else Windows tells me Access isn't responding. I'm guessing that Access is hanging since I've initiated the Do Until and then stalled it by telling it to wait 3 minutes before it goes to the next step. I suspect that if I waited it out, when the new Startwindow hit I would be asked about running the maintenance again.
My issue is that I need the database to remain responsive to whatever the user needs to do while I wait to ask about running maintenance again. Therefore, I don't think Do Until is my answer - at least not the way I've implemented it.
Anyone have any suggestions to accomplish my goal? The will ultimately be set on a 10 minute timer interval and start/end window will be 01:00 and 01:15 AM respectively. The times you see above are there for testing so I can see what happens.
