Connecting Outlook to Databases

A common question is, Can I connect Outlook with my existing customer database? Definitely yes, using the resources on this page. Two basic approaches are available:

• Expose database information in Outlook either directly or by synchronization
• Write your own code to make the database connection and create, modify, and delete records. ADO, a programming library included with Office to facilitate database connections, is most commonly used in this scenario.

Replication API

For Outlook 2003 and later, Microsoft provides a programming interface to synchronize an external data source with an Outlook .pst file dedicated to storing data from that source. Outlook's ability to expose data from SharePoint lists is an example of this use of a proxy .pst file. To make this technique work, you need a "wrapped" .pst file. For more information, see:

• Outlook 2007
• About the Sample Wrapped Store Provider (Outlook 2007)
• Outlook 2003
• Outlook 2003 Integration API Wrapped PST Docs and Sample

LINQ Queries

If your main interest in treating Outlook as a database is to be able to run queries against its data, the capabilities of the new LINQ query feature for .NET 3.5 (Visual Studio 2008) might be just what you're looking for. In the article Querying Outlook and OneNote with LINQ, Stefan Cruysberghs shows how to use LINQ to query Outlook messages, appointments, tasks, contacts, and notes with several very practical examples, including running a single query to get the count of messages sent for each day during the past week.

ADO

A flexible method for using Outlook with a database is ActiveX Data Objects (ADO), the programming interface for OLE DB database technology, which allows you to connect to virtually any kind of data source. ADO 2.0 is included with Office 2000 and downloadable for earlier versions of Outlook. Later versions of Office come with later versions of ADO.

In .NET applications, you'd use its equivalent -- ADO.NET.

If you want to synchronize data between Outlook items and database records, you might link the Outlook item and the database record through a custom ID field on the Outlook item that has a counterpart in the database, both holding the same values. See Developer information about the calendar changes in Outlook 2003 Service Pack 2 and later versions about changes related to the EntryID in appointments that may make it a poor choice for this kind of linking.

My book, Microsoft Outlook Programming - Jumpstart for Administrators, Developers, and Power Users, includes practical examples for connecting to Access and SQL Server databases with both DSN and DSN-less connections, populating a combo box from a database table, and using a database to generate a sequential unique ID. You can download the sample code from the get Sue's code link below.

Also see:

• Chapter 14 - Working with the Data Access Components of an Office Solution (Microsoft Office 2000/Visual Basic Programmer's Guide)
• HOWTO Open ADO Connection and Recordset Objects
• How Do I Use the Connection Object in ADO?
• ADO Connection Strings
• Connection Strings
• Easy method for building an ADO connection string (outlook-dev list)
• ACC2000 How to Retrieve Exchange and Outlook Data with OLEDB -- has the same major limitations as the Linked Tables method

DAO

DAO = Data Access Objects, an older database connection technology that supports ODBC (Open Database Connectivity) drivers and works with Office 97 and later versions. Many of Microsoft's sample forms and code examples demonstrating Outlook+Access connectivity use DAO.

Web Service (Outlook 2003 & 2007)

In Outlook 2003 and 2007, contact and event lists from a Windows SharePoint Services site are copied a background process to a proxy .pst file and exposed as read-only folders. The underlying WSS feature that supports this functionality is a web service. Therefore, you can use a similar web service to expose any kind of data in Outlook. As long as it uses the same contract as the WSS web service, Outlook will handle the data just like the native WSS contact and event data. Examples are available for both contacts and calendar from Microsoft's Stephen Toub, who pioneered this technique:

• Custom Calendar Providers for Outlook 2003
• Custom Calendar Providers for Outlook 2007
• Windows XP Media Center 2004 recording schedules viewed as calendars in Outlook
• OutlookContactProvider
• Two-Way STSSync Protocol Server for Outlook 2007 - generic solution from Patrick Creehan to make it easy to build data providers that take advantage of the synchronization built into Outlook

Linked Tables

Without programming, you can add a table linked to an Outlook or Exchange folder to any Access database, using drivers built into Office 2000 (or later), by following these steps:

1. In Access, click the New Table button to add a new table.
2. In the New Table dialog, choose Link Table, then click OK.
3. In the Link dialog, under Files of type, choose Outlook() or Exchange().
4. If you're prompted for an Outlook profile, indicate which one you want to use
5. In the Link Exchange/Outlook Wizard, choose the address book or folder you want to link to, then click Next.
6. Give the linked table a name, then click Finish.

See the MSKB article OL2000 - How to Use Your Outlook Items with Other Office Programs for more information.

Unfortunately, this method has at least three huge limitations and, therefore, is not recommended: 

• The linked table ignores any custom fields. 
• Not all address fields are linked. The Home and Office addresses each are listed as one field, while the Business address -- probably whatever is the default mailing address -- is parsed into its street, city, etc. components.
• Not all fields are included for all items -- not even all essential fields. You can see the fields available by switching to Design View.

Michael Kaplan wrote articles in the August and September 2000 issues of the Smart Access newsletter that explain how to get around the above limitations and make this method work much better. Unfortunately, we have not been able to duplicate his results. The August article is available on MSDN -- The Jet 4.0 Exchange/Outlook IISAM.

Samples

VSTO - Importing addresses from Database to Outlook - add-in from Naresh Bojja showing how to import from and synchronize with a database

Synchronizing a Local Data Store with Microsoft Outlook -- complete sample and documentation for synchronizing a SQL Express database with an Outlook data store using managed code in a .NET VSTO 2005 add-in

Company-Contact Selector sample form (ADO)

Using Outlook or Exchange with a database (DAO) -- Uses linked tables

AutoNumber Outlook Contacts (DAO) -- Generate a unique ID from an ODBC data source

School Calendar Form -- demonstrates how to populate drop-down lists on an Outlook form from Microsoft Access data. To install it:

1. Chose File | Open | Outlook Data File, point to the folder where you installed the sample, and select the sample's .pst file.
2. In the root folder for the sample's .pst file, open the one item you find there.
3. Double-click the installer .oft form attached to that item. If you get a Select Folder prompt, you can select any folder; it doesn't matter which.
4. Switch to the Files page of the installer form.
5. Save the .oft form attachment listed on the Files page to a folder on your local hard drive.
6. Choose Tools | Forms | Design a Form and use the User Forms in File System choice to browse to the folder from Step 5
7. Select the form to open it. You can then review its code and publish it.

PubCal -- Web-based display of public folder calendar items with a SQL database in the middle.

OL2002 How to Programmatically Import Outlook Items from Microsoft Access (DAO)

OL2002 How to Programmatically Export Outlook Items to Microsoft Access (DAO)

Synchronizing a Local Data Store with Microsoft Outlook -- using Visual Studio 2005 Tools for Office

Sample Outlook forms with accompanying Exchange event script samples that use ADO database connections; see School Calendar Form above for installation instructions: 

• Lunch Count
• Student Attendance

For additional samples, see:

• Import and Exporting Data from Custom Microsoft Outlook Forms