Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Creating a Microsoft Access database connection to a REST-based API

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.

like image 764
Antonio Avatar asked Sep 24 '15 18:09

Antonio


People also ask

Does REST API use 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.

How do you create a web based database in Access?

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.

How do I connect to Microsoft Access database?

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.


1 Answers

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&#39;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
#
like image 100
Gord Thompson Avatar answered Sep 27 '22 17:09

Gord Thompson