37. (Advanced Programming In Access 2013) Importing JSON To A Table From A RESTful Web API

Importing JSON To A Table From A RESTful Web API video in the "Advanced Programming in Microsoft Access 2013" series hosted by Steve Bishop. In this free advanced video tutorial series Steve will be going over Microsoft SQL Server installation, Database Migration, creating a better User Interface, using external data sources, complex Visual Basic For Applications (VBA) concepts and distributing your application.

Click here for the full playlist of "Advanced Programming in Access 2013":

Click here for the Work Files of this series:

Link to vba-json class:

[courtneystearns]

Hi Steve. This is a fantastic post. I particularly appreciate how you focused on the minimum viable product to help one work with XML and JSON in VBA. When I was ready for more, I reviewed one of your other videos that went deeper into the weeds of the how the XML and JSON header, detail and action verbs work. I was able to use this to integrate web applications with local ERP systems and it worked like a champ. More importantly, I have now have a framework and tool set to make future integrations far easier to accomplish. Thank you very much!

[bobalston]

Wonderful video. thank you. any chance you can post the Access database to avoid all the cut n paste and especially the properly setting up of the class object?

[tarunprakashsingh]

hey is there a way parse and store values from JSON with out knowing the data structure. example how many Items/sub-items or rows it will have. Trying to build something where it can parse any JSON to a excel Sheet. Any help would be really great. Thanks

[Lamborgful]

Great video. If I've been given a txt file formatted as .json how do I utilise the parser to push the records into a table. The txt file has 500, 000 records in it?

[chenhuiboy]

Hi Steve, I just your useful video. However, the vba-jason class link doesn't work anymore. I tried google it, but No Luck. Would you be able to send me another link for this source code?  Thanks in advance.

[swapnilwankhede]

Hi Steve
Excellent Tutorials, Thanks a lot Steve for uploading this videos, it’s really helpful, never found such good videos.

[schalld]

is there a way in vba to know which response type we are getting so we can then execute json parser vs xml parser

[terjegundersen]

Terje Gundersen
Thank's for your great video. Do you have any plans for a tutorial that explains how to update from a table in Access back to the webserver in Json format?

[ΠΑΝΑΓΙΩΤΗΣΑΝΤΩΝΕΛΛΟΣ-ζφ]

Hello, I get an error message "Certificate Authority is invalid or incorrect." after they (host) updated there ssl certificate on the server.

[SAMYTAHER]

thank you for the interesting, beautiful explanation

[tibtill]

Hi, Steve. Thank you very much for sharing with us a quite important part of your gold-mine knowledge. Importing JSON to a table is something I was wondering how to do for a very long time. However, I have a very specific situation: like somehow first I need to POST something on the webserver and then GET associated data to my request. So, as you understand, I'd love to know if I can use Access VBA to send (POST) my demand, as well. For example: let's say I have some partners and I have the opportunity to send (POST) a list (an array) of IDs and current data [{"ID":1234, "Date":"2017-09-30"}, {"ID":3456, "Date":"2017-09-30"} ...] and get back from the webserver another array containing the ID, request date, partner name, code of activity and True/False if the partner is still active... could you help me? Thank you.

[yvettethompson]

Now I have an integer in my Collection and Thing.Item("IntegerField") won't work. Any ideas? THANK YOU

[timaxproductions]

Thank you for the video! Please help me to resolve one issue. Followed your tutorial and everything works except that I fetch the same data from API unless I restart access... Do I need to close any connections everytime I fetch new data or anything?

[carloscollao]

Hi JJ Dasher I have the same problem :  I get a runtime error 13 Type Mismatch on the line "Set Coll = Any ideas?

[yvettethompson]

THANK YOU SO MUCH for these videos! They are wonderful. My API import into Access is getting held up on the first record - I am suspecting it is because we use colons in one of our fields. I would love to send you 2 screen shots where I am stuck. Is that possible? Thank you again, Yvette

[snoble]

Can anyone help point me to this code? i am trying the link and the comments below but not able to source the code. Thanks!

[worawutmaneeruja]

Thank you for this useful video.
Just wonder if there is any code for creating from scratch the table from JSON respondText.
Thank you in Advance.

[pjamiesonnz]

Another great Video Steve.  I have one main problem immediately - Pretty much all my tables have a hyphen (-) in the name as well as most fields.  This caused VBA to error out as soon as I type the code as it thinks it's a subtraction.  I've tried putting quote marks and square brackets around and this does not work.

[victoriafriess]

This has been really helpful. Thank you for your efforts in teaching this.

I have run into a type mismatch error when attempting to add a new record into my table. This is my first stab at importing JSON into MS Access, and I feel like I am VERY close. Any pointers on how to get over this hump?

[JJsMessyBench]

I've been trying for hours to get this to work. The JSON parsing class seems to work, but once it comes back to the module, I get a runtime error 13 Type Mismatch on the line "Set Coll = Any ideas?