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.
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:
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.
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.
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:
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:
- In Access, click the New Table button to add a new table.
- In the New Table dialog, choose Link Table, then click OK.
- In the Link dialog, under Files of type, choose Outlook() or Exchange().
- If you're prompted for an Outlook profile, indicate which one you want to use
- In the Link Exchange/Outlook Wizard, choose the address book or folder you want to link to, then click Next.
- 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.
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:
- Chose File | Open | Outlook Data File, point to the folder where you installed the sample, and select the sample's .pst file.
- In the root folder for the sample's .pst file, open the one item you find there.
- 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.
- Switch to the Files page of the installer form.
- Save the .oft form attachment listed on the Files page to a folder on your local hard drive.
- Choose Tools | Forms | Design a Form and use the User Forms in File System choice to browse to the folder from Step 5
- 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:
For additional samples, see:
Synchronization & Sharing Tools
Perform data transfer and synchronization between your Outlook information and any Access/SQL/ODBC database. Supports custom fields and understands collections like ContactItem.Links and MailItem.Recipients.
Synchronizes any ODBC data source with data in a public or personal folder in Outlook or Exchange, including Outlook Contacts. Many other data integration features, including Notes <-> Exchange synchronization. (Formerly Replic-Action)
Replicates contacts from a database to Exchange Server public folder or Global Address List on a schedule. Creates Exchange distribution lists based on database queries. Supports field mapping to custom Outlook contact forms.
Component to synchronize data in FileMaker Pro 5.5 (or later) databases with Outlook data. Includes support for attachments, which are copied to a folder accessible to all FileMaker users.
Windows service that copies Global Address List information to a database, using extended MAPI, not ADSI or CDO. GALtoDB supports Microsoft Access, Oracle, Microsoft SQL, and most other database systems and can also export contacts to Public and Personal Folders. The GALtoDB Configuration wizard allows you to setup database connections, choose exchange fields, and schedule database imports and public folder exports.
database connectivity between Outlook/Exchange data and contact, calendar and customer information stored in your database for updating in either direction. Tasks can also be created in Outlook based on events in your database. Supports connectivity to any standard database through ODBC.
Tool for two-way data transfer between Outlook and Microsoft Access (or through Access to any ODBC data source), including custom fields. Handling of contact links and appointment recurrences. Automatically creates Access tables based on Outlook fields. Updates can be run on a scheduled for automatic synchronization.
Automatically synchronizes Outlook data with Access, SQL Server or any other SQL database. Includes two other applications -- RBSchedule for viewing multiple users' schedules in a single window and Outlook2WordPro for mail merge. In German.
Provides the relational database capability that Outlook lacks by storing Outlook contacts, tasks, appointments, and messages in a SQL database. Users can share the information, working with SQL tables as if they were Outlook folders. Supports dynamic distribution lists, hierarchical classification schemes, folder-based custom forms. Free single-user version. (Formerly TabTag)
(formerly TimeCards for Outlook 2000) Contact management tool, streamlining repetitive entry tasks and supporting shared folders via Exchange Server public folders. Automatically enters contact data into other Outlook items. Synchronizes Outlook Journal items with an Access database. Can create multiple tasks to go with each new contact.
Parses and extract data from form-based emails to update databases, send automatic responses, schedule follow-up email messages, and update Outlook contacts. Could be used to handle mailing list subscriptions and unsubscribes.
While this toolkit for Outlook developers does not itself offer any direct linking from Outlook to databases, it does provide several features that make Outlook act more like a database (so maybe you don't need that external database after all) -- record locking, generating a unique ID, field-level data encryption, and lookup from other Outlook folders or from data maintained as a separate Outlook item.
COM add-in for Outlook 2000 or later to tag incoming mail messages with information from a database, so you can more easily identify messages from clients. Also checks for large or missing attachments on outgoing messages.