Sorry I'm just trying to consolidate my learning Json here, I'm trying to capture Json from a website and store it in Ms access database table , but I keep on getting an error that collection is empty , but whenever I hover around the VBA code I see the details there , any Idea where I'm going wrong?
Code:
Option Compare Database
Option Explicit
Private Sub CmdJson_Click()
Dim http As Object, JSON As Object, i As Integer
Dim item As Variant
Dim Z As Integer
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set http = CreateObject("MSXML2.XMLHTTP")
http.Open "GET", "http://jsonplaceholder.typicode.com/users", False
http.Send
Set rs = db.OpenRecordset("Contact", dbOpenDynaset, dbSeeChanges)
Set JSON = ParseJson(http.responseText)
i = 2
For Each item In JSON
'Process data.
Z = 1
rs.AddNew
rs("Id") = item("id")
rs("Name") = item("name")
rs("UserName") = item("username")
rs("Email") = item("email")
rs("Address") = item("address")("city")
rs("phone") = item("phone")
rs("WebSite") = item("website")
rs("company") = item("company")("name")
rs.Update
Z = Z + 1
Next
MsgBox ("complete")
rs.Close
Set rs = Nothing
Set db = Nothing
Set JSON = Nothing
Set item = Nothing
End Sub