Скачать или смотреть AL:Using Dictionaries and Collections to work with JSON in Access: Maria, Neil Sargent, Leo theDBguy

Today we dig into how to use Collections and Dictionaries to work with JSON in Access, following from George Young's excellent presentation on using Web APIs. Maria Barnes is joined by Neil Sargent and Leo theDBguy.

LINKS

Tim Hall's library
https://github.com/VBA-tools/VBA-JSON/

JSON Parser
https://jsonparser.org

Using the Dictionary Class in VBA
by Patrick Matthews
https://www.experts-exchange.com/arti...

Learn: Collection object
https://learn.microsoft.com/en-us/off...

article: Introducing JSON
https://www.json.org/json-en.html

Slides and Demo database
http://accessusergroups.org/lunch/wp-...

00:01 Welcome
42:08 Dictionary and what it is
1:00 Microsoft Scripting Runtime reference
01:12 article: Using the Dictionary Class in VBA by Patrick Matthews
https://www.experts-exchange.com/arti...
1:25 Dictionary is keys and items
Add, Exists, Items, Keys, Remove, RemoveAll
03:07 Dictionaries can be nested
03:57 download Sample Database VBA Module, mod_CollectionsAndDictionaries: TestDictionaries
4:44 Collection part of the VBA language
https://learn.microsoft.com/en-us/off...
ordered set of items
Add, Item, Remove, Count
Employees - nest a Dictionary inside of a Collection
Dictionary can be nested inside a Dictionary
nest a Collection inside of a Dictionary
nest a Dictionary inside of a Collection
Nesting both directions is what gives Dictionaries and Collections the power of making a JSON object.
07:33 VBA TestCollections
add each employee to the collection
08:10 What is JSON?
Java
Script
Object
Notation
article: Introducing JSON
https://www.json.org/json-en.html
lightweight format for storing and transporting data
09:24 JSON Syntax Rules
[thanks to Phil for corrections]
Curly braces hold objects
Square brackets hold arrays
Implement objects as dictionaries and arrays as collections
15:22 To create JSON
Collections (VBA) – to make the arrays
Dictionaries (Microsoft Scripting Runtime) – to make the objects
15:50 Tim Hall's Library
Automatically converting and parsing JSON
https://github.com/VBA-tools/VBA-JSON/
16:34 tool: JSON Parser
17:01 Downloading Tim Hall's tool from GitHub
17:33 Module: JSON Converter for VBA
17:57 run TestDictionaries in Immediate window
18:44 Locals window
19:53 ConvertToJson on Body
20:26 jsonparser.org to make JSON understandable
22:50 main functions
ParseJson
ConvertToJson
24:28 Neil to read JSON, understand how its converted into VBA
25:52 Google APIs Explorer
https://developers.google.com/apis-ex...
26:03 API Reference tells how to use free Books API
https://developers.google.com/books/d...
26:54 Access table
27:07 vba: result pretty unreadable, string is about 30,000 bytes, list of 10
27:28 ParseJson function takes a string and returns an object we can work with
27:35 what type of object has it returned?
27:47 TypeName
27:58 Dim As Scripting.Dictionary to look at with Intellisense
, rather than on object where it really doesn't know what it's describing
28:43 API documentation - what's coming back from the API?
29:21 vba Count
loop through each of the keys
29:53 data types
30:42 Immediate window
30:59 in API definition, totalItems is integer yet TypeName returns a Double
31:09 For Each varKey in Dictionary.Keys
32:19 For Each varItem in Dictionary.Items
32:49 another way, loop and change Item index
Scripting.Dictionary always return a 0-based array
34:01 what to do with each of the data types
36:56 Item list
36:10 JSon parser
36:29 decode JSON into a set of dictionaries, collections, or normal VBA variables
37:38 VBA Collection is 1-based
38:34 Reflection on collections
40:21 article on dictionaries is superb (by Patrick Matthews)
40:44 done parsing, now convert
41:57 ConvertToJson has a whitepsace parameter to make it easier to read
42:34 Prettifying JSON strings
ParseJson
ConvertToJson
45:30 confusing when a lot of data comes back from the API
48:13 JSON string back from API, extract author
49:47 vba
50:52 3 authors
52:26 Problems using VBA-JSON tool like lack of support for Null and multibyte Unicode characters
53:01 February
53:35 Resources
54:51 Leo - other ways of parsing JSON
56:54 Maria - JSON can be really big when you get it back
57:46 Neil - API might have a Fetch individual by ID function available
59:42 George - limit to TOP number
1:00:12 Neil - list doesn't come back in any particular order
1:01:16 Maria - clarify. Special thanks to Neil and Leo!
1:00:02 calendar invite for new year

NEXT
Access Lunchtime – Parsing JSON Data using VBA with Maria, Neil, and Leo
Tue, Feb 25
https://accessusergroups.org/lunch/ev...

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