尝试在我的MS-Access数据库程序中集成HTTP GET请求

问题描述：

我想使用来自MS Access程序的HTTP GET请求从信用卡处理公司Anedot导入数据。 Anedot使用RESTful API并在该网站上提供了帮助： https://anedot.com/api/v2

I want to import data from Anedot, a credit card processing firm, using a HTTP GET request from an MS Access program. Anedot uses a RESTful API and has provided help on there website: https://anedot.com/api/v2

我想用VBA执行此操作，并将导入与MS Access表单上的按钮相关联。我已经读过，这只能通过XML实现。我是否使用VBA创建XML文件？

I want to do this with VBA, and associate the import with a button on an MS Access form. I've read that this only possible with XML. Do I create the XML file with VBA?

我非常感谢有关如何完成此操作的一些背景信息，因为大部分内容都在我的脑海中浮现。我真的不知道从哪里开始，我在google上找不到任何有用的东西。

I'd greatly appreciate some background information on how to get this done, as most of it is flying over my head. I don't really know where to begin and I'm having trouble finding anything useful on google.

到目前为止，我已经意识到我需要引用他们的API通过URL链接（他们提供），我将不得不使用我的用户名和令牌ID授权我的帐户。但是我怎么能在VBA中做到这一点？

So far I've realized I'll need to reference their API via a URL link (which they provide), and that I'll have to authorize my account using my username and a token ID. But how can I do this in VBA?

谢谢。

答

首先尝试使用基本授权向API发出请求。以下面的代码为例：

First of all try to make a request to API using basic authorization. Take a look at the below code as the example:

Sub Test()

    ' API URL from https://anedot.com/api/v2
    sUrl = "https://api.anedot.com/v2/accounts"
    ' The username is the registered email address of your Anedot account
    sUsername = "mymail@example.com"
    ' The password is your API token
    sPassword = "1e56752e8531647d09ec8ab20c311ba928e54788"
    sAuth = TextBase64Encode(sUsername & ":" & sPassword, "us-ascii") ' bXltYWlsQGV4YW1wbGUuY29tOjFlNTY3NTJlODUzMTY0N2QwOWVjOGFiMjBjMzExYmE5MjhlNTQ3ODg=
    ' Make the request
    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", sUrl, False
        .SetRequestHeader "Authorization", "Basic " & sAuth
        .Send
        Debug.Print .ResponseText
        Debug.Print .GetAllResponseHeaders
    End With

End Sub

Function TextBase64Encode(sText, sCharset) ' 05 10 2016
    Dim aBinary
    With CreateObject("ADODB.Stream")
        .Type = 2 ' adTypeText
        .Open
        .Charset = sCharset ' "us-ascii" for bytes to unicode
        .WriteText sText
        .Position = 0
        .Type = 1 ' adTypeBinary
        aBinary = .Read
        .Close
    End With
    With CreateObject("Microsoft.XMLDOM").CreateElement("objNode")
        .DataType = "bin.base64"
        .NodeTypedValue = aBinary
        TextBase64Encode = Replace(Replace(.Text, vbCr, ""), vbLf, "")
    End With
End Function

将您的凭据存入 sUsername 和 sPassword 变量，选择来自 API帮助页面并将其放入 sURL 。然后你可以解析服务器的JSON响应（目前你会在立即窗口中看到 / v2 / accounts 请求的响应）。

Put your credentials to sUsername and sPassword variables, choose the appropriate URL from API help page and put it to sURL. Then you can parse JSON response from the server (currently you will see the response for /v2/accounts request in Immediate window).

尝试在我的MS-Access数据库程序中集成HTTP GET请求

相关推荐