Parse JSON with Access VBA

Did you read that API's spec carefully? Does it not provide different, additional data exchange formats like XML (good API's do)?

I cant find any reference to xml.  That would be great if there was an xml option, I think I could fumble through that.  This all looks like json to me, but here is the link

https://min-api.cryptocompare.com/

Bad API. Ignores the Accept header.. but the result is a simple serlized object. So just a little bit of string fiddling:

Option Explicit

Public Sub Test()

  Dim Count As Long
  Dim CurrencyCode As String
  Dim ExchangeRates() As String
  Dim Json As String
  Dim CurrencyValue As Double
    
  Json = MockJson
  Json = Replace(Replace(Json, "{", ""), "}", "")
  ExchangeRates() = Split(Json, ",")
  For Count = 0 To UBound(ExchangeRates())
    CurrencyCode = Trim(Replace(Split(ExchangeRates(Count), ":")(0), """", ""))
    CurrencyValue = Split(ExchangeRates(Count), ":")(1)
    Debug.Print CurrencyCode; CurrencyValue
  Next Count

End Sub

Private Function MockJson() As String

  MockJson = MockJson & "{                       "
  MockJson = MockJson & "    ""USD"": 7547.14,   "
  MockJson = MockJson & "    ""JPY"": 840137.28, "
  MockJson = MockJson & "    ""EUR"": 6460.93    "
  MockJson = MockJson & "}                       "

End Function

Select allOpen in new window

Ok, I see how you are stripping the brackets out of the string, but I'm still confused on how to implement this.  I have a button on a form that I want to refresh the current values for the corresponding items in the table.   I have the existing JSON output in the immediate window and to a popup msgbox but I don't understand how to get the response into a table

I also get a variable not defined error on Json = MockJson

You must replace MockJson with your function retrieving the JSON from the web service.. but without code...

Without code?  My form now pulls the info from the web service but without the header row I cant get it into the table the way I had intended to before by looping through the recordset and assigning the values to the corresponding column.

Your code, you haven't posted so far..

Oh! Sorry, wasn't trying to be vague.  I was trying to make use of an existing solution but I cant get it to work so I didn't want to confuse the issue.  Here is what I have:


Public Sub ReadJSON()

Dim reader As New XMLHTTP60
Dim Coll As Collection
Dim Json As New clsJSONParser
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim Crypto As Variant

reader.Open "GET", "https://min-api.cryptocompare.com/data/pricemulti?fsyms=ARK,BTC,BCH,BTG,ADA,DASH,DGB,DGC,ETH,ETC,GNT,KMD,LSK,LTC,XMR,NEO,OMG,RVN,RDD,XRP,SHIFT,SC,STRAT,SYS,WAVES&tsyms=USD", False
reader.SetRequestHeader "Accept", "application/json"
reader.Send

Do Until reader.ReadyState = 4
    DoEvents
Loop
   
If reader.Status = 200 Then

Set db = CurrentDb
Set rs = db.OpenRecordset("tblJSON", dbOpenDynaset, dbSeeChanges)
Set Coll = Json.parse(reader.responseText)



For Each Crypto In Coll
rs.AddNew
rs!Type = Crypto.item("TYPE")
rs!MARKET = Crypto.item("MARKET")
rs!FROMSYMBOL = Crypto.item("FROMSYMBOL")
rs!TOSYMBOL = Crypto.item("TOSYMBOL")
rs!FLAGS = Crypto.item("FLAGS")
rs!PRICE = Crypto.item("PRICE")
rs!LASTUPDATE = Crypto.item("LASTUPDATE")
rs!LASTVOLUME = Crypto.item("LASTVOLUME")
rs!LASTVOLUMETO = Crypto.item("LASTVOLUMETO")
rs!LASTTRADEID = Crypto.item("LASTTRADEID")
rs!VOLUME24HOUR = Crypto.item("VOLUME24HOUR")
rs!VOLUME24HOURTO = Crypto.item("VOLUME24HOURTO")
rs!OPEN24HOUR = Crypto.item("OPEN24HOUR")
rs!HIGH24HOUR = Crypto.item("HIGH24HOUR")
rs!LOW24HOUR = Crypto.item("LOW24HOUR")
rs!LASTMARKET = Crypto.item("LASTMARKET")
rs!CHANGE24HOUR = Crypto.item("CHANGE24HOUR")
rs!CHANGEPCT24HOUR = Crypto.item("CHANGEPCT24HOUR")
rs.Update

Next

Else

MsgBox "unable to import data.", vbOKOnly

End If
End Sub

I'm trying to use the parser in this link following what he did.

https://www.youtube.com/watch?v=5qpUFV5Gtb0&feature=youtu.be

But you're trying hard.. You have already a JSON parser.. What's wrong with it?

Or just apply the Split() approach..

No I'm not.  I stated very clearly in my first post that I am a novice and needed an explanation in laymans terms.  I'm not sure that anything is wrong with the parser I tried, I may not be implementing it correctly.  This was one of several attempts Ive been making on my own to achieve the desired result.

How should i help you or tell you how to use your code, when you don't post it???