This article may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist. To maintain the flow of the article, we've left these URLs in the text, but disabled the links.

The Jet 4.0 Exchange/Outlook IISAM

Michael Kaplan

Microsoft's security patch to help avoid e-mail viruses causes mailmerge and other applications to break. Jet comes to the rescue with its ability to treat Exchange mail data like a Jet table. Michael Kaplan takes you under the hood to show you how Jet connects to MAPI in Access 2000.

The comedian Rodney Dangerfield used to complain that he got no respect. However, compared to the Jet Exchange/Outlook IISAM, he was almost a beloved statesman. This link between Access and Exchange gets so little attention in every newsgroup, journal, magazine, and newsletter (including Smart Access) that I was almost embarrassed to have done a great deal of work on a wizard that exposes some of the IISAM's functionality. The Exchange/Outlook IISAM lets you attach folders from your Outlook or Exchange client to your Access database and use their data as part of your application.

Lately, in the wake of the ILOVEYOU, JOKE, and other viruses, Microsoft has released a security patch for Outlook that's going to break a lot of Outlook applications that do mailmerge and other operations. This has generated new interest in safe, programmatic methods for accessing MAPI data, leading to this article on the Jet IISAM. With some help and encouragement from several people, including Microsoft MVP Sue Mosher (the genius behind http://www.slipstick.com), I'll show how to use the IISAM.

Since Access 97, when I first looked at this new IISAM's capabilities, I've always thought of it as the "MAPI IISAM." So, why isn't it called that? If you've ever dealt with MAPI before, you know that there's wide variability in the compliance of various MAPI providers, which makes the term "MAPI" a little suspect to developers. Also, at the time when Access 97 came out along with the Exchange IISAM, Microsoft Office marketing was gearing up to promote the not-yet-released mail client Outlook. As a result, the official name came to be the "Exchange/Outlook IISAM." If you notice me using different names throughout the article for this tool, don't panic. It's just me adding a little variety.

This article will focus on using the version of the IISAM that ships with Jet 4.0 (msexch40.dll), and won't really cover the Jet 3.5 IISAM (msexch35.dll). Most of what I cover here is either features that didn't exist in the Jet 3.5 version, or features that are implemented in a totally different way in Jet 4.0. However, since not very much information about the 3.5 IISAM was ever made available, I'll try to cover as much about version 3.5 as I possibly can, rather than just doing a "what's new" article.

Getting at your folders

Microsoft has put quite a bit of work into the new IISAM. Back at the end of 1997, when Beth Scott (formerly a program manager for Jet) was looking at the planned work for the IISAM, she worried that the next version of Exchange ("Platinum") was going to ship within a month of Office 2000, making all of the IISAM's new features useless. In the end, she decided that it was important to deal with the known product and not rely on the ship cycle of other products. In retrospect, since Exchange 2000 hasn't yet hit the streets, she made a wise decision. Since the IISAM will, without changing code, work just as well against Platinum mail stores as the original MAPI stores, the Jet provider remains useful if you do upgrade to Exchange 2000. Exchange 2000 also ships with an OLE DB provider, but unfortunately you can't bind it to an Access form or report.

Since there's an ADO provider for Exchange, you might wonder why I'm suggesting DAO instead of ADO for dealing with Exchange data. The major reason is philosophical: One of the premier strengths of ADO has always been how it got away from the hierarchical nature of the DAO object model. Since Jet treats MAPI data hierarchically, and this matches the way that MAPI organizes folders, this is one place where DAO makes more sense to me. It's possible, however, to do the same type of work with ADOx, and I'll give some examples of it later in this article.

The first step in working with the MAPI folders is to retrieve the top-level MAPI stores. Those stores are the actual containers for folders in Exchange/Outlook. They have names such as "Personal Folders" or "Public Folders." To get these folders, you have to open a top-level object. This code lists all of the top-level objects that are available:

Sub EnumerateTopLevelObjects()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim stConnect As String

stConnect = "Exchange 4.0;MAPILEVEL=;"
Set db = _
  OpenDatabase("c:\temp\", False, False, _
                stConnect)
For Each tdf In db.TableDefs
   Debug.Print "FOLDERS: " & tdf.Name
Next tdf
db.Close
    
