MS Access API Integration — Connect Access to Any REST API, QuickBooks, or Salesforce Without Breaking When the Vendor Changes Something

MS Access has no native REST connector. The integration layer is built in VBA using the WinHttp.WinHttpRequest.5.1 COM object, which gives Access full HTTP capability: GET, POST, PATCH, DELETE, custom headers, OAuth bearer tokens, and JSON/XML body construction and parsing. Every production MS Access API integration I build layers four components on top of this HTTP capability.

The first component is the HTTP request layer itself: VBA functions that authenticate against the API (OAuth 2.0 with automatic token refresh, API key, Basic Auth, or ODBC), paginate multi-page responses, respect the API's rate limits, and retry on transient HTTP errors (429, 503, timeouts) with exponential backoff.

The second component is a staging table — a buffer in Access or SQL Server where raw API data lands before any validation or transformation. Your forms, queries, and reports always read from validated, clean production tables. They never read from raw API payloads. The staging table is the contract between the integration and everything downstream of it.

The third component is idempotent keys. Before writing any record to the staging table, the integration checks whether a record with the same unique identifier (QuickBooks invoice ID, Salesforce record ID, order number) already exists. If it does, the record is updated — not duplicated. This makes every retry safe. Without idempotent keys, the retry logic that protects you from failures is also the thing that silently creates duplicate records in your Access database.

The fourth component is a log table and failure alerts. Every integration run writes a record to tblImportLog: when it started, when it finished, how many records it attempted, how many it wrote, how many failed, and the specific API response that caused each failure. If the error count is above zero — or if the record count is unexpectedly zero — an alert fires before your team starts work in the morning. Operations can answer 'what ran last night?' without opening a support ticket.

The most expensive MS Access integration problem is the one nobody knows is happening. A partial import that looks complete because the log file says it ran. A duplicate invoice record that shows up in the aging report three months later. A Salesforce sync that stopped when the OAuth token expired at 2 AM and no alert fired. These are not edge cases — they are the normal failure modes of integrations built without staging tables, idempotent keys, and a log table with real alerting.

Vendor API changes without notice are the second most common failure mode. A shipping carrier renames 'TrackingNumber' to 'tracking_id' in a response update. The VBA parsing code that looked for 'TrackingNumber' now returns empty strings. The staging table fills with blank tracking fields. Nobody notices until a customer calls about a missing shipment. A schema hash check on the inbound response structure catches this at the pipeline level and alerts before a single blank record reaches the Access database.

Integration debt also compounds. A pipeline that worked fine when first built — low volume, one user, stable API — starts breaking as data volume grows, more users rely on the sync, and the vendor iterates on their API. Batch size tuning, connection timeout handling, and retry logic calibrated for real network paths (including VPN and remote office scenarios common in US businesses) are what separates an integration that holds for years from one that needs constant maintenance.