How to connect MS Access with OneDrive

ODBC PowerPack API Driver
1

Introduction

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

Prerequisites

Steps

Create an Application on the OneDrive Side

  1. Go to the Azure Portal and log in.
  2. Navigate to Azure Active Directory.
  3. Under App registrations, select New registration.
  4. Enter an application name, choose Accounts in this organizational directory only for the account type, and select Web for Redirect URI.
  5. Enter a valid redirect URL, such as https://login.microsoftonline.com/common/oauth2/nativeclient or https://zappysys.com/oauth.
  6. Save your Application (client) ID, Tenant ID, and Secret ID.
    OneDrive API configuration
    OneDrive API configuration1226×374 38.4 KB

Create a New ODBC Driver

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

  2. For SQL Server Integration, go to User DSN or System DSN and click Add. Select ZappySys API Driver, choose OneDrive as the connector, and click Continue.

    Select OneDrive Driver
    Select OneDrive Driver625×506 50.3 KB

OneDrive Connector Configuration

  1. Fill in the Client ID and Client Secret and specify the required scopes. Use the following URLs for authorization and token generation:

    * https://login.microsoftonline.com/{tenant_id}/oauth2/v2.0/authorize
* https://login.microsoftonline.com/{tenant_id}/oauth2/v2.0/token

  2. Generate the token and test the connection.

    Generate Token and Test Connection
    Generate Token and Test Connection849×749 57.8 KB

  3. In the Preview Tab, select a table and view the results.

    Preview Data
    Preview Data769×600 38.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 previously 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 Real-Time Data (Optional)

Linked tables allow live access to data, supporting real-time data management and simplifying maintenance tasks.

  1. In MS Access, create or open the database where you want to create the linked table. Go to External Data > New Data Source > ODBC Database and select Link to Data Source by creating a linked table.

  2. Select your Machine Data Source from earlier and confirm the selection.

  3. Choose the tables and views to link. When prompted, skip the Unique Key selection and proceed.
    MS Access Linked Table - Key Selection

  4. Double-click the linked table to load data.

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

Considerations

You can save your credentials for easy access in future sessions.

Video Tutorial

Video Tutorial

Conclusion

Using the ZappySys ODBC API Driver, you can easily connect MS Access to OneDrive, enabling streamlined data integration and analysis. Our support team is available on the ZappySys website for further assistance.

References