Using Web APIs in Access with George Young (2024 Edition)
Can Your Access App Talk to the Web? A Guide to Web APIs (an Access User Group talk with George Young; updated for 2024).
Looking to integrate real-time weather data, currency conversions, or other web services into your Access applications?
In this Access Lunchtime presentation, Microsoft MVP George Young provides a practical introduction to working with Web APIs in Access applications. The talk covers both consuming public APIs and creating your own web services, with a particular focus on using the VBA-Web library to simplify API interactions.
Whether you're new to Web APIs or looking to enhance your existing Access applications with external data sources, this presentation offers valuable insights into modern data integration techniques.
Understanding Web APIs
Core Concepts
- Web APIs are URLs that return data instead of web pages
- Uses REST specification for standardized data access
- Returns data in JSON format (JavaScript Object Notation)
- Can be used for both reading and writing data
Key Benefits
- Access to thousands of public data sources
- Standardized data access model across platforms
- Helper libraries available for different development environments
- More secure than direct database connections
- Single data access layer to manage
Practical Implementation
Required Components
- Microsoft WinHTTP Services reference
- Microsoft Scripting Runtime reference
- VBA-Web library (available on GitHub)
- Basic understanding of dictionaries and JSON
Code Structure
- WebClient object handles base communication
- WebRequest object manages specific requests
- WebResponse object processes returned data
- Dictionary objects store nested JSON data
Demo Applications
Weather API Example
- Reads city data from local Access table
- Retrieves weather data using OpenMeteo API
- Demonstrates basic API consumption
- Shows how to process nested JSON responses
Products API Example
- Full CRUD operations (Create, Read, Update, Delete)
- Demonstrates database operations via API
- Shows how to handle different HTTP methods (GET, PUT, POST, DELETE)
- Illustrates form binding techniques
Development Tips
Best Practices
- Use Postman for API testing
- Create temporary tables for data binding when needed
- Structure code for clarity over optimization
- Handle nested JSON data carefully
Common Challenges
- Managing authentication
- Processing nested JSON structures
- Binding API data to Access controls
- Handling different data types
Conclusion
George's presentation demonstrates that integrating Web APIs into Access applications is both achievable and powerful. While there are some complexities to manage, particularly around data binding and JSON processing, the available tools and libraries make it possible to extend Access applications with modern web services.
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.
Related Presentations
If you're feeling deja vu (as I did while putting this recap together), you're not imagining things. I recently posted a recap to a video of George's presentation on this same topic to the Denver Area Access User Group (DAAUG) back in 2023.
Here's the link to that recap:
Mike Wolfe
Also, Maria Barnes built on the foundation of these two videos when she spoke about using the Microsoft Graph API in one of her recent Access Lunchtime meetings:
Mike Wolfe
Acknowledgements
- Base cover image generated by FLUX-schnell
- Initial draft generated by Claude-3.5-Sonnet
