Consuming an API to retrieve data and store it in a Microsoft Access table.

[lbcurrier]

I have created some code, with some help from ChatGPT, to consume an API to retrieve data and store it in a Microsoft Access table. It does not give me any errors, but it only adds one blank row into the table. I'm only trying to add one field called link to cut down on troubleshooting. Any pointers would be greatly appreciated.

Sub ConsumeAPIAndInsertData()
Dim url As String
Dim jsonData As String
Dim httpRequest As Object
Dim response As String
Dim jsonResponse As Object
Dim db As DAO.Database
Dim rs As DAO.Recordset

' Set the URL for the API endpoint
url = "HIDDENTOFORPROTECTION"

' Create the JSON data to send
jsonData = "{""token"": ""HIDDENTOFORPROTECTION"", ""pretty"": true, ""form_id"": 555555}"

' Create a new XMLHTTP object
Set httpRequest = CreateObject("MSXML2.XMLHTTP.6.0")

' Open the request
httpRequest.Open "POST", url, False
httpRequest.setRequestHeader "Content-Type", "application/json"

' Send the request with JSON data
httpRequest.send jsonData

' Get the response text
response = httpRequest.responseText

' Debug: Print the raw response
Debug.Print response

' Parse the JSON response
Set jsonResponse = JsonConverter.ParseJson(response)

' Check if jsonResponse is a dictionary/object
If TypeName(jsonResponse) = "Dictionary" Then
' Debug: Check the structure of the parsed JSON
Dim key As Variant
For Each key In jsonResponse.Keys
'Debug.Print key & ": " & jsonResponse(key)
Next key

' Open the database and table
Set db = CurrentDb
Set rs = db.OpenRecordset("Playground", dbOpenDynaset)

' Add a new record to the Playground table
rs.AddNew

' Check if "link" exists and handle the value
If Not IsNull(jsonResponse("link")) Then
rs!link = jsonResponse("link")
Else
rs!link = "" ' Set to empty if link is null
End If

' Update the record
rs.Update

' Clean up
rs.Close
Set rs = Nothing
Set db = Nothing
Else
Debug.Print "Response is not a dictionary."
End If

Set jsonResponse = Nothing
Set httpRequest = Nothing

MsgBox "Data inserted successfully!"
End Sub

 

[Gasman]

Have you walked the code?

 

[CJ_London]

Please use the code tags (the </> button) around your code to preserve indentation

 

[lbcurrier]

Have you walked the code?

To be honest I know about 1/2 an inch of depth to writing code but am scrappy in finding code and figuring out how to modify it. I am way over my head in this effort. Looking into walking the code. In the meantime, I think that there is something wrong with json pulling the data. I have tested it and downloaded the dataset and know that it is there. Thank you for your response, I appreciate it.


I just ran the url through the json parser online and it read the data but comes back with a message that it has invalid syntax. Contacting the site to get their help.