Using Calendar Folder Query

The Web Store expands recurring appointments and meetings when the calendar folder is queried through Microsoft® ActiveX® Data Objects (ADO). The instances are expanded within the range specified by the query. For example, if you query a calendar folder for all entries in the month of November, the Web Store creates the November instances of all recurring appointments and meetings. Although appointments and meetings can be stored in any Exchange folder, the Web Store expands only recurring appointments and meetings stored in calendar folders.

To expand recurring appointments, query the calendar folder using a select statement. Use the WHERE clause of the SELECT statement to specify the range of dates that are expanded within each recurring appointment. First the dates must be adjusted from local time to coordinated universal time UTC (because Web Store dates are stored in UTC), then, converted to ISO format, and finally cast to the dateTime data type, as shown in the following query.

The following code example shows how to query a calendar for a specific user. It expands any recurring appointments and meetings from 8:00 am October 25 to 5:00 pm October 29. This example prints the subject, start time, and end time of each appointment and meeting found. The code example includes functions to convert time zones and ISO format; be sure to change the time zone constants to the appropriate values for your clients.

[Visual Basic]

Dim CalendarURL As String
Dim Rs          As New ADODB.Recordset
Dim Rec         As New ADODB.Record
Dim dtStart     As Date
Dim dtEnd       As Date

dtStart = #10/25/1999 8:00:00 AM#
dtEnd = #10/29/1999 5:00:00 PM#

CalendarURL = "file://./backofficestorage/" & DomainName & "/MBX/" & UserName & "/calendar/"

'Open the record set for the items in the calendar folder
Rec.Open CalendarURL
Set Rs.ActiveConnection = Rec.ActiveConnection

Rs.Source = "SELECT ""DAV:href"", " & _
                  " ""urn:schemas:calendar:dtstart""," & _
                  " ""urn:schemas:calendar:dtend""," & _
                  " ""urn:schemas:calendar:duration""," & _
                  " ""urn:schemas:httpmail:subject"", " & _
                  " ""urn:schemas:calendar:instancetype""," & _
                  " ""urn:schemas:calendar:busystatus"" " & _
            " FROM scope('shallow traversal of """ & CalendarURL & """')" & _
            " WHERE (""urn:schemas:calendar:dtstart"" >= CAST(""" & GetISODate(TZ_ConvertDateCDO(dtStart)) & """ as 'dateTime')) AND " & _
              "(""urn:schemas:calendar:dtend"" <= CAST(""" & GetISODate(TZ_ConvertDateCDO(dtEnd)) & """ as 'dateTime'))"

'Rs.Source = "SELECT ""DAV:href"", " & _
'                  " ""urn:schemas:httpmail:subject"", " & _
'                  " ""urn:schemas:calendar:dtstart"", " & _
'                  " ""urn:schemas:calendar:dtend"" " & _
'            "FROM scope('shallow traversal of """ & CalendarURL & """') " & _
'            "WHERE (""urn:schemas:calendar:dtstart"" >= CAST(""1999-10-01T08:00:00Z"" as 'dateTime')) " & _
'            "AND (""urn:schemas:calendar:dtend"" <= CAST(""1999-11-01T08:00:00Z"" as 'dateTime'))"

Rs.Open , , adOpenStatic, adLockOptimistic

'Enumerate the record set and print each item's subject, start time, and end time
Rs.MoveFirst
Do Until Rs.EOF
  Debug.Print "Subject: " & Rs.Fields(CdoHTTPMail.cdoSubject).Value
  Debug.Print "Start time: " & Rs.Fields(cdoDTStart)
  Debug.Print "End time: " & Rs.Fields(cdoDTEnd)
  Debug.Print ""
Rs.MoveNext
Loop


Function GetISODate(dtDate As Date)
    GetISODate = Format(CStr(dtDate), "yyyy-mm-ddThh:mm:ssZ")
End Function

Function TZ_ConvertDateCDO(dtSrc As Date, _
                                   Optional dstTzId As CdoTimeZoneId = cdoPacific, _
                                   Optional srcTzId As CdoTimeZoneId = cdoPacific, _
                                   Optional srcTz As String = "", _
                                   Optional dstTz As String = "") As Date
    
    Dim obAppt      As New CDO.Appointment
    Dim obConfig    As New CDO.Configuration
    Dim bTz         As Boolean
    
    obAppt.Configuration = obConfig
    
    bTz = ((srcTz <> "") Or (dstTz <> ""))
    
    If (Not bTz) Then
        obConfig.Fields(cdoTimeZoneIDURN) = srcTzId
    Else
        obConfig.Fields(cdoTimeZoneURN) = srcTz
    End If
    obConfig.Fields.Update
    
    obAppt.StartTime = dtSrc
    
    If (Not bTz) Then
        obConfig.Fields(cdoTimeZoneIDURN) = dstTzId
    Else
        obConfig.Fields(cdoTimeZoneURN) = dstTz
    End If
    obConfig.Fields.Update
 
    TZ_ConvertDateCDO = obAppt.StartTime
    
End Function