GET and POST with JSON with Neil Sargent & Leo theDBguy

Mike Wolfe -

Ever wondered how to send data from your Access application to external systems using modern web APIs?

Neil Sargent demonstrates a production-ready solution for both retrieving and posting JSON data through APIs using Microsoft Access and VBA. This presentation goes beyond simple data retrieval to show how Access applications can integrate with third-party systems, featuring a real commercial application that synchronizes electrical testing data between a 20-year-old Access database and a modern web-based scheduling system. The session covers dictionary creation, JSON conversion, batch processing, and conflict resolution strategies.

Whether you're looking to integrate your Access applications with external APIs or simply want to understand the mechanics of posting structured data to web services, this deep dive into production API integration provides practical insights you can apply immediately.

Business Context and Architecture

Real-World Application

  • Electrical testing company using Access for business administration
  • Third-party FieldMotion system handles mobile scheduling and data collection
  • Integration bridges 20-year-old Access database with modern web application
  • Engineers use smartphones for on-site data collection and appointment management

Data Flow Overview

  • Access sends customer, engineer, and job information to FieldMotion
  • FieldMotion schedules appointments and manages mobile workforce
  • Engineers complete forms and record test results on mobile devices
  • Data flows back to Access for invoicing, timesheets, and wages

Integration Challenges

  • Complex relationships: customers → sites → jobs → tests → visits
  • Multiple engineers per appointment
  • Synchronization between systems without conflicts
  • Batch processing to avoid system overload

Technical Implementation

Core API Function Structure

  • Modified FieldMotion's PHP example into VBA equivalent
  • Three parameters: function name, parameters dictionary, data dictionary
  • Automatic addition of API key, timestamp, and MD5 hash for authentication
  • Uses Tim Hall's VBA-Web library for HTTP operations

Dictionary and JSON Conversion

  • VBA dictionaries represent database records
  • Nested dictionaries for complex relationships (jobs containing multiple tests)
  • Collections handle arrays of related records (multiple engineers per appointment)
  • Automatic JSON conversion through VBA-Web library

POST Operations vs GET Operations

  • POST requests used for both sending and retrieving data
  • Parameters posted as JSON rather than URL parameters
  • Body contains actual data being transmitted
  • Response includes either error codes or processed records with assigned keys

Data Retrieval Implementation

Handling API Responses

  • JSON strings sometimes contain embedded JSON (quoted JSON within JSON)
  • Custom parsing to handle nested JSON structures
  • Null value handling for missing or empty fields
  • Forward compatibility checks for unknown fields

Database Integration

  • Automatic field mapping from JSON to Access tables
  • Field existence validation before assignment
  • String truncation to prevent field overflow
  • Memo field handling for longer text content

Data Posting Implementation

Record Preparation

  • Query-based recordsets combining multiple related tables
  • Dictionary creation for each appointment record
  • Embedded arrays for engineers and tests within appointments
  • Composite data structures matching API requirements

Batch Processing Strategy

  • Records processed in small batches (half dozen at a time)
  • Prevents system overload on both ends
  • Error handling for individual batch failures
  • Progress tracking for large synchronization operations

Response Handling

  • API returns processed records with assigned primary keys
  • Local tables updated with server-assigned identifiers
  • Synchronization state maintained for conflict resolution
  • Mirror copy strategy ensures data consistency

Conflict Resolution and Synchronization

Conflict Management

  • "Last one wins" strategy for competing updates
  • Separate note fields for each system to avoid overlap
  • Background synchronization when API is unavailable
  • Timestamp tracking for conflict detection

Data Flow Design

  • Circular flow: Access → FieldMotion → Access
  • Different data types flow in each direction
  • Job creation in Access, scheduling in FieldMotion
  • Results and timesheets flow back to Access

Advanced Considerations

Error Handling

  • API response validation and error reporting
  • Network connectivity failure management
  • Data validation before transmission
  • Rollback strategies for failed operations

Performance Optimization

  • Batch processing for large datasets
  • Background synchronization capabilities
  • Efficient query design for composite data
  • Minimal API calls through intelligent caching

Conclusion

This presentation showcases a sophisticated real-world integration between Access and modern web APIs, demonstrating that Access applications can participate fully in contemporary data ecosystems. The solution effectively bridges legacy systems with modern web services while maintaining data integrity and providing robust conflict resolution.

Recording

The full recording is available on YouTube:

Join Live!

Want to get even more out of these presentations? Join the live Access User Group events! The next upcoming events are listed on the AUG Event Calendar.

Attending live gives you the opportunity to:

  • Interact directly with presenters during Q&A sessions
  • Network with other Access developers
  • Share your own experiences and challenges
  • Get immediate answers to your specific questions
  • Participate in group discussions

With multiple user groups across different time zones (and languages!), you're sure to find a meeting time that works for your schedule.

Acknowledgements

  • Base cover image generated by FLUX-schnell
  • Initial draft generated by Claude-Sonnet-4