0

Hello everyone I hope some can help me. I want to parse a json text in VBA and store it in an MS Access table and it almost work.

I use a libraries the GitHub one to parse but only problem when I want to import the data to my table I get a data type conversion error. I don't know how to fix it. Below is my code:

Public Sub exportCCProductidInfo()
    Dim coll As Object
    'Dim json As New ClsJsonParser
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim reader As String
    Dim ccproductid As Variant
    
   
    Set db = CurrentDb
    Set rs = db.OpenRecordset("CcProductid_details", dbOpenDynaset, dbSeeChanges)
        
    Set coll = JsonConverter.ParseJson(getTerminalsByCcproduits())
        
    For Each ccproductid In coll
        rs.AddNew
        rs!cc_product_id = ccproductid("cc_product_id") 'this is where i have the problem
        rs!Connected = ccproductid("connected")
        rs!interface = ccproductid("interface")
        rs!registered = ccproductid("registered")
        rs!Type = ccproductid("type")
        rs.Update
    Next
End Sub

getTerminalsByCcproduits() is another function I created and which returns me this result

[
   {
      "cc_product_id":"0195d-2b0d6-1524c-05508-1",
      "connected":"4",
      "interface":"None",
      "registered":"4",
      "type":"Internal Voice Mail Unit"
   }
]

The problem is that instead of having rs!cc_product_id = ccproductid("cc_product_id") I have rs!cc_product_id=5 or else and ccproductid("cc_product_id")= "0195d-2b0d6-1524c-05508-1"

So please, how do I fix this data error?

Parfait
  • 104,375
  • 17
  • 94
  • 125
Ana Kelly
  • 15
  • 4
  • Try type conversion, [`CStr`](https://support.microsoft.com/en-us/office/type-conversion-functions-8ebb0e94-2d43-4975-bb13-87ac8d1a2202): `rs!cc_product_id = CStr(ccproductid("cc_product_id"))`. – Parfait Feb 01 '21 at 15:17
  • I trie CStr conversion but it didn't work still the same that's why i'm lost the fact is that i don't understand why i have an integer value and a string value – Ana Kelly Feb 01 '21 at 15:31
  • Can you [**edit**](https://stackoverflow.com/posts/65994076/edit) your post and provide a fuller JSON with `"cc_product_id": 5`? Please also post the table definition of `CcProductid_details`, specifically the data types of each column of recordset. – Parfait Feb 01 '21 at 16:44
  • I would suggest accessing each of the structures within the JSON as VBA structures, i.e. assigning a JSON entry to a `Dictionary` or a `Collection` and then accessing the next level of the structure. [This answer](https://stackoverflow.com/a/46245469/4717755) details how JSON is mapped into the various different structures and how you can use them. – PeterT Feb 01 '21 at 17:57
  • Thank you very much to you all you where right @PeterSmith cc_product_id in my database was in type Long instead of short i didn't pay attention too that when i created the table as soon as i change the type it worked – Ana Kelly Feb 01 '21 at 18:33

0 Answers0