0

[Apologies in advance if this has been asked an answered elsewhere.]

I have recently dived into Microsoft Access 2016 to handle a relatively small (but still too big for Excel) database with somewhat sensitive (read should not live on the internet) information. So far so good.

What I would like to do is be able to pull down information from a publicly available API to update a few columns of a table.

Ex: For each entry in this table take field A and submit it to the web API. The API would then return a list of numbers which I then parse and plug into fields B, C, and D.

QUESTIONS:

  1. Is this even possible inside Access?
  2. Where should I look/what should I search for to figure out how to do it?

I would rather not have to pipe it through Excel or some third party service if possible. I am comfortable programming and don't mind picking up a new language but I can't seem to figure out where to start. Any help from "hit the _____ button." to "Searching for _____ will get you started." would be appreciated.

Improve this question
1

The most straightforward way to have Access VBA interact with a web service would be to use a MSXML2.ServerXMLHTTP object like this:

Option Compare Database
Option Explicit

Sub WebRequestExample()
    Dim httpReq As Object, response As String
    Set httpReq = CreateObject("MSXML2.ServerXMLHTTP")
    httpReq.Open "GET", "http://checkip.dyndns.com", False
    httpReq.Send
    response = httpReq.responseText
    Set httpReq = Nothing
    Debug.Print response
End Sub

In this case the response string contains

<html><head><title>Current IP Check</title></head><body>Current IP Address: 192.168.53.49</body></html>

If the web service returns a relatively short and straightforward response then you could just parse it yourself.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.