How to connect MS Access with Gmail in ODBC

ODBC PowerPack API Driver
1

Introduction

This article will guide you through connecting Microsoft Access to Gmail using the ZappySys ODBC JSON Driver. This integration lets you retrieve, manage, and analyze your Gmail data directly within MS Access. By leveraging the power of the JSON Driver with OAuth, you ensure secure and efficient data handling, which is particularly valuable for organizations looking to streamline communication workflows and enhance data-driven decision-making processes. Whether you’re looking to analyze email patterns, archive communication, or integrate Gmail data with other systems, this guide will walk you through the necessary steps to achieve a seamless connection.

Prerequisites

Steps

Create/Select a Project in the Google API Console

  1. Navigate to the Google API Console.
  2. Click on the Project Dropdown at the top bar and either select an existing project or create a new one by clicking CREATE PROJECT.
    Create Project
    Create Project744×230 20.9 KB
  3. Once the project is set, click ENABLE APIS AND SERVICES.
  4. Search and enable the Gmail API service.
    Enable Google Gmail API
  5. Return to the main screen and click on the OAuth Consent Screen tab. Provide the necessary details and save.
  6. Move to the Credentials tab.
  7. Click CREATE CREDENTIALS in the top bar, choose OAuth Client ID, select Desktop App as the Application Type, and click Create to obtain your Client ID and Secret.
    Create Credentials
    Create Credentials871×334 38.8 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 JSON Driver.
    Select JSON Driver

Connect with Gmail API in ODBC JSON Driver

  1. Enter the following URL:
    https://www.googleapis.com/gmail/v1/users/me/messages/
  2. In Connection Type, select OAuth and click on Configure.
  3. Select Google API in the OAuth provider and enter the credentials from the previous step. For the scope, use:
    https://mail.google.com/
  4. Generate the Token and click OK to save the configuration.
    Gmail OAuth connection
    Gmail OAuth connection618×595 18.6 KB
  5. Select the GET request, use the filter to get the messages like this:
    $.messages[*] and test the connection.
    Gmail configuration
    Gmail configuration804×666 35.8 KB
  6. Go to the Preview tab and check the data you will receive. You can check more details in the Gmail API documentation.
    Gmail preview data
    Gmail preview data804×666 44.4 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 Gmail using the ZappySys ODBC JSON Driver, streamlining the data integration and retrieval process. This powerful tool simplifies the management of your Gmail 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.