The Jet 4.0 Exchange/Outlook IISAM

<< Click to Display Table of Contents >>

Navigation:  Smart Access 1996-2006 > Aug-2000 >

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.

Constant name

Value

Meaning

FT_HASNOSUBFOLDERS

&H0&

The folder has no subfolders under it

FT_HASSUBFOLDERS

&H10000000

The folder has at least one subfolder under it

FT_GENERICSUBFOLDER

&H1&

Generic folder type

FT_INBOX_MASK

&H1000000

The Inbox folder for the MAPI store

FT_OUTBOX_MASK

&H2000000

The Outbox folder for the MAPI store

FT_WASTEBASKET_MASK

&H3000000

The Deleted Items folder for the MAPI store

FT_SENT_ITEMS_MASK

&H4000000

The Sent Items folder for the MAPI store

FT_TASKS_MASK

&H5000000

An Outlook Tasks folder

FT_CALENDAR_MASK

&H6000000

An Outlook Calendar folder

FT_CONTACTS_MASK

&H7000000

An Outlook Contacts folder

FT_JOURNAL_MASK

&H8000000

An Outlook Journal folder

FT_NOTES_MASK

&H9000000

An Outlook Notes folder

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.

Part

Meaning

Additional Notes

ColX

The column number

This starts with column 1 and must increase without gaps (1, 2, 3, and so on). The ColX acts as the value name part of the .INI file entry, and is always required.

Name

The column name

The name of the column. If the name contains spaces, then it must be surrounded with double quotes. The name is also always required.

Type

The column's datatype

The datatype of the column (all possible datatypes that the IISAM will recognize are shown in Table 3). This column is always required as well.

Width

The column width

The width of the column in bytes, which is required any time the datatype is Char (the equivalent of a Jet text field, which can have up to 255 characters or 510 bytes).

Scale

The column scale

The scale of the column, which is required any time the datatype is Decimal.

Precision

The column precision

The precision of the column, which is also required any time the datatype is Decimal.

Tag

The column's MapiTag

The MapiTag, which the IISAM requires any time the column is not one of the known, standard MAPI tags defined in the IISAM (this would include custom columns and special columns in Outlook). It is shown as a signed integer representation of what MAPI gurus would typically view as a hexidecimal number. You 'll only ever see this entry for custom columns that you define (discussed later in the article).

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

Datatype

DAO equivalent

ADO equivalent

MAPI type

MAPI Type Value

Binary

dbBinary

AdBinary

PT_BINARY

&H102&

Bit

dbBoolean

AdBoolean

PT_BOOLEAN

&HB&

Byte

dbByte

--

PT_I2

&H2&

Char

dbText

AdWChar

PT_STRING8

&H1F&

Currency

dbCurrency

AdCurrency

PT_CURRENCY

&H6&

DateTime

dbDate

AdDate

PT_SYSTIME

&H40&

Decimal

dbDecimal

AdDecimal

PT_DOUBLE

&H5&

Double

dbDouble

AdDouble

PT_DOUBLE

&H5&

Float

dbFloat

--

PT_DOUBLE

&H5&

GUID

dbGuid

AdGUID

PT_CLSID

&H4&

Integer

dbLong

AdInteger

PT_LONG

&H3&

Long

dbLong

AdInteger

PT_LONG

&H3&

LongBinary

dbLongBinary

AdLongVarBinary

PT_BINARY

&H102&

LongChar

dbMemo

AdVarWChar

PT_STRING8

&H1F&

Memo

dbMemo

AdVarWChar

PT_STRING8

&H1F&

OLE

dbLongBinary

AdLongVarBinary

PT_BINARY

&H102&

Short

dbInteger

 

PT_I2

&H2&

Single

dbSingle

AdSingle

PT_R4

&H4&

Text

dbText

AdWChar

PT_STRING8

&H1F&

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.

Constant name

Value

Meaning

COL_NOTINDEFAULTVIEW

&H0&

The column isn't currently in the default view for the folder.

COL_INDEFAULTVIEW

&H40000

The column is in the default view for the folder.

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.

Parameter

Explanation

IndexX

Starts with 1 and must increment without gaps (just as the ColX parameter does for column definitions).

Name

The name of the index, which is crucial when Seek is being used.

Field

Consists of a plus or minus sign ("+" or "–") to indicate whether the index is ascending or descending, the name of the column (surrounded by quotes if there is a space in the name), and ending with a ` character.

Flags

A combination of one or more of the flags in Table 6.

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

Index type

Flag value

Unique values not required

0

Unique values required

1

Primary index

2

Disallow NULL values

4

Ignore NULL values

8

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.

Read about the download  008KAPLAN.zip on this page

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.