The following provides a methodology to allow the developer to schedule and enforce periods of downtime for any number of databases.
Define a database, ie, TimeCtl. All users should have read access to the database, but must not be able to change/move/delete the database or anything within the database.
Define a table, tblLogOut on the TimeCtl database having the following fields per row.
--ApplicationName text(50) primary key
--LogoffStart date/time
--LogoffEnd datetime
--Inactive Boolean
you will have one row for every active database. The application name is the database name only without the mdb. It is not the full path name. For example, if you database is DrugCourt.mdb the applicationname on tblLogOut is DrugCourt.
LogoffStart is the date and time you want to insure that all users cannot access applicationname. LogoffEnd is the date and time the users can begin to use applicationname again.
If the inactive flag is true, the record will never be checked again. This allows you to keep a historical track of your scheduled down time.
In TimeCtl, create a module called gfunctions (global functions). Cut and paste the following function into gfunctions.
Public Function funShutDown(DatabaseName As String) As Boolean: funShutDown = False
Dim db As Database
Dim rs As Recordset
Dim strSQL As String
DatabaseName = UCase(DatabaseName)
Dim myDate As Date: myDate = Now()
strSQL = "SELECT * FROM TBLLOGOUT WHERE APPLICATIONNAME = '" _
& DatabaseName & "' AND INACTIVE = 0 AND #" & myDate & "# BETWEEN LOGOFFSTART AND LOGOFFEND"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
If rs.RecordCount = 0 Then
GoTo OutShutDown
Else
funShutDown = True
End If
OutShutDown:
rs.Close
Set rs = Nothing
Set db = Nothing
Exit Function
End Function
WATCH OUT FOR WORD WRAP.
On each database that is now represented on tblogout, establish a reference to the timecontrol database. This will allow you to call funShutDown from that database.
Link tbllogout to your database. Again, we don’t care if the user can see this table as long as he can’t do anything else but see information.
In your application database create an unbound form with one textbox. Place the following code on the open form event.
Private Sub Form_Open(Cancel As Integer)
If funShutdown(UCase("drug court")) = True Then
Application.Quit
End If
End Sub
This insures that a user who attempts to log on during the scheduled down time will fail to do so.
Set the forms timer interval to 300000 (5 minutes). The following is the forms timer event.
Private Sub Form_Timer()
If funShutdown("drug court") = True Then
Application.Quit
End If
End Sub
In the startup routine of your database, open this form hidden.
Every 5 minutes, your database will test if it should shut down. If down time is scheduled it will shut down within 5 minutes of the downtime start time.
Reference:
http://www.tek-tips.com/faqs.cfm?fid=2145
