Link to home
Start Free TrialLog in
Avatar of Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.ScFlag for Zambia

asked on

High level Json/VBA Array Access in Ms Access

How to access data in Json arrays in Ms Access, below is an example of a pure json with arrays, I have no problem when dealing with pure object value pairs:
Current code:
{
    "age": 45,
    "cars": [ {
        "model": "Audi A1", 
        "mpg": 15.1
    },
    {
        "model": "Zeep Compass", 
        "mpg": 18.1
    }
    ],
    "children":[ "Alice",
		  "Bob"
	],
    "married": true,
    "name": "Ken",
    "pets": [ 
		"Dog"
	]
}

Open in new window

How can I access “Alice”, “Bob” and “Ken”

For the current code I’m able to access or add all items to my table by doing something like below:
With rs
            .AddNew
            ![id] = item("id")
            ![firstName] = item("name")
            ![UserName] = item("username")
            ![email] = item("email")
            ![street] = item("address")("street")
            ![suite] = item("address")("suite")
            ![city] = item("address")("city")
            ![zipcode] = item("address")("zipcode")
            ![lat] = item("address")("geo")("lat")
            ![lng] = item("address")("geo")("lng")
            ![Phone] = item("phone")
            ![WebSite] = item("website")
            ![Company] = item("company")("name")
            ![catchPhrase] = item("company")("catchPhrase")
            ![bs] = item("company")("bs")
            .Update
        End With

Open in new window


Source Data:

{
    "id": 1,
    "name": "Leanne Graham",
    "username": "Bret",
    "email": "Sincere@april.biz",
    "address": {
      "street": "Kulas Light",
      "suite": "Apt. 556",
      "city": "Gwenborough",
      "zipcode": "92998-3874",
      "geo": {
        "lat": "-37.3159",
        "lng": "81.1496"
      }
    },
    "phone": "1-770-736-8031 x56442",
    "website": "hildegard.org",
    "company": {
      "name": "Romaguera-Crona",
      "catchPhrase": "Multi-layered client-server neural-net",
      "bs": "harness real-time e-markets"
    }
  },

Open in new window


My problem comes when arrays are involved , how is it done???????
Kindly regards

Chris
Avatar of Daniel Pineault
Daniel Pineault

Take a look at the VBA-JSON library
ASKER CERTIFIED SOLUTION
Avatar of als315
als315
Flag of Russian Federation image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial