JSON/Web Scrapping woes...

[NauticalGent]

Good morning everyone,

Using the code in this video, I thought "wow, looks pretty easy, let me give a try with something I have been wanting to do!"

Here is my modified code:

Public Sub ReadJSON(strCAGE As String) ' the CAGE I am passing is "00060"
    
    Dim Reader As New xmlhttp60           'Refrence to Microsoft XML, V6.0 required
    Dim strURL As String
    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    strURL = https://cage.dla.mil/Search/Results?q= & strCAGE & "&page=1"     '00060
    Reader.Open "get", strURL, False
    Reader.send

    Do Until Reader.ReadyState = 4
        DoEvents
    Loop

If Reader.status = 200 Then
        Set db = CurrentDb
        Set rs = db.OpenRecordset("tblCAGE", dbOpenDynaset, dbSeeChanges)
        With rs
            .AddNew
            rs!CAGE = strCAGE
            rs!Description = Reader.responseText
            .Update
            DoEvents
        End With
    Else
        Dim intError As Integer
        intError = Reader.status
        MsgBox "Connection not made. Error Code: " & intError
    End If

End Sub

It works great but I am not getting the actual results. I am sure that it is because if you use the site interactively, you have to accept cookies and click some "A agree" buttons but and am not sure and if I AM right, is there a way around this?

 

[GPGeorge]

You might get some additional help from these two recent videos on similar topics. Using Rest/APIs in Access by Juanjo Luna with English translation by George Young. Using Web APIs in Access by George Young (the same GY).

I just completed my first Web API call in Access, using the GitHub resources Juanjo and George Y. identify. I suspect it's a lot more complex, but that comes with increased flexibility. For example, it took multiple tries, but I eventually managed to figure out how to pass the correct token to the Web API which required a developer key to respond. None of the three videos do that; they all use anonymous calls. However, once you've fooled around and found out it's like magic.

 

[MsAccessNL]

You have to make a distinction between a rest api request. The provider has to have information about the api and mostly you need the have an api key. On the other hand you have the option to use webscraping. For webscraping you have to inspect the webpage and learn about the xml dom document to get the requested information from the site. Like with the GetElementByID(...). I will take a look at the website and see if i can be of any help.

 

[NauticalGent]

Thanks, I have looked at some Wise Owl videos that echoed what you said but figured I would try this because it looked like the "easy" button!

 

[MsAccessNL]

The page https://cage.dla.mil/Search/Details?id=70

Gives quite some information, are you looking for specific info from this site?A quicker option is to let Access fill the inputfield with your request, download the whole page with msxml into your database and show it in your webbrowser control.

I think your code will work When you replace your url with https://cage.dla.mil/Search/Details?id=70 (best to set your description field to rich text). Set a webcontrol on a form and use:
WebControl.Document

With Me.WebControl.Object.Document
.Open
.Write rs!Description
.Close
End With

I mostly us msxlm2 code something like;
Dim XMLReq As New MSXML2.XMLHTTP60
Dim HTMLDoc As New MSHTML.HTMLDocument

XMLReq.Open "Get", sUrl, False
XMLReq.Send

If XMLReq.Status = 200 Then
HTMLDoc.Body.innerHTML = XMLReq.responseText