I am trying to, in Microsoft Access 2013, create a real-time link to data provided by a REST-based API (this API, to be specific). The ultimate goal is for the data to be available in a query as if it were a local database.
How can this be accomplished? Specifically, I am struggling with how to have Access call the API upon request. The only way I can think to achieve a similar result is to write a script that pulls the entire database via the API and translates it to an Access-readable format, then run that script at set intervals. But I'd really like to find a solution that works in real time, even if it's a skosh slower than locally caching the database.
REST means representational state transfer, and it's an architecture used to design client-server applications. With a Rest API, you're getting a representation of the requested data stored in a database.
Open Access, and select Custom web app. Enter a name for your new web app, and select its location. Then, select Create. Add structured or blank tables to the web app, or create tables from existing data sources.
On the Tools menu, click Data Connections. In the Data Connections dialog box, click Add. In the Data Connection Wizard, click Create a new connection to, click Receive data, and then click Next. On the next page of the wizard, click Database (Microsoft SQL Server or Microsoft Office Access only), and then click Next.
Since a call to a RESTful Web Service is really just a specific kind of HTTP request you could, at the very least, use the Microsoft XML library to shoot an HTTP request to the web service and parse whatever it returns. For example, when I run the following VBA code
' VBA project Reference required:
' Microsoft XML, v3.0
Dim httpReq As New MSXML2.ServerXMLHTTP
httpReq.Open "GET", "http://whois.arin.net/rest/poc/KOSTE-ARIN", False
httpReq.send
Dim response As String
response = httpReq.responseText
Debug.Print response
the string variable response
contains the XML response to my request. It looks like this (after reformatting for readability):
<?xml version='1.0'?>
<?xml-stylesheet type='text/xsl' href='http://whois.arin.net/xsl/website.xsl' ?>
<poc xmlns="http://www.arin.net/whoisrws/core/v1" xmlns:ns2="http://www.arin.net/whoisrws/rdns/v1"
xmlns:ns3="http://www.arin.net/whoisrws/netref/v2" termsOfUse="https://www.arin.net/whois_tou.html"
inaccuracyReportUrl="http://www.arin.net/public/whoisinaccuracy/index.xhtml">
<registrationDate>2009-10-02T11:54:45-04:00</registrationDate>
<ref>http://whois.arin.net/rest/poc/KOSTE-ARIN</ref>
<city>Chantilly</city>
<companyName>ARIN</companyName>
<iso3166-1>
<code2>US</code2>
<code3>USA</code3>
<name>UNITED STATES</name>
<e164>1</e164>
</iso3166-1>
<firstName>Mark</firstName>
<handle>KOSTE-ARIN</handle>
<lastName>Kosters</lastName>
<emails>
<email>[email protected]</email>
<email>[email protected]</email>
</emails>
<resources termsOfUse="https://www.arin.net/whois_tou.html"
inaccuracyReportUrl="http://www.arin.net/public/whoisinaccuracy/index.xhtml">
<limitExceeded limit="256">false</limitExceeded>
</resources>
<phones>
<phone>
<number>+ 1-703-227-9870</number>
<type>
<description>Office</description>
<code>O</code>
</type>
</phone>
</phones>
<postalCode>20151</postalCode>
<comment>
<line number="0">I'm really MAK21-ARIN</line>
</comment>
<iso3166-2>VA</iso3166-2>
<streetAddress>
<line number="0">3635 Concorde Parkway</line>
</streetAddress>
<updateDate>2015-05-26T11:36:55-04:00</updateDate>
</poc>
What gets returned by your web service might look somewhat different. Or, as in the case of the ARIN whois RWS above, you may have several data formats from which to choose; XML was just the default. I could have requested a plain text response using
httpReq.Open "GET", "http://whois.arin.net/rest/poc/KOSTE-ARIN.txt", False
in which case response
would contain
#
# ARIN WHOIS data and services are subject to the Terms of Use
# available at: https://www.arin.net/whois_tou.html
#
Name: Kosters, Mark
Handle: KOSTE-ARIN
Company: ARIN
Address: 3635 Concorde Parkway
City: Chantilly
StateProv: VA
PostalCode: 20151
Country: US
RegDate: 2009-10-02
Updated: 2015-05-26
Comment: I'm really MAK21-ARIN
Phone: +1-703-227-9870 (Office)
Email: [email protected]
Email: [email protected]
Ref: http://whois.arin.net/rest/poc/KOSTE-ARIN
#
# ARIN WHOIS data and services are subject to the Terms of Use
# available at: https://www.arin.net/whois_tou.html
#
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With