[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:
- Is this even possible inside Access?
- 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.
1 Answer 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.