How to connect MS Access with Outlook in ODBC

ODBC PowerPack API Driver
1

Introduction

This article will guide you through connecting Microsoft Access to Outlook Mail (Office 365) using the ZappySys ODBC API Driver. By leveraging the ZappySys SSIS Outlook Mail Connector, you can effortlessly manage, analyze, and integrate your Outlook Mail data directly within Microsoft Access and other applications. Whether you want to automate data retrieval or enhance your email analytics, this guide will provide step-by-step instructions for a smooth and secure connection.

Prerequisites

  • ODBC PowerPack: Download and install the ZappySys ODBC PowerPack from the Customer Download Area or the trial version.

  • Microsoft Azure Account: Ensure you can access the Azure Portal to create the necessary application.

Steps

Create an Application in Microsoft Azure

  1. Log into the Azure Portal and navigate to Azure Active Directory.
  2. Click on App registrations in the left menu.
  3. Select New registration and provide a name for your application.
  4. Choose the supported account type and specify the Redirect URI as https://login.microsoftonline.com/common/oauth2/nativeclient.
  5. Copy the Application (client) ID, Tenant ID, and Secret ID from your App configuration.
    Microsoft configuration
    Microsoft configuration1226×374 38.4 KB

Create a New Driver

  1. Open the ODBC Data Source by typing “ODBC” in the search box and launching the ODBC Data Source.
    Open ODBC Data Source
  2. To gain access for yourself or other users, go to the User DSN or System DSN. Go to the System tab for SQL Server Integration and add a new System DSN. Click the “Add” button.
    ZappySys ODBC Driver - Open UI
  3. From the driver list, select ZappySys API Driver, then choose Outlook Mail (Office 365) from the connector list and press Continue.
    Select Outlook Connector
    Select Outlook Connector625×506 50.3 KB

Outlook Mail (Office 365) Connector Configuration

  1. Fill in the connector fields with the Client ID, Client Secret, the necessary scopes, and the appropriate Authorization and Token URLs. Then generate the token:
    • Authorization URL: https://login.microsoftonline.com/daed1250-xxxx-xxxx-xxxx-ef0a982d3d1e/oauth2/v2.0/authorize
    • Authorization URL (Without Tenant ID): https://login.microsoftonline.com/common/oauth2/v2.0/authorize
    • Token URL: https://login.microsoftonline.com/daed1250-xxxx-xxxx-xxxx-ef0a982d3d1e/oauth2/v2.0/token
    • Token URL (Without Tenant ID): https://login.microsoftonline.com/common/oauth2/v2.0/token
      Outlook Connector configuration
      Outlook Connector configuration845×731 56.1 KB
  2. Go to the Preview Tab, select a table, and click Preview Data to view the results. Click OK to save the configuration.
    Outlook data preview
    Outlook data preview845×731 55.6 KB

Read Data in Microsoft Access

  1. Open MS Access and create a new database.

  2. Under External Data, create a New Data Source, select Other Sources, and then select ODBC Database.
    MS Access Create Database

  3. Choose the option Import the source data into a new table in the current database.

  4. Navigate to the Machine Data Source, select the data source you created earlier, and click OK.
    MS Access Select Driver

  5. Proceed with selecting tables and views. You can import multiple tables or views.
    MS Access Tables

  6. Wait for the data to load. Once complete, you should see a similar view:

    MS Access Example 1
    MS Access Example 1721×701 61.5 KB

Using Linked Tables for Live Data (Slower Performance)

Linked tables in Microsoft Access are essential for online databases as they allow real-time access to centralized data, support scalability, enable collaboration, enhance security, and simplify maintenance tasks. They offer a flexible and efficient way to work with data stored in online databases, promoting cross-platform compatibility and minimizing data duplication.

  1. Create a new database. Then, under External Data, create a New Data Source, select Other Sources, and then select ODBC Database. This time, choose the option Link to Data Source by creating a linked table.

  2. Navigate to the Machine Data Source, select the data source you created earlier, and click OK.
    MS Access Select Driver

  3. Proceed with selecting tables and views. You can link multiple tables or views.
    MS Access Tables

  4. When prompted to select the Unique Key column, DO NOT select any column(s) and click OK.
    image

  5. Double-click the newly created Linked Table to load the data.

    MS Access Example 2
    MS Access Example 2768×759 49.1 KB

Considerations

Save your authentication details to avoid needing to retrieve them each time you connect.

Video Tutorial

Conclusion

Following these steps, you can easily connect MS Access to Outlook Mail (Office 365) using the ZappySys ODBC API Driver, streamlining the data integration and retrieval process. This powerful tool simplifies the management of your Outlook data, enabling enhanced analysis and reporting. If you encounter any issues or require further assistance, our support team is available via chat on our website.

References

For more detailed information, refer to our ZappySys Outlook Mail (Office 365) Connector.