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

[@courtneystearns8680]

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!

[@bobalston7399]

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?

[@jabariprice6254]

The link you provided (code.google.com/archive/p/vba-json/) has been archived on google. Do you know were else I could find the vba-json code? Thanks.

[@swapnilwankhede3440]

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

[@SAMYTAHER]

thank you for the interesting, beautiful explanation

[@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?

[@terjegundersen1235]

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?

[@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.

[@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

[@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.

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

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

[@worawutmaneeruja2881]

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.

[@schalld1]

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

[@ProgrammingMadeEZ]

If the body begins with a < then it's XML. If it begins with { or [ then it's JSON.

[@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

[@ProgrammingMadeEZ]

+Yvette Thompson What makes you think it's the colon that's the problem?

[@yvettethompson]

It is not the colon. Now I am suspecting tha it is an extra nest of "{" at the beginning that says DATA: here is my reader.responseText: ? reader.responseText {"data":[{"ID":"55f9fa5900aa06c2bfdc4c297244c12c","name":"Marketing Initiatives Calendar","objCode":"PROJ","DE:Job Code":"200628--Special-18268","DE:Server Location":"M:\\MARKETING\\Marketing\\\\Marketing Initiatives Calendar\\Missing Medium"},{"ID":"55fa06dd00ac6e797b3dd89502c1fe87","name":"Marketing Work Request","objCode":"PROJ","DE:Job Code":"150826---19070","DE:Server Location":"M:\\MARKETING\\\\\\Marketing Work Request\\Missing Medium"},{"ID":"55fa06ed00ac6fc31ee0d5bae9706ce5","name":"Plan and Design Workfront Implementation","objCode":"PROJ"},{"ID":"561d48a3001d8d6f2100be8c911f903e","name":"IT Request Queue","objCode":"PROJ","DE:Job Code":"211001","DE:Server Location":"M:\\MARKETING\\Human Resources\\ITTessi\\\\Missing Medium"},{"ID":"561d4a3a001f1e4eee983b1b884aa693","name":"IT Work Request","objCode":"PROJ"},{"ID":"562a1d5600ab15f859946948c5b71d5e","name":"RP: 2015 - 11\/04 Members FTLN, Meet Your Mammoth, Membership as Gift, CoL, KidsClub at DMA","objCode":"PROJ","DE:Job Code":"151104-Eblast-eBlast s-100938","DE:Server Location":"M:\\MARK

[@victoriafriess9805]

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?

[@colddonkey]

I'm realizing that my (same guy, different log in) json site starts with this: {"Data":[{"Id":17,"SpiPartNumber":"COM.0631E","LinkCode":"AF1JAST1COM.0631E","Description":null,"CustomerPartNumber":null,"StandardPackQty":40," I think the first {"Data": might be to blame?? I need the fields after the initial {"Data"...what would be the best way to approach this? Sorry for being a newbie :-/

[@colddonkey]

Well...for now, I just trimmed off the first 8 characters of the string and it seems to be working as intended. Maybe I can get some sleep now...:-)

[@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?

[@chrisvanderhaar9278]

Hello Steve, First of all, all the compliments to you for all of your video's. The are clear and even for me good to understand. The above video of importing JSON is working for me. But there is one small problem with the inoported JSON file. The file i have to import has an extra child node in it. The Ulr is json.jmt.nl/?ver=2 These are furniture that we also want to import in our database. Is there a way of importing the child nodes as well? Perhaps you have a tip for me how i must work this out.

[@carloscollao3850]

Hi JJ Dasher I have the same problem : I get a runtime error 13 Type Mismatch on the line "Set Coll = json.parse(reader.responseText)". Any ideas?

[@twopeaksendurance]

This function gives the Json without the first "wrong" part: Function jsonkurz(Json As String) As String Dim i As Integer Dim lengthjson As Integer i = 1 Do Until Mid(Json, i, 1) = "[" i = i + 1 Loop i = i - 1 lengthjson = Len(Json) jsonkurz = Right(Json, lengthjson - i) End Function and in the main function: If reader.status = 200 Then Set db = CurrentDb Set rs = db.OpenRecordset("tblSteuern", dbOpenDynaset, dbSeeChanges) Jsonshort = (jsonkurz(reader.responseText)) Set coll = Json.parse(Jsonshort)

[@snoble1978]

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!

[@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 = json.parse(reader.responseText)". Any ideas?

[@ProgrammingMadeEZ]

+JJ Dasher Did you compare it to the work files?

[@BrianJohnson-os6tu]

JJ Dasher: Did you ever figure this issue out? I am having the same problem and see no difference between my references or work files.

[@BrianJohnson-os6tu]

Steve: Firstly thank you for all of the videos. I know nothing about programming and have learned a ton from them. I asked JJ Dasher if he resolved this issue but probably should have asked you directly. I get this error in my DB, but not in your work files even though all the same. I assume I am missing a reference?

[@ProgrammingMadeEZ]

I'm thinking it has to do with the data and the parser. If the JSON starts with a { then the parser will return a Dictionary. If the JSON starts with a [ then it will return a collection. So since the variable we are trying to set the result to is a collection, but the JSON starts with a { then we'll get a type mismatch since the parser will be returning a dictionary instead of a collection.

[@BrianJohnson-os6tu]

You are good. I kept running the code trying to figure out the issue and when I hover over the line in question I get reader.responseText"{. I switched the variable from collection to dictionary and this allowed code to jump this error, but the value remains the same and my rs items all error on null. Thank you for your help and I will see if I can muddle around and work it out.

[@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.

[@ProgrammingMadeEZ]

Hyphens in names of things in general is bad. If anything use underscores, but you can get around this in most cases by surrounding your tablenames or column names with []'s when you call them. So My-Table would be [My-Table] in your query.

[@pjamiesonnz]

I have no control over the tables, it's a global system. The brackets have worked, thanks!

[@PatrickChalier]

Hello, great tutorial thanks ! Unfortunately the vba-json code on google seems to have dissapeared ! Could you upload it to pastebin maybe ? Thanks in advance, Patrick.

[@ProgrammingMadeEZ]

+Patrick Chalier It is in the work files.

[@jmonti777]

The code in the link is no longer there. Any way you could post it here or somewhere we can grab it? Thank you so much

[@ProgrammingMadeEZ]

I just tried it and it seems to be working fine for me. That link is just to the root folder of the project files. You have to go to the folder where the file is located.

[@jmonti777]

when you say where the file is located where would that be? I see the project folder which is where it takes me. then I see the source, issues, wikis, and downloads links to the left. I have drilled down to the source then again to the source. I have downloaded the code in that window. yet I am not sure which is the code or file to use. I apologize for this as I am a newbie. trying to learn. thank you so much

[@ProgrammingMadeEZ]

When you go to the link in the description it should take you to a google drive location. From there you will see multiple folders, and they begin with a number. The number corresponds to the number of the video, so this video is #37 and the work files folder you're looking for starts with 37.

[@mattseweryniak7016]

The link in the description is not working - it is providing error 406 requires you to sign on, - after signing in to youtube/google still getting that

[@yvettethompson]

Thank you so much for your wonderful training. I would love to pay you for your support (I need some more help!) I have successfully imported nested loops(collections) into Access using your video. I am now stumped with a situation where I ask for fields=parameterValues and it returns a list of custom fields. When it is another collection then it has a bracket "[" and I know how to grab that. But this doesn't, and I am having trouble grabbing the values. In my example I ask for fields= resolvables, parameterValues. The resolvables is another collection, but what the heck is the parameterValues? (It is some sort of a list) I can't use the Thing.Item() to grab the fields... Here is how it returns: _________________________________________________________________________________ "data": [ { "ID": "566897750011d716ac10b4a71c59ae64", "name": "RP: Winter Wonderland Sleepover PDF for Registered Guests", "objCode": "PROJ", "status": "CPL", "resolvables": [ { "ID": "56689708001178a381b30dcca2abd03d", "name": "Winter Wonderland Sleepover PDF for Registered Guests", "objCode": "OPTASK" } ], "parameterValues": { "DE:TemplateCalc": "Layout - PDF Layout", "DE:FilePath": "M:\\MARKETING\\Sales\\Sleepovers\\Winter Wonderland Sleepover PDF for Registered Guests\\In-Museum Digital Signage\\151211-PDFLayout-Sleepovers-110203-CM\\", "DE:PlannedDateCode": 151211, "DE:SubCodeCalc": "Sleepovers", "DE:Job Code": "151211-PDFLayout-Sleepovers-110203-CM", "DE:Designer": "CM", "DE:Department": "SAL - Sales", "DE:Server Location": "M:\\MARKETING\\Sales\\Sleepovers\\Winter Wonderland Sleepover PDF for Registered Guests\\In-Museum Digital Signage", "DE:Medium": "In-Museum Digital Signage", "DE:Digital Elements": "PDFLayout - PDF Layout", "DE:Sales Products": "Sleepovers - Sleepovers ///FAMILY ADVENTURES ////EARNED REVENUE", "DE:ElementCalc": "PDFLayout", "DE:Element Format": "Digital", "DE:SubCode": "Sleepovers", "DE:DeptCalc2": "SAL", "DE:Name1": "RP Winter Wonderland Sleepover PDF for Registered Guests", "DE:CreativeFilePath": "M:\\CREATIVE\\SAL\\151211-PDFLayout-Sleepovers-110203-CM\\", "DE:SubCodeCalc1": "Sleepovers - Sleepovers ///FAMILY ADVENTURES ////EARNED REVENUE", "DE:Name2": "Winter Wonderland Sleepover PDF for Registered Guests" } }, { "ID": "56421f7800549215c74830eeadc7d419", "name": "RP: DMN Arts & Life 1/31 (COL Now Open)", '______________________________________________________________________ Maybe I just need to know how to grab the fields that are within the parameterValues set? I have tried: rs![Element Format] = Thing.Item("parameterValues:DE:Element Format") rs![Element Format] = Thing.Item("DE:Element Format") rs![Element Format] = Thing.Item("parameterValues!DE:Element Format") Thank you in advance! I wish there was somewhere I could learn about the Thing.Item parameter!

[@yvettethompson]

+Yvette Thompson UPDATE: I had to assign it to an Object ( I don't know why it would not accept the Variant Dim) and then it worked: Set jsonParsedObj = Thing.Item("parameterValues") But how would I know that "keys" was a part of that? (jsonParsedObj.keys gave the variable names) Is there a book somewhere where I can look these things up? Does every Object have a "KEY"?

[@ProgrammingMadeEZ]

+Yvette Thompson I'm honestly not entirely understanding the problem. I think there are too many variables in your situation for me to fully grasp what you're doing and what your data is doing.

[@yvettethompson]

+Programming - Thank you for responding and I apologize for the "data dump". My problem in this comment was similar to my first problem I asked you a month ago. My API export comes with an Object Label at the beginning (Data) whereas your code in this video did not. A month ago the only way I could get your code to run was to remove that first "Data" label which I did with the Right() function on the Reader.ResponseText prior to running the Parse Code . I ask my API source for fields=parameterValues, resolvables. Resolvables is a nested collection and I could grab it. The parameterValues was not a collection, more like an array of values, and I had extreme difficulty finding anything to grab the data from it. Finally I set the parameterValues as an Object, and then I could extract its values using its "keys". But it was basically a blind pig finding an acorn as I was lucky to locate someone else's code that used that "key" information, or I would still be struggling to parse the fields from parameterValues. Initially, I tried to remove the "parameterValues {" label from the Reader.ResponseText before I used your Parse Code, (I tried to use the Replace() function but failed because I couldn't remove the parameterValue's closing french bracket "}" from the text). Anyway, I did solve the problem by learning to grab an array of values when you don't know the field (key) names. I appreciate your getting back to me and wish I knew more so I could communicate better. THANK YOU!

[@snoble1978]

Ignore :) sorry i was on a secured connection that was blocking my access. I am good now.