Link to home
Start Free TrialLog in
Avatar of Alexis Valdez
Alexis Valdez

asked on

VBA for CRUD in consuming an API (Web Service)

MS Access VBA modules to Create, Read, Update, Delete (CRUD) using API (Web Service) via odata or SOAP protocol. This project is particularly consuming an MSD (Microsoft Dynamics) APIs.
Avatar of ste5an
ste5an
Flag of Germany image

CRUD is a term related to ORM's. Access as RAD does not support OO very well.
The term " API (Web Service)" makes no sense. Cause everything can be an API.

So you may rephrase your question, please.

But: Consuming a REST or SOAP service is better done in .Net.

While you can consume REST and SOAP services by using MSXML and XMLHTTP60, you may stumble upon:

- A service in the Dynamics environment may have some higher requirements for authentication.
- SOAP can get very fast very complex because of its XML content.
- REST often supports only JSON, there is no native support for that in VBA. And parsing JSON isn't also that easy.

But you can do it.

SOAP:

Option Compare Database
Option Explicit

Public Function ViesCheckVat(ACountryCode As String, AVatNumber As String, ByRef OAddress As String, ByRef OName As String) As Boolean

  Const VIES_SOAP As String = _
    "<s:Envelope xmlns:s=""http://schemas.xmlsoap.org/soap/envelope/"">" & _
    "<s:Body>" & _
    "<checkVat xmlns=""urn:ec.europa.eu:taxud:vies:services:checkVat:types"" xmlns:i=""http://www.w3.org/2001/XMLSchema-instance"">" & _
    "<countryCode>{0}</countryCode>" & _
    "<vatNumber>{1}</vatNumber>" & _
    "</checkVat>" & _
    "</s:Body>" & _
    "</s:Envelope>"
  Const VIES_URL As String = "http://ec.europa.eu/taxation_customs/vies/services/checkVatService"

  On Local Error Resume Next

  Dim WebResponse As MSXML2.DOMDocument60
  Dim WebClient As MSXML2.XMLHTTP60

  ViesCheckVat = False
  Screen.MousePointer = ccHourglass
  Set WebClient = New MSXML2.XMLHTTP60
  WebClient.Open "POST", VIES_URL, False, "", ""
  WebClient.setRequestHeader "Content-Type", "text/xml"
  WebClient.send FormatStr(VIES_SOAP, ACountryCode, AVatNumber)
  If WebClient.Status = 200 Then
    Set WebResponse = New MSXML2.DOMDocument60
    WebResponse.LoadXML WebClient.responseXML.XML
    WebResponse.SetProperty "SelectionNamespaces", _
      "xmlns:s='http://schemas.xmlsoap.org/soap/envelope/' " & _
      "xmlns:v='urn:ec.europa.eu:taxud:vies:services:checkVat:types'"

    ViesCheckVat = (WebResponse.SelectSingleNode("//v:valid").Text = "true")
    If ViesCheckVat Then
      OAddress = WebResponse.SelectSingleNode("//v:address").Text
      OName = WebResponse.SelectSingleNode("//v:name").Text
    End If

    Set WebResponse = Nothing
  Else
    MsgBox "Error while quering VIES ({0}): {1}", WebClient.Status, WebClient.StatusText
  End If

  Set WebClient = Nothing

LocalError:
  Screen.MousePointer = ccDefault

End Function

Open in new window

A call to a REST service looks pretty much the same, but is shorter, cause you don't need to exchange messages. E.g. like here.
This question needs an answer!
Become an EE member today
7 DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.