Using/parsing JSON with vba

[jdraw]

Tony,Colin

In any comments related to screen recordings, would you please include what you are using/doing for video editing.
After making an extremely crude video to assist a user, I searched google looking for editing software. I haven't used it,
but I did find some great tutorials on a free editor Blender. It seems it is used for much more, but the free editor was
a feature highlighted in my search.

I really appreciate your efforts in this thread and subject matter.

 

[isladogs]

BB Flashback comes with both Recorder & Player (editor) programs.
I believe that's the case for most programs of this type

I have also used an open source program called Virtual Dub for editing videos.
Its available from http://virtualdub.sourceforge.net/

 

[Uncle Gizmo]

Tony,
I tried the link in your #47 and did not get a video.
I've attached screen shot of where I landed??

Hi Jack,

I posted an "Editing" URL...

I've updated it now.. See HERE:-
https://www.access-programmers.co.uk/forums/showpost.php?p=1546065&postcount=57

Direct Link Here:-
I did a YouTube video demo of one of the most recent additions to its functionality here:- https://youtu.be/Xbnx_B1Mik4

 

[Uncle Gizmo]

I tried using the VBA jsonparser a few years back however I couldn't get on with it, it always developed an error message. However I have been looking at it again and I have discovered that it is a "user error" or possibly a little bit of misinformation creeping in.

I understood it to be a "Class Module" therefore I installed it as a class and this is what caused the error. I have now installed it as a module and it works fine, once you get the references sorted out of course!

I should have persevered with it a bit longer, I would have found the issue. However in my defence, I had recently installed the 64 bit version of MS Access and just assumed it was just one more issue caused by that piece of ----- "software"!

I have been looking at the example database "A JSON parser database" provided by Colin and it's very impressive.

I reckon the next step is to extract the keys from the Json and use those keys to build the tables automatically.

However I could not find how to extract the keys with VBA, then I found this comment by "Codo" here:-

The JScriptTypeInfo object is a bit unfortunate: it contains all the relevant information (as you can see in the Watch window) but it seems impossible to get at it with VBA.

URL:-
https://stackoverflow.com/questions/5773683/excel-vba-parsed-json-object-loop

As far as I can tell VBA is unable to extract the keys from Json. The workaround appears to be to use the JavaScript parser within VBA. The above link is where I got the information from I am still looking into it...

If anyone else has ventured down this route and can offer any help, comment, advice or critique then that would be most welcome.

 

[isladogs]

Hi Tony

The JScriptTypeInfo object is a bit unfortunate: it contains all the relevant information (as you can see in the Watch window) but it seems impossible to get at it with VBA.

I'm pleased to say the author of that quote (Codo) is wrong as I've already completed this step

Version 3.1 of the parser includes the following changes:
a) updated code so it should now work in Access 2007
b) analyser feature which determines the field names / datatypes & field size then creates the table automatically
For example:

If the JSON file contains subarrays, these are also identified by the analyser & recommended action for splitting the data into several tables is provided:

Next I'm going to write code to create multiple tables based on the analysis.

Finally I hope to automate the process of creating the transform function, again based on the analysis

I would be VERY grateful if others would be willing to test the new version and provide feedback.

Unfortunately the files are now too big to upload to the forum.
However if anyone is willing to do so, please email me using the link below or send me a private message. I will then provide you with a link so you can download the latest version.

See attached PDF file (zipped) which gives details about using the latest version