Trying to integrate an HTTP GET request in my MS-Access database program

First of all try to make a request to API using basic authorization. Take a look at the below code as the example:

Sub Test()

    ' API URL from https://anedot.com/api/v2
    sUrl = "https://api.anedot.com/v2/accounts"
    ' The username is the registered email address of your Anedot account
    sUsername = "[email protected]"
    ' The password is your API token
    sPassword = "1e56752e8531647d09ec8ab20c311ba928e54788"
    sAuth = TextBase64Encode(sUsername & ":" & sPassword, "us-ascii") ' bXltYWlsQGV4YW1wbGUuY29tOjFlNTY3NTJlODUzMTY0N2QwOWVjOGFiMjBjMzExYmE5MjhlNTQ3ODg=
    ' Make the request
    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", sUrl, False
        .SetRequestHeader "Authorization", "Basic " & sAuth
        .Send
        Debug.Print .ResponseText
        Debug.Print .GetAllResponseHeaders
    End With

End Sub

Function TextBase64Encode(sText, sCharset) ' 05 10 2016
    Dim aBinary
    With CreateObject("ADODB.Stream")
        .Type = 2 ' adTypeText
        .Open
        .Charset = sCharset ' "us-ascii" for bytes to unicode
        .WriteText sText
        .Position = 0
        .Type = 1 ' adTypeBinary
        aBinary = .Read
        .Close
    End With
    With CreateObject("Microsoft.XMLDOM").CreateElement("objNode")
        .DataType = "bin.base64"
        .NodeTypedValue = aBinary
        TextBase64Encode = Replace(Replace(.Text, vbCr, ""), vbLf, "")
    End With
End Function

Put your credentials to sUsername and sPassword variables, choose the appropriate URL from API help page and put it to sURL. Then you can parse JSON response from the server (currently you will see the response for /v2/accounts request in Immediate window).

It's a fairly lengthy question to be honest, but lets start with some code to get you going.

This Class Module ("clsXMLHttpMonitor") should help:

Option Explicit


Dim XMLHttpReq As MSXML2.ServerXMLHTTP

Dim RequestedVar As String
Dim P As Object

Public Sub Initialize(ByVal uXMLHttpRequest As Object, Optional RequestedValue As String = "")
   RequestedVar = RequestedValue
   Set XMLHttpReq = uXMLHttpRequest
End Sub

Sub ReadyStateChangeHandler()
    If XMLHttpReq.ReadyState = 4 Then
        If XMLHttpReq.Status = 200 Then
            'Process the response here
            Debug.Print "200 recieved"
            Set P = JSON.parse(XMLHttpReq.responseText)
        Else
            If XMLHttpReq.Status = 404 Then
                'Handle it
            End If
        End If
    End If

End Sub

Function returnResponseHeaders() As String
returnResponseHeaders = XMLHttpReq.getAllResponseHeaders
XMLHttpReq.Send
End Function

Function returnFullText() As String
If XMLHttpReq.ReadyState = 4 Then
    If XMLHttpReq.Status = 200 Then
        returnFullText = XMLHttpReq.responseText
    Else
        returnFullText = "-1"
    End If
Else
    returnFullText = ""
End If
End Function

End Function

Use it like this:

Set XMLHttpReq = New MSXML2.ServerXMLHTTP
Set XMLHttpMon = New clsXMLHttpMonitor
XMLHttpMon.Initialize XMLHttpReq
XMLHttpReq.OnReadyStateChange = XMLHttpMon
XMLHttpReq.Open "POST", URL, True
XMLHttpReq.Send strPayload

As you seem to request a Json response from a URL, you can study the Json modules here for a full implementation that collects the Json response in a collection, which you then can use in your code or save to a table. See the demo module for examples:

VBA.CVRAPI