The Outlook on Access: Using Outlook 97 from Access 97 (and Vice Versa)

By Don Kiely

For years, Microsoft Office has been an alluring collection of functionality begging to be included in stand-alone custom applications. It had the potential to eliminate the need to create custom text editors and word processors, spreadsheets to display or crunch numerical data, or personal information managers. Two obstacles, however, always stood in the way of such a use: 1) Anyone using your custom application needed all relevant parts of Office installed on each machine, potentially an expensive requirement that eliminated the possibility of wide distribution; and 2) OLE, the glue that held everything together, was painfully slow. Even Office 95, though it certainly improved the situation, was sluggish and had wildly inconsistent object models and programming tools. (A single top-level object for Word? Puleeze!)

Office 97 represents a comprehensive makeover of Microsoft’s flagship suite, for both the user interfaces and the development tools. Office is now the complete development environment Microsoft has promised for years. All the major applications — except Outlook — use Visual Basic, Application Edition (VBA), a rich programming language capable of almost anything. Because it’s used throughout Office, forms the basis of the stand-alone Visual Basic 5.0 product, and — most importantly — is licensed to third parties, the VBA skills you acquire are applicable to some 75 major software applications. C++ programmers may look down their noses at Basic, but the range of business applications you can develop with VBA, and the objects exposed through OLE, are truly amazing.

The focus of this article is on using Access and Outlook together, particularly on obtaining/exchanging each other’s data. It will cover some of the main differences you’ll encounter developing applications with the two products, and how they differ from the other Office 97 family members. A detailed discussion of every data-access technique is beyond the scope of any single article, but this article should provide enough information to decide which data access technique is best, and where to go for more information about implementing it in your applications.

Using Access and Outlook Together

Why would you want to develop applications using Outlook and Access? As covered in detail later in this article, Outlook uses VBScript as its native programming language, a seriously restricted subset of the full VBA 5.0 environment. This has the odd effect of making many “Outlook” programming tasks far easier to perform from outside Outlook. In marked contrast to Access, Outlook also has limited data processing and analysis capabilities. So if, for example, you need to analyze Journal entries, automate processing of groups of contacts, or process information from several users’ Outlook data stores, it’s far easier to use Access to get to and process the data. Using Access remotely from Outlook can be useful as well, e.g. a custom Outlook form that requires enterprise-level data.

To get your creative juices flowing, here’s a list of applications you might develop using Outlook and Access:

With Outlook Journal entries that log phone calls, Access can be used to analyze the information and compare it with your phone bill as a cost control measure.

Create a catalog of all or part of the public folders in Access for easier searching, sorting, and retrieval of information.

Link a database of real estate listings to an Outlook data store, allowing agents to use Outlook with their laptops to reference property information while on the road.

From a central calendar database, update users’ Outlook calendars with important events, based on a user’s department or job function.

Publish information to an intranet or Internet Web site. Access has several ways to publish database objects to a Web page, so it’s just a matter of getting the data to Access.

Create Outlook forms that automatically update certain information from a central Access database, such as part numbers for ordering information or general ledger numbers for expenses.

Email-enable any kind of application that requires transmitting data or database reports to individuals inside and outside the company.

For more ideas, check out the Outlook sites at http://www.microsoft.com, particularly those listed in the “Resources” sidebar on page XX.

Programming Outlook and Access from Each Other

One of the grand promises of Office 97 was that all the applications would have consistent user interfaces, the same embedded programming language and tools, and object hierarchies structured similarly so that understanding one would help you understand the others. The Outlook development team seems to have gone out of its way to be just different enough from the other Office applications in all three of these areas to make programming Outlook an adventure. I like the user interface, but the Outlook Bar and Folder List are unlike anything else in Office. The menu structures are different enough to make things hard to find, e.g. in Outlook, Find is on the Tools menu rather than the Edit menu as with the other family members.

