How to connect MS Access with Smartsheet

ODBC PowerPack API Driver
1

Introduction

This guide demonstrates how to connect Microsoft Access to the Smartsheet API using the ZappySys ODBC API Driver, ensuring seamless data integration and retrieval. The ZappySys ODBC Smartsheet Connector offers a reliable solution for incorporating Smartsheet data into MS Access for enhanced reporting and analysis.

Prerequisites

  1. ODBC PowerPack: Download and install the ZappySys ODBC PowerPack from the Customer Download Area or the trial version.
  2. Smartsheet Account

Steps

1. Create a Token to Access the Smartsheet REST API

  1. Log in to your Smartsheet account.

  2. Navigate to Account > Apps & Integrations > API Access and click Generate Token.
    Create Smartsheet API Token for REST API Access

  3. After generating the token, copy it securely for later use (treat this token as sensitive information).
    Generate New Smartsheet Token

2. Configure the ZappySys ODBC Driver

  1. Open the ODBC Data Source by searching for “ODBC” and launching the ODBC Data Source Administrator.
    Open ODBC Data Source

  2. Go to the User DSN or System DSN tab, depending on your setup. Click Add to create a new data source.
    ZappySys ODBC Driver - Open UI

  3. Select ZappySys API Driver, then choose Smartsheet from the connector list. Click Continue.

    Select Smartsheet Connector
    Select Smartsheet Connector625×506 50.3 KB

3. Configure the Smartsheet Connector

  1. Enter the API Token you generated, earn the token field, and enter into the token field. Test that the connecting works correctly.

    Smartsheet Connector Configuration
    Smartsheet Connector Configuration622×545 70.8 KB

  2. Go to the Preview Tab, select a table, and Preview tab data. Click OK to save the configuration.
    OKeview Smartsheet Data|490x500](upload://gKYuR4AwnAgvd2Q9khwjfeWcq2k.png)

4. Import Data into Microsoft Access

  1. Open Microsoft Access and create a new database.

  2. Go to External Data > New Data Source > From Other Sources and select ODBC Database.
    MS Access Create Database

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

  4. Under Machine Data Source, select the configured data source and click OK.
    MS Access Select Driver

  5. Select the desired tables and views, then import the data. You can choose multiple tables.
    access-select-multiple-tables-views-from-the-datasource

  6. Once imported, your Smartsheet data will appear in the Access database.

    MS Access Example
    MS Access Example721×701 61.5 KB

5. Use Linked Tables for Real-Time Data Access

Linked tables in MS Access allow real-time data updates without duplication.

  1. Open Microsoft Access and create a new database.

  2. Go to External Data > New Data Source > From Other Sources and select ODBC Database.

  3. Choose Link to Data Source by creating a linked table.

  4. Under Machine Data Source, select your configured data source and click OK.
    MS Access Select Driver

  5. Select the tables or views you want to link.

  6. Skip the step for selecting a unique key column by clicking OK.
    Skip Unique Key Selection

  7. Open the linked table to load and view live data.

    MS Access Linked Data Example
    MS Access Linked Data Example768×759 49.1 KB

Considerations

  • Save Authentication Details: Save your API Token securely to streamline future connections.
  • Linked Tables: Use linked tables for real-time data access, but note that performance may be slower than when importing data.

Video Tutorial

Conclusion

With these steps, you can efficiently connect Microsoft Access to Smartsheet using the ZappySys ODBC API Driver. This integration simplifies data management and enables powerful reporting and analysis. If you need assistance, feel free to contact the ZappySys support team.

References