创建到基于 REST 的 API 的 Microsoft Access 数据库连接

本文介绍了创建到基于 REST 的 API 的 Microsoft Access 数据库连接的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我正在尝试在 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>[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>

您的网络服务返回的内容可能看起来有些不同.或者，在上面的 ARIN whois RWS 的情况下，您可能有多种数据格式可供选择；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 将包含

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
#

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