使用JSOn获取MS Access中的节点值

时间:2022-09-28 15:42:29

I am using VB-JSON parser to get data from an API and saving the data in MS access table.

我正在使用VB-JSON解析器从API获取数据并将数据保存在MS访问表中。

I am not able to figure out how to access the -KLj9kXnKd-9txfyIqM8 and -KLjJoT7gXCMq_jHx2_z.

我无法弄清楚如何访问-KLj9kXnKd-9txfyIqM8和-KLjJoT7gXCMq_jHx2_z。

I have Table structure as below, and want to save the data as shown below.

我有如下表结构,并希望保存数据,如下所示。

|ServerID               |Name       |Mobile
|-KLj9kXnKd-9txfyIqM8   |Adarsh     |9987
|-KLjJoT7gXCMq_jHx2_z   |Manas      |022

JSON

JSON

{
  "-KLj9kXnKd-9txfyIqM8": {
    "personmobile": "9987",
    "personname": "Adarsh"
  },
  "-KLjJoT7gXCMq_jHx2_z": {
    "personmobile": "022",
    "personname": "Manas"
  }
}

VBA

VBA

Public Sub GetPerson()
'I have code here which gets the json as above from api.     
Dim egTran As String    

If reader.Status = 200 Then
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblPerson", dbOpenDynaset, dbSeeChanges)

    egTran = "[" & reader.responseText & "]"
    Set coll = Json.parse(egTran)

    For Each contact In coll
        rs.AddNew
        rs!Name = contact.Item("personname")
        rs!Mobile = contact.Item("personmobile")
        rs!ServerID  = contact.Item("??????")  

what do I write in ??????

我该怎么写??????

        rs.Update
    Next
End If
End Sub

I am also open to using any other parser. The API is based on Firebase Database

我也愿意使用任何其他解析器。该API基于Firebase数据库

1 个解决方案

#1


2  

I'm not familiar with VB-JSON, but obviously "??????" is not an item of contact.

我不熟悉VB-JSON,但很明显“??????”不是联系项目。

Thus, if I run this test function:

因此,如果我运行此测试功能:

Public Sub TestJsonText()

    Dim DataCollection      As Collection
    Dim ResponseText        As String

    ResponseText = _
        "{" & _
        "  ""-KLj9kXnKd-9txfyIqM8"": {" & _
        "  ""personmobile"": ""9987""," & _
        "  ""personname"": ""Adarsh""" & _
        "  }," & _
        "  ""-KLjJoT7gXCMq_jHx2_z"": {" & _
        "  ""personmobile"": ""022""," & _
        "  ""personname"": ""Manas""" & _
        "  }" & _
        "}"

    If ResponseText <> "" Then
        Set DataCollection = CollectJson(ResponseText)
        MsgBox "Retrieved" & Str(DataCollection.Count) & " root member(s)", vbInformation + vbOKOnly, "Web Service Success"
    End If

    Call ListFieldNames(DataCollection)

    Set DataCollection = Nothing

End Sub

using the Json modules from VBA.CVRAPI it will print:

使用VBA.CVRAPI中的Json模块,它将打印:

root                        
    -KLj9kXnKd-9txfy        
        personmobile        9987
        personname          Adarsh
    -KLjJoT7gXCMq_jH        
        personmobile        022
        personname          Manas

From the function ListFieldNames you can pick MemberName for the field name and DataCollection(Index)(CollectionItem.Data) for the field value to add records.

从函数ListFieldNames中,您可以为字段名称选择MemberName,为字段值选择DataCollection(Index)(CollectionItem.Data)以添加记录。

#1


2  

I'm not familiar with VB-JSON, but obviously "??????" is not an item of contact.

我不熟悉VB-JSON,但很明显“??????”不是联系项目。

Thus, if I run this test function:

因此,如果我运行此测试功能:

Public Sub TestJsonText()

    Dim DataCollection      As Collection
    Dim ResponseText        As String

    ResponseText = _
        "{" & _
        "  ""-KLj9kXnKd-9txfyIqM8"": {" & _
        "  ""personmobile"": ""9987""," & _
        "  ""personname"": ""Adarsh""" & _
        "  }," & _
        "  ""-KLjJoT7gXCMq_jHx2_z"": {" & _
        "  ""personmobile"": ""022""," & _
        "  ""personname"": ""Manas""" & _
        "  }" & _
        "}"

    If ResponseText <> "" Then
        Set DataCollection = CollectJson(ResponseText)
        MsgBox "Retrieved" & Str(DataCollection.Count) & " root member(s)", vbInformation + vbOKOnly, "Web Service Success"
    End If

    Call ListFieldNames(DataCollection)

    Set DataCollection = Nothing

End Sub

using the Json modules from VBA.CVRAPI it will print:

使用VBA.CVRAPI中的Json模块,它将打印:

root                        
    -KLj9kXnKd-9txfy        
        personmobile        9987
        personname          Adarsh
    -KLjJoT7gXCMq_jH        
        personmobile        022
        personname          Manas

From the function ListFieldNames you can pick MemberName for the field name and DataCollection(Index)(CollectionItem.Data) for the field value to add records.

从函数ListFieldNames中,您可以为字段名称选择MemberName,为字段值选择DataCollection(Index)(CollectionItem.Data)以添加记录。