Скачать или смотреть AL: Get and Post with JSON using Microsoft Access and VBA by Neil Sargent (+ theDBguy)

Welcome to Access Lunchtime for a special presentation deep diving into JSON using a web API with Microsoft Access. Neil Sargent eloquently shows how to exchange data both ways -- read and write, how to encode and how to decode. He also shares his example database so those of you who want to try yourself can do it.

download
Neil's sample database, VBA-Web-V4.1.6a.accdb, is included here:

https://accessusergroups.org/lunch/wp...

Leo, theDBguy, helped with testing, answers questions in the chat, and comments.

Thanks to Maria, Neil, and Leo.

videos for relevent previous presentations

Recently for Access Lunchtime, is how to convert JSON results passed by web APIs to values and structures that VBA and Microsoft Access can understand.

1. AL: Using Dictionaries and Collections to work with JSON in Access: Maria Barnes, Neil Sargent, Leo theDBguy (1:03:47)
   • AL:Using Dictionaries and Collections to w...  

2. AL: Parsing Data using JSON and VBA in Access, Neil Sargent and Leo (the DBguy)
   • AL: Parsing Data using JSON and VBA in Acc...  

and how to use web APIs

AL: Working with the Microsoft Graph API from VBA with Maria Barnes (1:09:49)
   • AL: Working with the Microsoft Graph API f...  

AL: Using Web APIs in Access by George Young (1:03:31)
   • AL: Using Web APIs in Access by George Young  

web API

Tim Hall’s free VBA-WEB tool with source code on Github
https://github.com/VBA-tools/VBA-Web
(WebHelpers module in Neil's download)

0:01 Access Lunchtime JSON after party.
0:22 Neil Sargent real world example
show you detail about how we take our tables from Access, turn then into JSON dictionaries, post them up to the API, where it takes the information and puts them into SQL tables.
1:13 Application
Company does electrical testing for compliance, health, and safety. Electricians go in, do job, and bill sent.
Access application does all the administration except scheduling, which is done by a 3rd party application with a web API.
Relationship Diagram
2:36 Contacts are customers
2:42 multiple Sites
2:48 Jobs
2:55 multiple Tests for each job
3:18 Engineers
3:23 Engineers go on visits to perform jobs of multiple tests.
3:40 scheduling tables with FM prefix
store data used in API application for appointments
4:42 interface is Access tables to store data fron other system and tables to drive business
4:59 API send information up about customers, jobs, and engineers
5:12 fetch down information about appointments and read data they collect while they do their visit and store in Access
5:26 also fill information for timesheets from time spent on site
5:38 posting records from our system to their system, and reading records back from their system into our system
5:51 VBA
5:55 FieldMotion API documentation
https://docs.fieldmotion.com
API request
uses POST with parameters
even for Get, in this system
7:22 PHP code example on website for post
7:39, 7:53 VBA to run API function -- name, parameters, data
8:14 data parameter is optional
8:53 relationships diagram
8:56 Job record will become a dictionary entry
that includes a JSON array of related test records
9:56 VBA
10:04 clone dictionary if sent
10:42 new collection using the web client in the JSON Api
10:52 Method is HttpPost rather than normal get
Method, RequestFormat, ResponseFormat, Resource, body
12:11 Response
12:51 example of getting data down
13:00 API's _get function
13:14 in example, everything is a string
13:23 null isn't delimited
13:41 string value with JSON in it
intepret as JSON not a string
14:43 Get appointments
VBA read what comes back into a dictionary and then save it
15:08 If don't recognize, AddNew, Else Edit
15:13 loop each item in the data dictionary
save to appointment table
TypeName
16:41 Post appointments
16:49 relationship diagram
wrap up database records into a JSON dictionary to send up to the web
17:05 open recordset using a query with job information and when engineers have to do a job
relationship diagram showing tables data is coming from
17:33 Do loop for appointments to tell API system about
create new dictionary for each apppointment including:
dictionary of engineers, customer, when to schedule, notes, dictionary of values to fill in, collection of job tests,
19:21 appointment dictionary is created, with its embedded arrays of dictionaries (tests, engineers) within it
19:37 post up in batches
FM_AppointmentPostBatch
object is a collection of records, one per appointment:
existing apppointments to update or new appointments to create
call appropriate API function
21:09 FM_api(strFunction, oParams, oData)
21:19 relationship diagram -- what it does for reponse
22:19 showed you how to turn your database records into a set of dictionaries and collections
23:16 Q&A

Access Lunchtime hosted by Maria Barnes
https://accessusergroups.org/lunch/

Mike Wolfe's write-up on NoLongerSet:
https://nolongerset.com/aug-get-post-...