Ms-access – Pulling data into Access from an external API

ms access

[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.

Best Answer

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.

Related Solutions

Ms-access – Merging two Access tables into one

Create a new table that have all the columne in all your 5 tables, then create the needed SQL statment like SELECT * FROM Tb1 INTO TableWithAllColumns eventually add some WHERE to exclude the duplicate rows from the insert.

This will work if your table doesent have similar rows. If you need to merge similar rows together, there is no a simple way to tho this. The best you can do is find, or write, a tool that can compare all the value in all the columns and act in the right way.

MS Access Data Import – Handling Specific Time Format

As you have discovered, the Date/Time column type in Access does not support fractional seconds. If you need to preserve the precision (and not round to the nearest second) then you'll need to import the time values as Text and then populate another Long Integer column with the times converted to tenths-of-milliseconds (which I have abbreviated to "tms"):

After the initial import of the times as Text you will have something like this

ID  hms_time       tms_time 
--  -------------  ---------
 1  00:00:00.0001
 2  23:59:59.9999
 3  00:00:00.0000
 4  03:25:45.6789
 5  22:57:40.0000

If you create a VBA function in an Access Module like this

Option Compare Database
Option Explicit

Public Function Hms2Tms(hms As Variant) As Variant
    ' convert "hh:mm:ss.dddd" String
    '     to Long Integer tenths-of-milliseconds (0..863999999)
    Dim arr() As String, rtn As Long

    If IsNull(hms) Then
        Hms2Tms = Null
    Else
        arr = Split(hms, ":", -1, vbBinaryCompare)
        rtn = CLng(arr(0)) * 36000000 + CLng(arr(1)) * 600000
        arr = Split(arr(2), ".", 2, vbBinaryCompare)
        rtn = rtn + CLng(arr(0)) * 10000 + CLng(arr(1))
        Hms2Tms = rtn
    End If
End Function

then you can use the query

UPDATE TimesTable SET tms_time = Hms2Tms([hms_time])

to calculate the numeric equivalents

ID  hms_time       tms_time 
--  -------------  ---------
 1  00:00:00.0001          1
 2  23:59:59.9999  863999999
 3  00:00:00.0000          0
 4  03:25:45.6789  123456789
 5  22:57:40.0000  826600000

You can also use the following VBA function to convert a numeric "tms" time into an "hms" string:

Public Function Tms2Hms(tms As Variant) As Variant
    ' convert Long Integer tenths-of-milliseconds (0..863999999)
    '     to "hh:mm:ss.dddd" String
    Dim hh As Long, mm As Long, ss_dddd As Currency, remainder As Long, rtn As String

    If IsNull(tms) Then
        Tms2Hms = Null
    Else
        hh = Int(CCur(tms) / 36000000)
        rtn = Format(hh, "00")
        remainder = tms - hh * 36000000
        mm = Int(CCur(remainder) / 600000)
        rtn = rtn & ":" & Format(mm, "00")
        remainder = remainder - mm * 600000
        ss_dddd = CCur(remainder) / 10000
        rtn = rtn & ":" & Format(ss_dddd, "00.0000")
        Tms2Hms = rtn
    End If
End Function