Web APIs in Access with George Young

Mike Wolfe -

Ever wished your Access application could tap into real-time weather data, currency rates, or map services?

In this Denver Area Access User Group presentation, George Young demonstrates how to integrate web APIs into Microsoft Access applications using VBA. The presentation covers both basic data retrieval (using a weather API example) and full CRUD operations (Create, Read, Update, Delete) through a sample product management interface. George walks through the technical implementation details while explaining the core concepts of web APIs, RESTful services, and JSON data formats.

Whether you're looking to enhance your Access applications with external data sources or build modern distributed systems with Access as the front-end, this comprehensive introduction to web APIs provides the foundation you need to get started.

Understanding Web APIs

Core Concepts

  • Web APIs are essentially URLs that return data instead of web pages
  • Uses standard HTTP methods (GET, POST, PUT, DELETE) for operations
  • Returns data in structured formats (typically JSON)
  • Provides programmatic access to remote services and data

Benefits for Access Developers

  • Access to vast array of online services and data sources
  • Single standard data access model across different services
  • No need to install multiple libraries for different data sources
  • Secure access to remote data without exposing database credentials

Technical Implementation

Required Components

  • VBA-Web library for handling API requests
  • Microsoft WinHTTP Services reference
  • Microsoft Scripting Runtime reference
  • Postman (optional) for testing API calls

Basic Structure

  • WebClient object handles base URL and execution
  • WebRequest object manages API path, method, and parameters
  • WebResponse object receives the returned data
  • Dictionary objects parse JSON responses

Working with APIs

GET Operations (Reading Data)

  • Simplest form of API interaction
  • Used for retrieving data from services
  • Supports optional query parameters
  • Returns data in JSON format

POST/PUT Operations (Creating/Updating)

  • Requires body parameters with data
  • Used for adding or modifying records
  • Must handle required fields appropriately
  • Returns status codes indicating success/failure

DELETE Operations

  • Simplest write operation
  • Requires only an ID parameter
  • Returns status code indicating success
  • No response body needed

Implementation Examples

Weather API Integration

  • Demonstrates basic GET requests
  • Uses latitude/longitude parameters
  • Parses nested JSON response
  • Shows error handling for failed requests

Product Management System

  • Shows full CRUD implementation
  • Demonstrates proper parameter handling
  • Illustrates response processing
  • Examples of proper error checking

Building Your Own API

Using Visual Studio

  • Can create APIs with minimal coding
  • Supports Entity Framework for database access
  • Allows custom SQL and stored procedures
  • Provides automatic documentation

Deployment Options

  • Can run locally for development
  • Deploy to Azure or other cloud services
  • Use within corporate networks
  • Support multiple client applications

Limitations and Considerations

Technical Constraints

  • Must handle network connectivity issues
  • Need to manage API rate limits
  • Consider security implications
  • Handle authentication when required

Development Considerations

  • Test API responses thoroughly
  • Use Postman for initial testing
  • Consider error handling carefully
  • Document API requirements

Resources and References

Available Tools

  • VBA-Web library for implementation
  • Postman for API testing
  • Visual Studio for API development
  • Sample code available through DAUG

Conclusion

The presentation demonstrates that integrating web APIs into Access applications is both achievable and powerful, opening up new possibilities for extending Access applications beyond traditional database operations. While there are some technical considerations to manage, the standardized nature of web APIs makes them an attractive option for modernizing Access applications.

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-3.5-Sonnet