The biggest disappointment, of course, is that Outlook uses VBScript instead of VBA. VBScript is a lightweight edition of Basic designed for use on Web pages, where security and download speeds are critical factors in the success of a Web site. If you’ve done any VBA programming, however, you’ll quickly become frustrated by VBScript’s limitations. It uses a relatively small subset of the VBA language, leaving out such necessities as: conditional compilation (e.g. #Const and #If...Then...Else), the DoEvents function, the Format function, Debug.Print, and plenty more.

The list goes on. With no built-in debugger, you’re reduced to stone age debugging techniques, such as commenting out lines until you find the line causing the problem, and littering your code with message boxes to find the states of variables. The Outlook Script editor is nothing more than a dedicated text editor — no syntax checking and no color-highlighting. And forget about hip new VBA features like IntelliSense, Complete Word, and Quick Info to help you type code. It doesn’t even have search capability! Set your watch back 15 to 20 years when you program in Outlook. You don’t have access to an object browser, so you have to load up another Office application to get information about the objects with which you’re working. And the only data type available is Variant — the big, slow type that Microsoft has told us for years to use only as a last resort.

Working with objects in Outlook is another adventure. Because you can declare only Variant variables, all objects must be late bound, slowing things considerably. In VBA, for example, you can declare an object variable to access an Excel worksheet:

Dim xlSheet As Excel.Worksheet

This enables the compiler to gather all the information it needs about the object at compile time, called early binding, instead of at run time, speeding execution of your application. In Outlook, you must do it this way:

Dim objXL
Set objXL = Item.Application.CreateObject(_
              "Excel.Application")

(Note: Although it isn’t supported by Outlook, we’re using color syntax highlighting in this article for clarity.) Because Outlook isn’t able to gather information about the object ahead of time, it does so when you run the code, called late binding. There are times when late-binding is necessary and even good, but VBScript gives you no option.

Another hassle is the theoretically simple task of setting properties of a control on an Outlook form. In VBA, setting the Text property of a TextBox control is a simple statement:

txtStatus.Text = "Hey! Processing is done!"

In Outlook, however, it’s a convoluted process. You can’t refer directly to the control, but must first associate an Inspector object (more about this later) with it to refer to the current page on the form, set an object reference to the control you want, then set the appropriate property or call a method using that object variable:

Set MyPropChg = GetInspector.ModifiedFormPages
Set MyCtrl = MyPropChg("PhoneLog").Controls("txtStatus")
MyCtrl.Text = "Hey! Processing is done!"

Intuitive, eh? And because there’s no DoEvents function (or its equivalent) in VBScript, I haven’t found any way to update a form during processing, e.g. to display a status message such as “Reading Journal item 5 of 963.” The form doesn’t redraw, so all you get is “Reading Journal item 963 of 963” once processing is finished. (If anyone has found a way around this, please let me know.)

So why did the Outlook team decide to use VBScript instead of VBA? At this year’s TechEd in Orlando (highly recommended if you’re working on the cutting edge of Windows programming), I asked Darrique Barton that question. Darrique is one of the Outlook program managers and presented several excellent Outlook programming sessions. He said they wanted to use a lightweight language, presumably so that future versions of Outlook can publish applications across the Internet. (He also said that if anyone wants to try to convince the team to use VBA, send good reasons to http://www.microsoft.com/office/feedback/.)

Programming Outlook from Access

The degree to which Outlook is inconsistent with the other Office applications is surprising; especially since the Outlook team wasn’t burdened with backward compatibility. It’s loosely based on the old Schedule+ and Exchange client, but resembles neither.

The Access team, however, was working with a long-established product for the Access 97 version, with a mandate not to break too much existing code or databases. While Access uses the full VBA package as the other Office 97 applications, you must use and learn a number of development techniques different from Word, Excel, and PowerPoint. For example, the VBA IDE in Access is an MDI child window within Access, whereas in the other applications it’s a window separate from the main application. There also isn’t a separate Project Explorer window listing all the existing code modules.

Unlike Outlook, Access nominally uses the Microsoft Forms package, but programming forms in Access is quite different than in the other Office 97 members. There are many other differences; Microsoft has made great progress in unifying Office 97, but Outlook and Access are still the “odd ducks” when it comes to application development.

Nevertheless, compared to programming Outlook objects within Outlook using VBScript, manipulating Outlook’s objects in Access VBA is a sheer joy, and (almost) simple. First and foremost, you have the full VBA language, design environment, debugging tools, and all the other wonderful features that Microsoft has spent years adding to Visual Basic. So you’ve eliminated all the frustrating limitations mentioned earlier regarding Outlook programming.

Outlook’s objects are exposed to the outside world through a type library, providing access not only to the objects themselves, but to all the type constants. This eliminates the need to remember that an olAppointmentItem has a value of 1 in VBScript; you can simply use the olAppointmentItem constant.

To use Outlook objects within Access, you must set a reference to the type library, which is copied to your computer when you install Outlook. From any code module window, select Tools | References, and check the box for the Microsoft Outlook 8.0 Object Library. If it doesn’t appear in the list, but you’re sure that Outlook is installed on the computer, click the Browse button and select the Msoutl8.olb file, located in c:\Program Files\Microsoft Office\Office by default. Now your VBA programs have full access to all Outlook objects.

After you have this reference to the Outlook object model set, the first big difference in coding is getting a reference to Outlook’s Application object. Because VBA provides full use of typed variables, you can declare variables in your Access code of the specific types Outlook objects you want to work with. Then, instead of having immediate access to the Application object, you can use either CreateObject or GetObject to return a reference to Outlook:

Dim olApp As Outlook.Application
Dim olNewMail As MailItem

Set olApp = CreateObject("Outlook.Application")

After you have this reference, working with Outlook objects is pretty much the same as within Outlook, except you have all of the features of VBA to make your coding easier and more robust.

For example, in Outlook, this VBScript creates a new appointment to meet with clients:

Dim olAppt

' olAppointmentItem = 1
Set olAppt = Application.CreateItem(1) 

olAppt.AllDayEvent = True
olAppt.Subject = "Meet with clients"
olAppt.Start = DateValue("8/15/97")
olAppt.End = DateValue("8/16/97")
olAppt.Save

Here’s the equivalent code in Access, using VBA:

Dim olApp As Outlook.Application
Dim olNewAppointment As AppointmentItem

Set olApp = CreateObject("Outlook.Application")
Set olNewAppointment = olApp.CreateItem(olAppointmentItem)

With olNewAppointment
  .AllDayEvent = True
  .Subject = "Meet with clients"
  .Start = DateValue("8/15/97")
  .End = DateValue("8/16/97")
  .Save
End With

The Access code is a couple of lines longer than the Outlook code because you have to get or create the Outlook object variable and dimension an extra variable, but the code runs faster because of early binding and the use of the With structure. Furthermore, you can step through and debug each line, set watch and breakpoints, and so on. In examples with more code, the code will be relatively shorter than its Outlook equivalent, and far more efficient.

Not surprisingly, I find I do all the programming I can in applications outside of Outlook to take advantage of all the VBA features. The only time that I code in Outlook is when there is some compelling reason, e.g. creating an enterprise-wide form that will be used within Outlook. When I want to automate Outlook, however, any VBA-enabled application is far easier and quicker to program than Outlook.

Exchanging Data between Outlook and Access

Access and Outlook, by their natures, are focused on storing and manipulating data. While you can create applications in each that perform some action unrelated to data, you’re probably using the wrong tools for the job. And getting at each other’s data is not always the easiest, particularly since the native data formats are radically different.

Outlook’s MAPI Information Store is a hierarchical data storage structure model, based on OLE structured storage, that doesn’t readily lend itself to relational database design. FIGURE 1 shows how this works. At the topmost level, there can be several folders containing unrelated information. The figure shows my Personal Folders where I keep everything I don’t want anyone else to have access to. Below this level, there are many folders, again containing mostly unrelated information, some of which then have layers of subfolders. An Outlook item can reside in only one folder, and while it’s possible that an item in one folder is “aware” of another item in a different folder, this isn’t a built-in feature of the data structure. So, for example, you can have an Appointment or Task linked to a Contact by dragging and dropping items between folders. You can copy items to place them in multiple folders, but then you have multiple items, each of which is unique and independent.

This hierarchical structure means that you can’t model one-to-many relationships in a MAPI data store, using the data stored in the structure itself. You can, however, add a relational structure to your application by accessing external data and linking that data to your MAPI data store in a relational manner. So you might create a table in Access that has a record linked to each Contact in the MAPI data store, containing information not stored in Outlook about each Contact. You could also create an Access table that relates the data in two Outlook folders in some way.

Access, however, uses a relational database structure, storing data in multiple tables linked together in some way that mirrors the relationships between data. FIGURE 2 shows how multiple tables are related, modeling the relationships that the real, physical objects have outside the database. So a supplier produces one or more products, each of which is in a single product category. One of the big advantages of this scheme is that a particular piece of information is stored in a database only once if the database is properly normalized.

So the problem becomes how do you get at relational Access data from Outlook, which expects a hierarchical structure, and get at hierarchical Outlook data using the relational methods native to Access? The answer, fortunately, is that there are several ways to do it. The problem, in fact, might be deciding on the method that works best for your application.

FIGURE 1: Outlook currently supports only a MAPI data store, a hierarchical structure not easily adapted to relational databases.

FIGURE 2: Microsoft Access uses a relational database structure to store data in different tables. This structure is from the Northwind .MDB that ships with Access and Office.

Getting at Outlook Data from “the Outside”

Given the limitations of its programming environment, you’ll probably want to access Outlook data from other applications more than the other way around, so we’ll tackle that case first. Right now there are three main ways of getting at the MAPI data store: Using OLE automation and the Outlook object model, an ISAM (indexed sequential access method) driver freely available from Microsoft, or creating your own ActiveX control.

FIGURE 3: The structure of the Outlook object hierarchy belies its complexity.

Outlook Objects Exposed

Like all Office applications, the key to working with Outlook is to first understand its object hierarchy (see FIGURE 3). On the surface, this is a standard object model with an Application object at the top that gives you access to all the other Outlook objects. Application is the only object in the hierarchy that can be returned by using the CreateObject or GetObject functions from applications outside of Outlook. Interestingly, it gives direct access to any items you create using the CreateItem method, such as appointment, journal, or mail items.

The Explorer, Inspector, and Assistant objects, directly below the Application object, provide access to the active interface items. Explorer is the window in which the contents of a folder are displayed, e.g. the list of email messages, or a calendar view. It gives you control over this window, letting you change the folder being viewed, access the command bars associated with a window, and close the window.

An Inspector object is similar to an Explorer object, but at a lower level. It’s the window in which an Outlook item is displayed, such as the contents of an email message. Use this object to get access to the item for manipulation, the Pages collection used to display the item, and change how the item is displayed. These names take a bit of getting used to, but they describe the objects well.

The Assistant is the shared Office Assistant object, a fully programmable object, e.g. “Clippit” the paper clip and “Earl” the cat. Incidentally, if you haven’t downloaded Earl yet (it isn’t on the Office CD-ROM), go to Microsoft’s Web site and grab it. It just might change your mind about the Assistant (see the “Resources” sidebar for the specific location).

The NameSpace object provides access to all the items stored in Outlook in the various folders. You’ll use this object to create, catalog, and manipulate mail messages, journal entries, contacts, and every other Outlook item. You can also use NameSpace to create and delete folders. Microsoft describes this as an “abstract root object for any data source,” used for logging in and out of Outlook and selecting a profile, gaining access to the default folders for each Outlook item type, and accessing other users’ data stores. You’re likely to spend much of your programming effort working with the NameSpace object.

The only type of NameSpace data store currently supported is MAPI, the Mail API that underlies Outlook, Exchange, and all 32-bit Windows 95 and Windows NT messaging. You use the statement:

GetNameSpace("MAPI")

method to open the MAPI data store. Clearly, Microsoft has implemented this method with the flexibility to allow other data stores in the future. Once you have a NameSpace object reference, you have fairly complete access to all the data stored in Outlook. For example, the code in FIGURE 4 shows the use of the Application and NameSpace objects to open the default Journal items folder. Once the olJournal object variable is set to the folder, the myItems object variable is set to the collection of objects, which can be filtered, sorted, and processed like any VBA collection.

One twist to the Outlook object hierarchy is that there is a kind of sub-hierarchy of objects lurking below the Item object in the NameSpace portion of the model. An Outlook item can be one of several different classes, representing the different formats used to hold information in the MAPI data store (see FIGURE 5). In most cases, you can explicitly create each item type, shown by the “You” in the Creation column of the table. In four cases, however, Outlook itself creates an item in response to some action that your program takes. For example, you can’t explicitly create a MeetingRequestItem, but Outlook automatically creates this item when you set the MeetingStatus property of an AppointmentItem object to olMeeting and send it to one or more users. This can be a new or existing AppointmentItem.

Creating a new item is relatively straightforward, once you have a reference to the Application object. Refer again to the VBA and VBScript code used to create a new AppointmentItem using an existing Application object variable, then sets various properties for the calendar entry.

Set olMAPI = Application.GetNameSpace("MAPI")

' Open the default Journal Folder 
' (olJournalItems = 11)
Set olJournal = olMAPI.GetDefaultFolder(11) 

' Create an object reference to the Journal items.
Set myItems = olJournal.Items

' Filter the items and sort by the Start date.
Set myRestrictItems = myItems.Restrict(sFilter)
myRestrictItems.Sort "[Start]", False

' Add the Start date, Duration, and Subject to a String
‘ Variant to be used to display the information with
‘ tab delimeters.
For Each myItem In myRestrictItems
  sJList = sJList & myItem.Start & Chr(9) _
    & myItem.Duration & Chr(9) _
    & myItem.Subject & Chr(13) & Chr(10)
Next

FIGURE 4: Code used to access a filtered list of Outlook Journal items, showing how the NameSpace object provides access to stored information.

Outlook/Exchange ISAM Driver

The Outlook/Exchange ISAM driver is a relatively easy way to get at Outlook data — if you can live with its limitations (discussed later). The driver is available as part of the ValuePak on the Office 97 CD-ROM in Dataacc.exe, but don’t use that version. It isn’t self-registering and has some other limitations that you don’t need to tolerate. Microsoft has a newer version available on its Web site (again, see the “Resources” sidebar). Grab that one, which is part of the Microsoft Outlook/Exchange Wizard that gives you some other goodies for data access. Running the installation program, WzMAPI80.EXE, installs both the driver and the wizards. Just be sure that Access is installed first, because the setup program has to be able to find the Microsoft Jet files.

The ISAM driver has several limitations that might limit its use in your application:

You can’t select which fields are available to you in opened and linked folders.

Only a subset of the total number of Microsoft Exchange Client or Outlook fields is available; they are preprogrammed in the ISAM driver.

You can’t use it to edit existing records. You can add and delete records (items), but still don’t have access to all fields.

You cannot index the records in a linked table.

No user-defined fields for a custom Outlook form or folder are available through the ISAM.

To see the structures of the tables available using the ISAM driver, I created an Access database with links to all the standard Outlook folders, then used Access’s documenting tool to list the tables, fields, and their properties (Tools | Analyze | Documenter). The result is a Word 97 file named AccessOutlookData.doc, and is available for download; see the end of this article for details.

If you can live with these limitations, the driver lets you get at data stored remotely on a Microsoft Exchange server, or locally in offline folder (.OST), personal folder (.PST), or personal address book (.PAB) files. (How you’re logged onto Outlook or Exchange determines what local or remote data you access.)

Using the driver is the same as accessing any other ISAM database through Access, letting you either link or import the Outlook data into an Access database. The table in FIGURE 6 lists the information for the connection string you use with the OpenDatabase method.

To get access to messages in an Outlook folder, you have to specify the path to the folder just above the folder you want to use, then use the SourceTableName property in DAO (Data Access Objects) to select the actual folder. This is analogous to using the connection string to select a database file, then using SourceTableName to selecting a table in that database. For example, to gain access to my Tasks folder under the Northern Studies folder shown in FIGURE 1, you’d use the following connection string:

"Exchange 4.0;MAPILEVEL=Personal Folders|Northern Studies;"

Then use the SourceTableName property in your DAO code to specify the Tasks folder:

tdf.SourceTableName = "Tasks"

The code listing in FIGURE 7 shows one way to use the ISAM driver to connect and use the information in Outlook. It goes through the usual procedure of creating a reference to a workspace, then sets up the connect string for the Personal Folders section of the Outlook file:

sConnect = "Exchange 4.0;MAPILEVEL=Personal Folders|"

Note that the trailing | character is necessary even if you aren’t specifying a folder under the top level. Leaving it out will give you a misleading error in the OpenDatabase method, saying that the path you specified for the database is invalid. The code goes on to look at each folder, treating it as a regular Access table, reading the first three records in each table, and printing information to the Debug window. One other thing to notice is that when the code runs, only the folders on the level immediately under the top-level Personal Folders are open and read; any lower folders are ignored. If you want to get at a lower-level folder, you have to change the sConnect string accordingly.

Besides opening a non-persistent connection to the database as shown in FIGURE 7, you can also create a persistent link to the Outlook tables, so that each linked table is available each time the Access database is opened, or import the data so that a copy of it resides in Access. Again, all you need is to properly build the connection string, then use the standard methods for linking or importing data into Access. See the Access documentation under ISAM and the CreateTableDef method for more information about these techniques. The Exchange and Outlook Wizard, included with the latest version of the ISAM driver, steps you through linking and importing Outlook data using the Access user interface.

FIGURE 5: A kind of object sub-hierarchy layer underlies Outlook’s Item object. This table lists the various Item classes and whether you can create the item in code.

FIGURE 6: Connect string information for the Outlook ISAM driver. The items in bold are the keywords to use for each parameter.

Sub OpenOutlook()

Dim ws As Workspace
Dim db As Database
Dim rs As Recordset
Dim tbl As TableDef
Dim sConnect As String
Dim i As Integer

  Set ws = CreateWorkspace("ws", "admin", "", dbUseJet)
  sConnect = "Exchange 4.0;MAPILEVEL=Personal Folders|"
  Set db = ws.OpenDatabase("C:\Stpats.mdb", False, _
                           False, sConnect)

  ' Open each top-level folder 
  ‘ and print first three records.
  On Error GoTo OpenOutlookError

  For Each tbl In db.TableDefs
    Set rs = db.OpenRecordset(tbl.Name, dbOpenDynaset, _
                              0, dbOptimistic)
    i = 1
    Debug.Print "Records in Folder: " & tbl.Name
    Debug.Print "----------------------------------"

    While Not rs.EOF And (i < 4)
      Debug.Print i, rs!Subject
      i = i + 1
      rs.MoveNext
    Wend

    rs.Close

OpenOutlookError:
    If (Err) Then
      Debug.Print "*****" & tbl.Name, Err, Err.Description
      Err.Clear
    End If
  Next tbl

  Debug.Print "Done"
  db.Close
  ws.Close

End Sub

FIGURE 7: Making a connection to an Outlook data store using the ISAM driver is just like any other external Access database. This code is adapted from the documentation for the driver.

Custom Controls for Outlook

Another way to get at Outlook data is by building a custom control using Visual Basic 5.0, Visual C++, Delphi, or other development tool. This technique is outside the scope of this article and requires knowledge of creating controls for data access. If you’re inclined to pursue this method, it involves uses a combination of the Outlook Object Library discussed in this article, the ActiveX Messaging Library 1.1 (available with Microsoft Exchange Server 5.0), and either DAO 3.5, RDO (Remote Data Objects) 2.0, or ADO (Active Data Objects) 1.0. Creating your own control requires more work, but gives you the most flexible access to Outlook data.

Using Access Data in Outlook

If, despite all the obstacles to developing robust applications, you still want to write code in Outlook that accesses data outside its own data store, read on. There are essentially two ways to do this at the current stage of Outlook development: using the CreateObject method or using an ActiveX control on an Outlook form.

Using VBScript, the Item.Application.CreateObject method provides access to ODBC (Open Database Connectivity), ActiveX Data Objects (ADO), or an ActiveX DLL that links to data. This method essentially involves creating a custom OLE object with CreateObject, using VBScript to manage the interface between the object and Outlook, and letting the OLE object access the data.

With the ODBC option, you can use ODBCDirect, which is part of DAO 3.5, or Remote Data Objects (RDO). However, since the RDO object model is more complex than ODBCDirect, it is harder to use with the primitive tools available in VBScript. However, it is there if you need the flexibility.

The best way to learn how to use ODBCDirect and other ways to access data using the Outlook CreateObject method is to download and work with the Northwind Order application (see the “Resources” sidebar for details). The application contains a lot of code you can extract for your own use, particularly the GetODBCConnection function.

A connection to DAO is used for access to ODBC, using a statement such as:

Set dbe = Item.Application.CreateObject("DAO.dbEngine.35")

Using the resulting Variant object variable dbe, you can then create an ODBCDirect Workspace, append it to the dbe object, and connect to a specific ODBC data source name:

Set wrkODBC = dbe.CreateWorkspace("ODBCWorkspace", _
                strUser, strPass, dbUseODBC)

dbe.Workspaces.Append wrkODBC

' Establish the connection to DSN
Set conDB = wrkODBC.OpenConnection("Connection1", _
              MyPrompt, , MyConn)

I’ve taken these statements out of the context of all the necessary error handling that makes sure you successfully achieve a valid connection to the database. As you can imagine, this kind of code is a bear to debug in Outlook. Don't try this at home, and certainly not at 2 A.M. the morning the new application is due!

ADO is the object model of the future, according to Microsoft, that will eventually supersede all DB-Library, DAO, and RDO programming. One reason ADO is a good method of data access with VBScript (since it was designed for Internet use), is that it requires fewer calls to do the same things as RDO. Since all VBScript objects are late-bound, performance takes a big hit; however, since ADO requires relatively fewer calls, the performance hit isn't as great.

Using ADO requires that you create an ActiveX control or DLL to encapsulate the features you need for your application. With VB 5.0, this is far easier than it used to be. There are a number of benefits to this approach, although it is anything but simple. You can tailor the data access features you include in the component to a specific business application. You have far more control over security by using a component — even to the point of embedding all login passwords in the component itself. Error trapping within Visual Basic 5.0 is superior and performance is better due to early binding. And if you want to package your components for wide distribution, you can use Microsoft Transaction Server and DCOM to distribute them securely over a network.

If you can wait a bit, Microsoft is working on an Outlook Data Control you can place on an Outlook form and access data from almost anywhere. It should be available by the time you read this. TechEd attendees got a sneak preview. (Yet another reason to attend TechEd!) The control has an intriguing design, with the property page shown in FIGURE 8. It can take on the form of the combo box (shown in the figure), or an edit or list box. It has a surprising amount of flexibility at design time, including a mini-SQL statement builder on the Criteria page.

FIGURE 8: The upcoming Microsoft Outlook Data Control, being developed by Kwery Corp., can be used on an Outlook form to access almost any data.

Getting Help

You have to manually install the Outlook 97 VBA help file. It’s in the \ValuPack\Morehelp folder on the Office 97 CD-ROM. Copy Vbaoutl.hlp and Vbaoutl.cnt files to c:\Program Files\Microsoft Office\Office (or wherever your Office help files are installed). To access the help file, open the Outlook Script Editor while an Outlook item is open in design mode. Then select Microsoft Outlook Object Library Help on the Help item in the Script Editor's main menu. You can also open it in Windows Explorer.

Hopefully this article has given you some ideas of how to use Access and Outlook in concert, letting you approach the task with eyes open wide to the pitfalls, but also giving you an overview of the tools available to work with each application’s data. If you come up with a unique and innovative application, contact either the editors at Microsoft Office & VBA Developer about doing an article to share your ideas. If you're hesitant about writing, contact me; perhaps we can collaborate.

Resources

Microsoft Office 97 Developer Edition. If you’re going to develop and distribute Office applications, this is the version to have. You get printed manuals, maps of all the object models in all Office and its support applications (most are even correct), tools for packaging and distributing your applications, and much more. Well worth the few extra dollars above the price of Office Professional.

Mastering Office 97 Development, Microsoft Corp. This is a CD-ROM-based tutorial for learning how to develop applications with Office 97. It isn't comprehensive, but is an excellent introduction to the new tools. Includes lots of sample code, a subset of the Microsoft Knowlegebase, and technical papers.

Web sites. Microsoft has a wealth of information on its Web site. In particular, check out http://www.microsoft.com/outlookdev/, http://www.microsoft.com/outlook/, http://www.microsoft.com/accessdev/, http://www.microsoft.com/access/, and http://www.microsoft.com/officefreestuff/outlook/sampform/default.htm.

Accessing Microsoft Exchange and Outlook Data Using Visual Basic at http://www.microsoft.com/AccessDev/AccWhite/ExcOutlk.htm.

Microsoft Outlook/Exchange Wizard (which includes the ISAM driver) at http://www.microsoft.com/AccessDev/AccWhite/ExchWiz.htm.

Using ODBCDirect in Outlook 97: The Northwind Order Application at http://www.microsoft.com/OutlookDev/TechInfo/odbcoutl.htm.

The Outlook FreeStuff Web site has plenty of sample applications using Outlook, at http://www.microsoft.com/OfficeFreeStuff/Outlook/Default.htm. The equivalent site for all Office applications is at http://www.microsoft.com/OfficeFreeStuff/. (This is also where you can grab the Office Assistant, Earl the Cat.)

Building Microsoft Outlook 97 Applications by Peter Krebs, Microsoft Press, ISBN 1572315369. Required reading for serious Outlook development.

Access 97 Developer's Handbook by Paul Litwin, Ken Getz, and Mike Gilbert, Sybex Inc., ISBN 0-7821-1941-7. The latest edition of the bible of Access programming. If you ever have the opportunity to hear any of these authors speak, take it; you'll learn a lot.

— Don Kiely

The file referenced in this article is available for download from the Informant Web site at http://www.informant.com/mod/modnewupl.htm. File name: MOD9708DK.ZIP.

Don Kiely is a Principal of and the Director of Technology for SkyFire Group, a company that programs Windows and Web applications for businesses in rural Alaska. He's written and co-authored several books about Windows programming, including Visual Basic 5 Client/Server How-To, from Waite Group Press, and The Ultimate VB Controls Sourcebook, from Coriolis Group Books. You can reach him at d.kiely@ieee.org.