stConnect = "Exchange 4.0;MAPILEVEL=;TABLETYPE=1"
Set db = OpenDatabase("c:\temp\", False, False, _
                       stConnect)
For Each tdf In db.TableDefs
   Debug.Print "ADDRESS BOOKS:" & tdf.Name
Next tdf
db.Close

End Sub

This code will enumerate all of the top-level MAPI stores/address books in your current MAPI profile and then go on to the Windows Address Book. On my machine, the output of this routine looks like this (your system's output will be different, of course):

FOLDERS: Personal Folders
FOLDERS: Public Folders
FOLDERS: Mailbox - Michael Kaplan
ADDRESS BOOKS:Global Address List (Offline)
ADDRESS BOOKS:Personal Address Book
ADDRESS BOOKS:CompuServe Address Book
ADDRESS BOOKS:Address Book

The ADO version of the code looks only slightly different:

Sub Test()
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
    
Set cat = New ADOX.Catalog
cat.ActiveConnection = _
  "Provider=Microsoft.Jet.OLEDB.4.0;" & _
  "Exchange 4.0;MAPILEVEL=;Database=c:\temp;"
    
For Each tbl In cat.Tables
   Debug.Print "FOLDERS:" & tbl.Name
Next tbl
    
Set cat = New ADOX.Catalog
cat.ActiveConnection = _
   "Provider=Microsoft.Jet.OLEDB.4.0;" & _
   "Exchange 4.0;MAPILEVEL=;TABLETYPE=1;" & _
   "Database=c:\temp;"
    
For Each tbl In cat.Tables
   Debug.Print "ADDRESS BOOKS:" & tbl.Name
Next tbl
    
Set cat.ActiveConnection = Nothing
Set cat = Nothing
End Sub

Once you've retrieved the top-level objects, you can look at any level underneath them by using the OpenDatabase method, passing an appropriate MAPILEVEL. Once you do, the TableDefs collection of the Database object returned by OpenDatabase will contain a list of all of the folders within the container. This procedure will iteratively enumerate all of the folders or all of the address books:

Private Const FT_HASSUBFOLDERS = &H10000000

Public Sub EnumerateChildren( _
 Optional ByVal fFolders As Boolean, _
 Optional ByVal iLevel As Long = 1, _
 Optional ByVal stMapiLevel As String)
    
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim stConnect As String
    
stConnect = "Exchange 4.0;MAPILEVEL=" & _
               stMapiLevel & ";"
stConnect = stConnect & "TABLETYPE=" & _
               Abs(Not fFolders) & ";"
Set db = OpenDatabase("c:\temp\", False, _
               False, stConnect)
iLevel = iLevel + 1
For Each tdf In db.TableDefs
  Debug.Print String$(iLevel - 1, " ") & tdf.Name
  If (tdf.Attributes And FT_HASSUBFOLDERS) = _
           FT_HASSUBFOLDERS Then
      Call EnumerateChildren(fFolders, iLevel, _
      AlterMapiLevel(stMapiLevel, tdf.Name, _
               iLevel, fFolders))
   End If
Next tdf
End Sub

Public Function AlterMapiLevel( _
  stMapiLevel As String, _
  stChild As String, _
  ByVal iLevel As Long, _
  fFolder As Boolean) As String
 
If Not fFolder Then 
  iLevel = iLevel - 1
End If
Select Case iLevel - 1
   Case 1
      AlterMapiLevel = stChild & "|"
   Case Else
      AlterMapiLevel = stMapiLevel & "\" & _
                       stChild & "\"
End Select
End Function

The EnumerateChildren routine accepts a single parameter, which indicates whether you want to list MAPI folders (True) or address books (False). To enumerate all of the folders and then all of the address books, you call the routine twice:

EnumerateChildren True
EnumerateChildren False

In this code, I've skipped many of the details involved, and now it's time to go back and explain them.

MAPI folders

In DAO, both TableDefs and Fields objects contain an Attributes property, which the IISAM uses to make essential data about the object available to you. ADO doesn't support any of these items (unfortunately).

Table 1 shows how many of the DAO attribute values are defined. The first two flags (HASNOSUBFOLDERS and HASSUBFOLDERS) can be combined with any one of the 10 flags that follow. This makes sense because whether or not a folder has subfolders is completely independent of what kind of folder it is (and any folder can only be of one type). Most of the TableDef-level flags are really not very important and just exist so that the Access wizard can show the special folders with interesting icons. The first two flags are crucial to avoiding errors when working with folders. As I said, you use the OpenDatabase method to gain access to the folders contained within a folder. With most folders that have no children, using OpenDatabase will succeed and return a TableDefs collection with a.Count of zero. However, using OpenDatabase with the Windows Address Book (a folder with no children) will give you a runtime error. You should check the FT_HASSUBFOLDERS flag not only to avoid opening folders that don't contain anything, but to keep a runtime error from occuring in your enumeration code (and, yes, I do believe that the runtime error with the WAB is a bug).

Table 1. The DAO TableDef Attributes flags and the possible values used by the IISAM.

Tables

The Jet 3.5 IISAM needed a way to keep track of what columns to supply for a folder. To support this, a Jet database that connected to MAPI would contain two special system tables (if they didn't exist, the IISAM would create them). Those system tables have been replaced by a specially formatted Schema.ini text file (again, the IISAM will create this for you). Since the file is in the standard .INI file format, it can be read from and written to via the API calls GetPrivateProfileString and SetPrivateProfileString. Each section heading in the file represents a folder. Some sample section names are:

[1 – Exchange – Inbox]
[1 – Exchange – User Created Folder]
[1 – Outlook – Inbox]
[2 – Exchange – User Created Folder]

Each section name, as shown here, has three parts:

• A number, which is used to make the section name unique (INI files cannot repeat section names). This is important because you can have the same folder name repeated many times within the same or different MAPI stores.
• Either "Exchange" or "Outlook." This string controls whether some of the default MAPI column names follow the conventions of the Exchange or Outlook mail client.
• The name of the address book or folder itself.

Inside each file section is the information that describes the folder or address book.

The first two entries in the section relate to the way MAPI defines folders and address books. These define the EntryId, and look like this:

IdSize=24
IdBytes=00 00 00 00 E3 EA 38 B4 D5 6D D0 11 A3 
   92 00 A0 24 B0 61 F1 A2 80 00 00

The IdSize lists how many bytes are in the EntryID, while the IdBytes lists the actual bytes that make up the EntryId. While the actual meaning of an EntryId to MAPI is beyond the scope of this article, it provides the way that MAPI uniquely identifies a folder.

After this comes all of the information about the columns that make up the folder/table. The format of each entry of column information is as follows:

ColX=ColName ColType [ColWidth] [ColScale] [ColPrecision] [MapiTag]

The meaning of each piece of information can be found inTable 2. In Table 3 you can find the datatypes that'll appear in your Access table/MAPI folder.

Table 2. The definition of the various parts of the column definitions in schema.ini.

Table 3. The various allowable data types in the IISAM's Schema.ini file.

The first column in Table 3 gives a generic description of the datatype. Columns two and three provide the equivalent DAO and ADO data types. The last two columns show the MAPI data types that are used in the Schema.ini file. The Tag value in the Schema.ini file combines the DISPID of the column (a unique number that's assigned to each column) in the top two bytes, and the datatype from Table 3 in the bottom two bytes. Here are some sample column entries:

Col1=Importance Integer
Col2=Icon Char Width 510
Col3=Priority Integer
Col4=Subject Char Width 510
Col5="Message To Me" Bit
..
Col20=Custom1 Char Width 50 Tag –2135883710
Col21=Custom2 Char Width 100 Tag –2135816703
Col22=Custom3 Float Tag –2135246732

Note that the columns must start at 1 and increase without gaps, so the missing columns 6-19 are absent just to save some space in this article.

From within DAO, each column has two attributes, listed inTable 4.

Table 4. The DAO Field Attributes flags and the possible values used by the IISAM.

The last part of the definition of the folder in Schema.ini is another new feature of the version 4.0 IISAM, the index definition.

Indexable columns

One of the most common complaints about the Jet 3.5 IISAM was how long it took to search and sort MAPI data. Most of the problem was due to the IISAM not taking full advantage of MAPI's ability to perform searches and sorts. In Jet 4.0, you're allowed to specify indexes and actually perform Jet Seek operations on the data.

To specify an index, you make entries in the Schema.ini file following all of the column definitions I discussed in the previous section. The format for the definition of an index is as follows:

IndexX=Name [+|-]Field1'[+|-]Field2' Flags

The various parameters that make up the index are explained in Table 5. The possible index flags that can be combined to specify the type of the index are listed in Table 6.

Table 5. The parts of the index definition in the Schema.ini file.

Table 6. The index flags that are supported in the IISAM's Schema.ini file.

For my example, this entry identifies the index created by the IISAM on address books (which is actually the only search field that MAPI itself allows on address books). The value of 7 indicates that Index1 requires unique values (1), is a primary index (2), and won't allow NULL values (4):

Index1=Primary "+Display Name`" 7

Although the IISAM doesn't automatically define any other indexes (the Access wizard doesn't define indexes either), you can define your own. The only bad thing is that you can't create indexes using DAO. You must define the index in the Schema.ini file in order to use the index later for sorting and searching purposes. Here are two examples of some user-defined indexes:

Index1=MyIndex1 +Custom1` 4
Index2=MyIndex2 +Custom1`-Custom2` 1

One important thing to keep in mind for these indexes is that MAPI isn't going to update these indexes for all of the other clients that can insert and modify items in your MAPI folders. Although the indexes will be used when you perform write operations through the IISAM from Jet, their primary use is in creating specifications that the IISAM can use to optimize the type of operations that you can perform on mail data. You are in a very real sense providing optimization hints for the IISAM to use when searching.

In this article, I've really just started to introduce how to work with Exchange and Outlook from Access. Next month, I'll be back with more information on how to access your mail data and revisit some of the issues that I've raised here. For instance, while you'll typically attach MAPI folders using the File | Get External Data menu choice, you can also use DAO code. If you do use DAO code, you can try to create indexes and it'll almost work. I'll discuss that "almost" in the next article, among other topics.

Dedication

This article is dedicated to two people: Luna Consulting Access developer Stephanie Hudson, who provided a lot of help and support (there's no question, Stephanie!), and Sue Mosher, who first helped convince me to write this article while we both were getting e-mail in London one night earlier this year. Slipstick rocks, Sue, and it's mainly your fault!

Sidebar: Foundation Concepts: MAPI and IISAM

MAPI (Mail Application Programming Interface) is Microsoft's standard interface for accessing mail functionality. Regardless of which mail system you have installed on your computer, your application should be able to use the functions that make up MAPI to send and read mail. Access uses MAPI to implement its SendMail macro command.

ISAM (Indexed Sequential Access Method) describes a kind of file type where records are read and written in sequential order but can also be retrieved using indexes and keys. Most desktop database systems (Jet, dBase, Paradox) are, in some sense of the term, ISAM systems. IISAM (Installable ISAM) allows you to load the code to read and write a specific database on an as-needed basis. Jet comes with IISAMs for dBase, Excel, and text files. The amount of functionality supported for each file type will vary from IISAM to IISAM.

The Jet Outlook IISAM allows you to attach folders from your MAPI data source by selecting File | Get External Data and selecting Outlook or Exchange in the Files of Type list box. The wizard that appears when you select those types will walk you through the process of connecting an Outlook folder to your database as a Jet table. One important difference between Outlook folders and Jet tables is that Outlook folders can contain other folders. As a result, the IISAM must provide a way of handling folders within a folder. It does this by treating a folder as a database and requiring that, when you open a folder from DAO code, you use the OpenDatabase method.

To find out more about Smart Access and Pinnacle Publishing, visit their website at http://www.pinpub.com/html/main.isx?sub=57

Note: This is not a Microsoft Corporation website. Microsoft is not responsible for its content.

This article is reproduced from the August 2000 issue of Smart Access. Copyright 2000, by Pinnacle Publishing, Inc., unless otherwise noted. All rights are reserved. Smart Access is an independently produced publication of Pinnacle Publishing, Inc. No part of this article may be used or reproduced in any fashion (except in brief quotations used in critical articles and reviews) without prior consent of Pinnacle Publishing, Inc. To contact Pinnacle Publishing, Inc., please call 1-800-493-4867 x4209.