As some servers will presumably share the same maintenance windows I'd
suggest you first create a MaintenanceWindows table. You could use a
WindowStart date/time column and a WindowDuration number data type column if
you wish, or two date/time columns. I'd favour the latter as it makes
querying very simple.
You'll then need a table to model the many-to-many relationship type between
the Servers table and the MaintenanceWindows table, ServerMaintenanceWindows
say, in which as well as having a foreign key referencing the primary key of
Servers you could either have a composite foreign key referencing the
composite primary key of MaintenanceWindows, made up of the WindowStart and
WindowEnd columns, or you instead of the composite key you could have a
single column foreign key referencing a surrogate MaintenanceWindowID column
of MaintenanceWindows. I'd probably go for the former and enforce cascade
updates.
To fill the table you can use the following procedure, which is an
adaptation of one from my 'toolkit' for creating calendar tables. It assumes
no maintenance windows will span midnight and will all be less than 24 hours:
Public Sub FillMaintenanceWindows(strTable As String, _
dtmStart As Date, _
dtmEnd As Date, _
dtmStartTime As Date, _
dtmEndTime As Date, _
ParamArray varDays() As Variant)
Dim dbs As DAO.Database, tdf As DAO.TableDef
Dim strSQL As String
Dim dtmdate As Date
Dim varDay As Variant
Dim lngDayNum As Long
Set dbs = CurrentDb
' does table exist? If so get user confirmation to delete it
On Error Resume Next
Set tdf = dbs.TableDefs(strTable)
If Err = 0 Then
If MsgBox("Replace existing table: " & _
strTable & "?", vbYesNo + vbQuestion, _
"Delete Table?") = vbYes Then
strSQL = "DROP TABLE " & strTable
dbs.Execute strSQL
Else
End If
End If
' create new table if doesn't exist
strSQL = "CREATE TABLE " & strTable & _
"(WindowStart DATETIME, WindowEnd DATETIME," & _
"CONSTRAINT PrimaryKey PRIMARY KEY (WindowStart, WindowEnd))"
dbs.Execute strSQL
On Error GoTo 0
' refresh database window
Application.RefreshDatabaseWindow
For dtmdate = dtmStart To dtmEnd
For Each varDay In varDays()
If Weekday(dtmdate) = varDay Then
lngDayNum = lngDayNum + 1
strSQL = "INSERT INTO " & strTable & _
"(WindowStart, WindowEnd) " & _
"VALUES(#" & Format(dtmdate + dtmStartTime, "yyyy-mm-dd
hh:nn:ss") & "#,#" & _
Format(dtmdate + dtmEndTime, "yyyy-mm-dd hh:nn:ss") & "#)"
dbs.Execute strSQL
End If
Next varDay
Next dtmdate
End Sub
Watch out for any lines in the above which your newsreader may have split
over two lines.
To create and fill a table MaintenanceWindows with slots from 1:00 AM to
6:00AM every Saturday and Sunday from 1 January 2008 to 31 December 2015 say
you'd call it with (all as one line)
FillMaintenanceWindows
"MaintenanceWindows",#2008-01-01#,#2015-12-31#,#01:00#,#06:00#,vbSaturday,vbSunday
To add different maintenance windows to the table you'd simply call the
procedure again passing in different values for the slots.
To fill the ServerMaintenanceWindows table with a series of regular slots
needs a simple 'append' query; for Server 123 say to use the slots inserted
with the line above:
INSERT INTO ServerMaintenanceWindows
(ServerID, WindowStart, WindowEnd)
SELECT 123, WindowStart, WindowEnd
FROM MaintenanceWindows
WHERE WEEKDAY(WindowStart) = 1
AND TIMEVALUE(WindowStart) = #01:00#
AND TIMEVALUE(WindowEnd) = #06:00#;
Ken Sheridan
Stafford, England
