- The ADO/OLE DB Conspiracy
- What Does ADO 2.5 Have to Do with Exchange 2000?
- The Role of the Web Storage System
- The Exchange OLE DB Provider
- File URLs
- HTTP URLs
- Programmatically Getting a User's HTTP Mailbox Folder URLs
- The Open Method of the ADO 2.5 Record Object
- Using Web Storage System SQL to Get a List of Folders
- Web Storage System SQL
- The SELECT Statement
- The ORDER BY Clause
- The RANK BY Clause
- Some Unsupported SQL
- Summary
The Exchange OLE DB Provider
The final key to accessing Exchange data through ADO 2.5 is the Exchange OLE DB provider (ExOLEDB). Exoledb.dll is installed with the Exchange 2000 Server and is used to access Web Storage System items on a local server. This is really important. The Exchange OLE DB provider is a server-side component, meaning that currently it can be used only to access public folder and mailbox stores that reside locally on the server. ExOLEDB is designed for use in COM+ components, ASP pages, and Web Storage System event sinks that run on the server. It is not designed for use from a client application for accessing back-end data on a remote Exchange server.
Therefore, all of the examples here will work only if they are run on the same machine where Exchange 2000 resides. So to follow along, you need to be on a machine running Windows 2000 and Exchange 2000 and have a COM-compliant development language such as Microsoft Visual Basic or Visual Basic Scripting edition handy. We'll be using VB 6, but with a few modifications the sample code will work in VBScript. MDAC 2.5, which contains ADO 2.5, comes with Windows 2000, so you're all set there. Finally we recommended that you obtain the Exchange 2000 Software Development Kit (SDK) from the Exchange Server developer center (http://msdn.microsoft.com/exchange/). It is chock-full of documentation and sample code on all of the technologies described in this book.
To start, create a new Standard EXE project in Visual Basic 6.0 and set a reference to the Microsoft ActiveX Data Objects 2.5 library (from the Project menu in the VB integrated development environment), as shown in Figure 7.4. While you're at it, set a reference to the Microsoft CDO for Exchange 2000 library as well. This is part of the standard Exchange 2000 installation and defines some Exchange-specific constants, which we'll use later.
){kind=link}
Figure 7.4
For reference, the ADO 2.5 library
Opening an ADO Connection to Exchange
The first thing we need to do is tell ADO that we want to deal with the Exchange OLE DB provider (and not another OLE DB provider, such as the SQL Server OLE DB provider). If you want to create an ADO Connection object, you must set the Provider property to "ExOLEDB.DataSource." The Visual Basic code that follows opens the Administrator's inbox on the machine represented in Figure 7.3. We declare a global constant in our project that we'll use whenever we have to reference the Exchange OLE DB provider. We declare a standard ADO Connection object and, for illustrative purposes only, a string variable in which we hard-code the complete URL path of the inbox. Just by setting the Provider property of the Connection object and using the Open method with the URL as an argument, we now have a reference to the Inbox folder:
){kind=link}
Option Explicit
Global Const gblsExchangeProvider As String = "ExOLEDB.DataSource"
Dim objConn As ADODB.Connection
Dim sURL As String
' Reference a folder on my Exchange server:
sURL = "http://gomezawin2000.gomezamobile.local/exchange/
Administrator/Inbox"
' Connect to the local Exchange server:
Set objConn = New ADODB.Connection
objConn.Provider = gblsExchangeProvider
objConn.Open sURL
As you may recall, we don't need to explicitly open a connection to Exchange. We could have just passed a URL to the Open method of an ADO Record object like so:
Dim objRec As ADODB.Record Set objRec As New ADODB.Record objRec.Open sSomeFileURL
However, there are advantages to explicitly opening a Connection object. You can use transactions in your code to bundle together a bunch of ADO operations as a single unit of work. You can access the Errors collection to see if your ADO code is working error free. Most importantly, you can write SQL SELECT queries against Exchange items in the Web Storage System, as we'll see later in this chapter.
Now that we know how to open a connection, creating Exchange items is not much more difficult.
Creating a Folder
The first thing we might want to do is create a folder. Let's create a form in our VB project called frmMain. Let's add to that two text boxes called txtConnect and txtNewFolder, respectively. Drop in a button on the form called cmdNewFolder. You should type in the name of the folder structure that you want to connect to in txtConnect. In the example of Figure 7.3, http://gomezawin2000.gomezamobile.local/public/ connects to the Public Folders hierarchy on the Exchange server. You should type in the full URL path to the folder that you want to create in txtNewFolder. In our example, http://gomezawin2000.gomezamobile.local/public/NewFolder will create a new public folder on the Exchange server named NewFolder. All that said, the following VB code does the trick:
Option Explicit
Global Const gblsExchangeProvider = "ExOLEDB.DataSource"
Private Sub cmdNewFolder_Click()
Dim objReturnRecord As ADODB.Record
Dim objConn As ADODB.Connection
Dim sConnect As String
Set objConn = New ADODB.Connection
' Get the existing folder structure to connect:
' Should be something like http://gomezawin2000.gomezamobile.local/public/
sConnect = frmMain.txtConnect.Text
' Connect to the local Exchange server:
objConn.Provider = gblsExchangeProvider
objConn.Open sConnect
Set objReturnRecord = CreateFolder(frmMain.txtNewFolder.Text, objConn)
End Sub
Public Function CreateFolder(ByVal sURL As String, _
ByVal objConn As ADODB.Connection) As
ADODB.Record
' The sURL argument is an HTTP URL to the new public folder we want to create.
' For example, let's pass in
' "http://gomezawin2000.gomeza.local/public/NewFolder" as sURL.
' The objConn as an ADO Connection to your Exchange server.
' The function returns the new folder as an ADO Record.
Dim objRec As ADODB.Record
Dim sContentClass As String
' Let Exchange know you want to create a folder:
sContentClass = "urn:content-classes:folder"
' Try to create the folder:
Set objRec = New ADODB.Record
objRec.Open sURL, objConn, adModeReadWrite, adCreateCollection
objRec.Fields("DAV:contentclass") = sContentClass
objRec.Fields.Update
' Set the return value:
Set CreateFolder = objRec
' Clean up:
objRec.Close
Set objRec = Nothing
End Function
A lot is going on here. The key in creating the folder was twofold: Use the Open method of the ADO Record object and feed it the correct URL. Let's first take a closer look at the syntax for referring to folders as URLs, and then we'll tackle the Open method.