创建与基于REST的API的Microsoft Access数据库连接 [英] Creating a Microsoft Access database connection to a REST-based API

问题描述

我正在尝试在Microsoft Access 2013中创建到基于REST的API提供的数据的实时链接(此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.

这如何完成?具体来说，我正在努力使Access能够根据请求调用API.我想获得类似结果的唯一方法是编写一个脚本，该脚本通过API拉取整个数据库并将其转换为Access可读格式，然后以设置的间隔运行该脚本.但是我真的很想找到一个实时运行的解决方案，即使它比本地缓存数据库要慢一些.

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.

推荐答案

由于对RESTful Web服务的调用实际上只是一种特定的HTTP请求，因此您至少可以使用Microsoft XML库来拍摄对Web服务的HTTP请求，并解析返回的任何内容.例如，当我运行以下VBA代码

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

字符串变量response包含对我的请求的XML响应.看起来像这样(在重新格式化以提高可读性之后):

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>[e-mail]</email>
    <email>[e-mail]</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>

您的Web服务返回的内容可能看起来有些不同.或者，就像上面RWS是ARIN的情况一样，您可能有几种数据格式可供选择； XML只是默认设置.我本可以使用

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

在这种情况下，response将包含

#
# 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:          [phone] (Office)
Email:          [e-mail]
Email:          [e-mail]
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
#

这篇关于创建与基于REST的API的Microsoft Access数据库连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！