[AccessD] Retrieve data from a Json data service in collections and user defined types.

Hi all

This can easily done in VBA using the trick that by using the Microsoft Script Control 1.0 you can run Microsoft Jscript which is nearly identical to JavaScript. This way, it is a snap to URL encode the call to the service, and retrieve and URL decode the data.

    https://github.com/CactusData/VBA.CVRAPI

Now, a Json data package can be quite comprehensive, so you may have to write it directly to one or more tables. For the current case - to retrieve company info from the Danish and Norwegian register of companies - the amount of data is not that big, so I collect the data in a VBA collection and use a set of user defined types to ease the reading of the data after basic cleaning and converting (for example: a date is received as a custom string. This is converted to data type Date).

The challenge by this is, that while the items of a collection have a key and a value, you cannot obtain a list of the keys like you can from a dictionary, and if you ask for the value of a key that doesn't exist, you get an error. You can, however, list the values by index.
Searching for solutions to this, I found a workaround where you save not just the value but an array if two elements where the first is the key (the name of the key) and the second is the value.
Now you can list the key/value arrays, and the key will be value(0) and the value will be value(1). Thus:

    DataCollection(n)(0) is the key

and

    DataCollection(n)(1) is the value

As this is recursive - an element can be another collection - it can be quite lengthy, but the logic is consistent:

    DataCollection(n)(1)(y)(1) is the subvalue
    DataCollection(n)(1)(y)(1)(z)(1) is the subsubvalue

Of course, I could have referenced the Microsoft dictionary control and used that, but I think it would just have complicated matters.
So this a good example of using collections.

/gustav