API Integration for MS Access & Excel: Connect Your Data to Any System

What Is API Integration with MS Access and Excel?

• Works with: Microsoft Access 2016, 2019, 2021 and Microsoft 365, plus Excel for Windows and Microsoft 365.

• Protocols: REST, SOAP, GraphQL, OData, plus ODBC, CSV and SFTP when no API exists.

• Authentication: OAuth 2.0 with automatic token refresh, API keys, basic auth and Microsoft Entra ID (Azure AD).

• Sync modes: one-way pull, one-way push, two-way sync, on-demand refresh, scheduled batch.

• Pricing: fixed-scope quote after a free review of your API documentation. Projects typically start at $1,200.

Microsoft Access Integrations: 4 Ways to Connect Access to an API

Already running QuickBooks? See our dedicated MS Access QuickBooks integration service. If your source is a SQL database rather than an API, see Excel SQL database integration.

Example: pull API data into Access with VBA

This is a trimmed version of the pattern we deploy: a timeout-protected GET call, bearer-token authentication, explicit handling of HTTP 429 rate limits, JSON parsing, and a transaction so a failed import never leaves half-loaded data. Adapt the table, field names and endpoint to your system.

Option Compare Database
Option Explicit

' Requires JsonConverter.bas (open-source VBA-JSON, MIT license) imported into the project.
' Staging table: tblCustomersStaging (ExternalID Text, CustomerName Text, Email Text, SyncedAt Date/Time)

Private Function ApiGet(ByVal url As String, ByVal token As String) As String
    Dim http As Object
    Set http = CreateObject("MSXML2.ServerXMLHTTP.6.0")
    http.Open "GET", url, False
    http.setRequestHeader "Authorization", "Bearer " & token
    http.setRequestHeader "Accept", "application/json"
    http.setTimeouts 5000, 5000, 15000, 30000   ' resolve, connect, send, receive (ms)
    http.send
    If http.Status = 429 Then
        Err.Raise vbObjectError + 429, "ApiGet", "Rate limited. Retry after: " & http.getResponseHeader("Retry-After")
    ElseIf http.Status <> 200 Then
        Err.Raise vbObjectError + 500, "ApiGet", "HTTP " & http.Status & " " & http.statusText
    End If
    ApiGet = http.responseText
End Function

Public Sub ImportCustomers()
    Dim payload As Object, rec As Object, rs As DAO.Recordset
    Dim ws As DAO.Workspace, inTrans As Boolean

    On Error GoTo Fail
    Set payload = JsonConverter.ParseJson( _
        ApiGet("https://api.example.com/v1/customers?limit=100", GetApiToken()))

    Set ws = DBEngine.Workspaces(0)
    ws.BeginTrans: inTrans = True
    CurrentDb.Execute "DELETE FROM tblCustomersStaging", dbFailOnError

    Set rs = CurrentDb.OpenRecordset("tblCustomersStaging", dbOpenDynaset, dbAppendOnly)
    For Each rec In payload("data")
        rs.AddNew
        rs!ExternalID = CStr(rec("id"))
        rs!CustomerName = rec("name")
        If rec.Exists("email") Then rs!Email = rec("email")
        rs!SyncedAt = Now()
        rs.Update
    Next rec
    rs.Close

    ws.CommitTrans: inTrans = False
    Exit Sub
Fail:
    If inTrans Then ws.Rollback
    ' Log the error to tblSyncLog here, then surface or retry as needed
    Debug.Print "Import failed: " & Err.Number & " - " & Err.Description
End Sub

Production builds add paging (cursor or offset), exponential back-off, an upsert on ExternalID so reruns never create duplicates, and a tblSyncLog table that records every run.

How to Connect an API to Excel: Power Query, VBA or Office Scripts

Step-by-step: connect a REST API to Excel with Power Query

1. Open the web connector. In Excel, go to Data > Get Data > From Other Sources > From Web and choose Advanced.
2. Enter the API URL and headers. Paste the endpoint URL, then add HTTP request headers such as Authorization: Bearer <token> and Accept: application/json.
3. Parse the JSON response. In the Power Query editor, convert the response to a table and expand the record columns you need.
4. Set data types and load. Set column data types, choose Close & Load To a table or the Data Model.
5. Schedule the refresh. Open Query Properties and enable Refresh data when opening the file, or refresh every N minutes while the workbook is open.

For authenticated APIs, a query like this sends the bearer token in a header and shapes the JSON into typed columns:

let
    // Token is a Power Query parameter (Home > Manage Parameters), not hard-coded in the query
    Source = Json.Document(
        Web.Contents(
            "https://api.example.com/v1/orders",
            [
                Headers = [Authorization = "Bearer " & Token, Accept = "application/json"],
                Query   = [limit = "100", status = "open"]
            ]
        )
    ),
    Rows     = Source[data],
    AsTable  = Table.FromList(Rows, Splitter.SplitByNothing(), null, null, ExtractValues.Error),
    Expanded = Table.ExpandRecordColumn(AsTable, "Column1", {"id", "customer", "total", "created_at"}),
    Typed    = Table.TransformColumnTypes(Expanded, {{"total", type number}, {"created_at", type datetimezone}})
in
    Typed

Tip: store the token in a Power Query parameter rather than in the query text, and never share a workbook with a live token inside. For enterprise setups we move authentication into a secured gateway so end users never see credentials. Related reading: Excel API integration and external system automation and our Excel API and CRM sync case study.

Signs Your Team Needs an API Integration

• Staff export a CSV from one system, clean it in Excel and import it into another, every day or every week.

• Finance and operations report different totals because each works from a different export.

• Customer, product or order records are typed into two or three systems and drift out of sync.

• Month-end close depends on one person who knows the manual steps.

• A new SaaS tool was adopted and nobody connected it to the Access database or workbook that runs the business.

• Auditors ask for a trail of where a number came from and you cannot show it.

What's Included in Our API Integration Services

• Requirements analysis and API documentation review, including rate limits and versioning

• Custom API connector development (REST, SOAP, GraphQL, OData)

• Authentication and security (OAuth 2.0 with token refresh, API keys, encrypted secret storage)

• Field mapping, transformation and data-type handling

• One-way and two-way sync using external IDs and last-modified timestamps

• Paging, error handling, retries with exponential back-off, and duplicate prevention

• Scheduling: real-time, near-real-time or batch

• Data validation and reconciliation reports

• Sync logs, monitoring and failure alerts

• Testing, documentation, staff handover and ongoing support

Systems We Connect to MS Access and Excel

Security, Reliability and Limits You Should Plan For

• Secrets: never hard-code API keys in VBA or cells. We store credentials encrypted, scope them to least privilege and rotate them on a schedule.

• Token refresh: OAuth 2.0 access tokens expire, usually promptly. We build automatic refresh so syncs do not fail overnight.

• Rate limits: APIs return HTTP 429 when you call too often. We page results, throttle requests and retry with back-off.

• Duplicates: every record carries the source system's ID so reruns update rows instead of creating copies.

• Webhooks: desktop files cannot receive them, so we use a small cloud endpoint plus a SQL or SharePoint queue that Access and Excel read from.

• Size limits: an Access file maxes out at 2 GB and an Excel sheet at 1,048,576 rows. For larger volumes we use a SQL Server back end.

• Multi-user: when several people sync at once, we run the integration on one machine or server and share results through the database.

• API changes: vendors deprecate versions. We document dependencies and offer monitoring so changes are caught before they break reporting.

How Much Does API Integration with Access or Excel Cost?

See what the manual process costs before a build

Last updated: October 6, 2026. Reviewed by the Excel Access Expert engineering team.