How to connect MS Access with OData

ODBC PowerPack API Driver
1

Introduction

This guide provides a step-by-step walkthrough to connect MS Access to OData using the ZappySys ODBC API Driver for efficient data integration. This connection simplifies managing and analyzing OData data across various applications.

Prerequisites

Steps

Create a New ODBC Driver

  1. Open ODBC Data Source by searching “ODBC” on your system.
    Open ODBC Data Source

  2. To enable access for yourself or other users, go to User DSN or System DSN. Select System DSN for SQL Server Integration and click Add.
    ZappySys ODBC Driver - Open UI

  3. From the driver list, choose ZappySys API Driver, select OData from the connector list, and press Continue.

    Select OData Connector
    Select OData Connector625×506 50.3 KB

Configure the OData Connector

  1. Set the Authentication Type (e.g., Token Authentication) and specify the API Base URL. For additional details, refer to the Authentication section.

    OData Connector
    OData Connector806×706 50.1 KB

  2. Go to the Preview Tab, select a table, and review the result. Click OK to save your configuration.

    Preview Data
    Preview Data806×706 53.3 KB

Import Data in Microsoft Access

  1. Open MS Access and create a new database.

  2. Navigate to External Data > New Data Source > Other Sources > ODBC Database.
    MS Access Create Database

  3. Choose Import the source data into a new table.

  4. Under Machine Data Source, select your newly created data source and click OK.
    Select Data Source

  5. Select tables and views to import, then load the data.
    MS Access Tables

Using Linked Tables for Live Data (Optional)

Linked tables in Microsoft Access allow real-time access to data, centralized management, and simplified maintenance, which is especially useful for online databases.

  1. In MS Access, open the database where you want to create the linked table.

  2. Go to External Data > New Data Source > ODBC Database and choose Link to Data Source by creating a linked table.

    Load ODBC data source
    Load ODBC data source722×515 44 KB

  3. Continue to the Data Source Selection window, then select the desired Machine Data Source. Click OK to confirm.
    DSN selection

  4. Choose tables and views to link, then skip the Unique Key selection when prompted. Double-click the linked table to load data.

    MS Access Linked Table
    MS Access Linked Table764×755 49 KB

Considerations

Save your authentication details for quick access in future sessions.

Video Tutorial

Video Tutorial

Conclusion

Using the ZappySys ODBC API Driver, you can seamlessly connect MS Access to OData, enabling efficient data integration and analysis. For additional help, visit our support team on the ZappySys website.

References