Consuming an ASP.NET Webservice from an Access Application using VBA

Question

I am attempting to call and consume an ASP.NET webservice with an MS Access database application using VBA. The webservice itself appears to be operable because I am able to consume it successfully using a separate ASP.NET web application. However, I am having a difficult time trying to get the VBA code within the Access database to return the correct data string from the webservice.

I’ve searched a number of different forums on this topic, but I cannot seem to locate the information that I need in order to get this to work the way that I need it to. That said, I am new to working with webservices in general.

Here is the code that I am using within my MS Access database:

Private Sub Command0_Click()
  InvokeWebService ("http://localhost:51075/WebService1.asmx?HelloWorld")
End Sub

Public Function InvokeWebService(ByVal strUrlCommand As String) As String

  Dim HttpReq As Object
  Dim strWebCode As String
  Dim fOk As Boolean

  ' Routine that calls the web site
  Set HttpReq = CreateObject("MSXML2.XMLHTTP")

  HttpReq.Open "GET", strUrlCommand, False

  On Error Resume Next
    HttpReq.send

    fOk = (Err.Number = 0)

  If fOk Then
    strWebCode = HttpReq.responseText
  Else
    strWebCode = "Err"
  End If

  Set HttpReq = Nothing

  InvokeWebService = strWebCode
End Function

Here is the code that I am using for the webservice:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;

namespace Webservice
{
  /// <summary>
  /// Summary description for WebService1
  /// </summary>
  [WebService(Namespace = "http://tempuri.org/")]
  [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
  [System.ComponentModel.ToolboxItem(false)]
  // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
  // [System.Web.Script.Services.ScriptService]
  public class WebService1 : System.Web.Services.WebService
  {

    [WebMethod]
    public string HelloWorld()
    {
      return "Hello to the World";
    }
  }
}

When I execute the code in my Access database, I’m looking to get the string “Hello to the world” returned to me. However, here is what is actually returned (to the VBA variable “InvokeWebService “) when I run the VBA code. I appreciate any advice on what I am doing incorrectly.

<html>
    <head><link rel="alternate" type="text/xml" href="/WebService1.asmx?disco" />
    <style type="text/css">
    BODY { color: #000000; background-color: white; font-family: Verdana; margin-left: 0px; margin-top: 0px; }
    #content { margin-left: 30px; font-size: .70em; padding-bottom: 2em; }
    A:link { color: #336699; font-weight: bold; text-decoration: underline; }
    A:visited { color: #6699cc; font-weight: bold; text-decoration: underline; }
    A:active { color: #336699; font-weight: bold; text-decoration: underline; }
    A:hover { color: cc3300; font-weight: bold; text-decoration: underline; }
    P { color: #000000; margin-top: 0px; margin-bottom: 12px; font-family: Verdana; }
    pre { background-color: #e5e5cc; padding: 5px; font-family: Courier New; font-size: x-small; margin-top: -5px; border: 1px #f0f0e0 solid; }
    td { color: #000000; font-family: Verdana; font-size: .7em; }
    h2 { font-size: 1.5em; font-weight: bold; margin-top: 25px; margin-bottom: 10px; border-top: 1px solid #003366; margin-left: -15px; color: #003366; }
    h3 { font-size: 1.1em; color: #000000; margin-left: -15px; margin-top: 10px; margin-bottom: 10px; }
    ul { margin-top: 10px; margin-left: 20px; }
    ol { margin-top: 10px; margin-left: 20px; }
    li { margin-top: 10px; color: #000000; }
    font.value { color: darkblue; font: bold; }
    font.key { color: darkgreen; font: bold; }
    font.error { color: darkred; font: bold; }
    .heading1 { color: #ffffff; font-family: Tahoma; font-size: 26px; font-weight: normal; background-color: #003366; margin-top: 0px; margin-bottom: 0px; margin-left: -30px; padding-top: 10px; padding-bottom: 3px; padding-left: 15px; width: 105%; }
    .button { background-color: #dcdcdc; font-family: Verdana; font-size: 1em; border-top: #cccccc 1px solid; border-bottom: #666666 1px solid; border-left: #cccccc 1px solid; border-right: #666666 1px solid; }
    .frmheader { color: #000000; background: #dcdcdc; font-family: Verdana; font-size: .7em; font-weight: normal; border-bottom: 1px solid #dcdcdc; padding-top: 2px; padding-bottom: 2px; }
    .frmtext { font-family: Verdana; font-size: .7em; margin-top: 8px; margin-bottom: 0px; margin-left: 32px; }
    .frmInput { font-family: Verdana; font-size: 1em; }
    .intro { margin-left: -15px; }    
    </style>
    <title>
  WebService1 Web Service
</title></head>
  <body>
    <div id="content">
      <p class="heading1">WebService1</p><br>
      <span>
          <p class="intro">The following operations are supported.  For a formal definition, please review the <a href="WebService1.asmx?WSDL">Service Description</a>. </p>
                        <ul>
                        <li>
                            <a href="WebService1.asmx?op=HelloWorld">HelloWorld</a>
                        </li>
                        <p>
                        </ul>
      </span>
    <span> 
    </span>
  </body>
</html>

Yes, I realize that I could create and reference a .NET DLL in my Access database to get this to work. However, I need to avoid the .NET dependency and would prefer to be free of the issue of having to maintain and distribute the extra DLL file; assuming that I can accomplish this task without that.

If you open a browser to that URL, what do you see? "I am able to consume it successfully using a separate ASP.NET web application" - can you share the client-side code which does this? — Tim Williams, May 13 '20 at 22:45
...or try `http://localhost:51075/WebService1.asmx?op=HelloWorld` — Tim Williams, May 13 '20 at 23:20
When I load the URL "http://localhost:51075/WebService1.asmx" in my browser, the webservice description page appears. So, that part works. The client side code is the first block of code that I posted above (for Microsoft Access). — John Litchfield, May 13 '20 at 23:53
Sorry I meant the working web browser client-side code (assuming it's being called from the browser) — Tim Williams, May 14 '20 at 00:01
If it is the ASP.NET code you are looking to see, here is a copy (in case that helps). This code works. It's the MS Access code (posted at the beginning of this thread) that I cannot get to work. ``` protected void Button1_Click(object sender, EventArgs e) { string strResp; //string strCode; //strCode = "123"; localhost.WebService1 objservice = new localhost.WebService1(); objservice.HelloWorld(); strResp = objservice.HelloWorld(); this.TextBox1.Text = strResp; } ``` Sorry for the formatting issue. — John Litchfield, May 14 '20 at 00:04
That's a little higher-level than I was hoping for. Seems like if you want to know what the request should look like, then trigger that call from the browser and use the Developer Tools to view the request headers: that will give you the URL and any other parameters. — Tim Williams, May 14 '20 at 03:44
is not the url this: http://localhost:51075/WebService1.asmx/HelloWorld. You have a "?". When you hit the web page you should get rather clean xml as the return result. — Albert D. Kallal, May 14 '20 at 04:16
Make sure you search. https://stackoverflow.com/questions/15644254/connect-to-web-service-in-ms-access-with-vba — wazz, May 14 '20 at 09:10
Tim, I’m to find what you’re looking. Is it the soap http output? I can't paste an example since the Comments don't allow that many characters. — John Litchfield, May 14 '20 at 22:01
Albert, if I type “localhost:51075/WebService1.asmx/HelloWorld” in the browser address bar and press Enter, I receive a page error: Server Error in '/' Application. Request format is unrecognized for URL unexpectedly ending in '/HelloWorld'. — John Litchfield, May 14 '20 at 22:02
Wazz, I did see that URL and I tested that code. I got the same convoluted XML response that I pasted in my question. So, what I am trying to get back from the webservice when I call it is “Hello to the world”. Hopefully someone might have a VBA example that I can use to get that. The code from my webservice is pasted in the 2nd code block in my question. — John Litchfield, May 14 '20 at 22:04
Wazz, here is my Access calling code from that URL you provided: Public Sub GetPerson() Dim reader As New XMLHTTP60 reader.Open "GET", "http://localhost:51075/WebService1.asmx", False reader.setRequestHeader "HelloWorld", "application/json" reader.send Do Until reader.ReadyState = 4 DoEvents Loop If reader.Status = 200 Then MsgBox (reader.responseText) Else MsgBox "Unable to import data." End If End Sub — John Litchfield, May 14 '20 at 22:14

score 0 · Answer 1 · answered May 15 '20 at 04:50

0

If you can't JUST type in the URL and the web browser does NOT spit out the nice hello world xml?

It is a config issue - REALLY nasty!

check out this post:

Request format is unrecognized for URL unexpectedly ending in

So, you need to add this to your web config:

<configuration>
   <system.web>
    <webServices>
     <protocols>
       <add name="HttpGet"/>
       <add name="HttpPost"/>
     </protocols>
   </webServices>
  </system.web>

So, you can use SOAP or rest here.

This url should work:

I would VERY much first ensure that typing in a SIMPLE URL into the browser works and SPITS OUT that xml.

Ok, so what is the REST url you have to type in for this to work?

Hit the invoke button. You get this:

NOTE carefull the above URL.

So, in VBA, we can go:

Sub GetWeb()

  Dim strURL     As String
  strURL = "https://localhost:44392/WebService1.asmx/HelloWorld"

  Dim xDOM       As New MSXML2.DOMDocument60

  xDOM.async = False
  xDOM.Load (strURL)

  Debug.Print xDOM.Text


End Sub

The above should output Hello World. But BEOFRE you test/try/run the VBA code?

MAKE sure you can JUST type in the same URL in the web, and it should spit out the above XML. (and note: for some reason WITHOUT the above web.config settings the "test" via .net work, but just typing in the URL does NOT work - so you need the above web config.

answered May 15 '20 at 04:50

Albert D. Kallal

42,205
3
34
51

Thank you so much Albert! That nailed it. For the benefit of others, I'll elaborate.I got it to work using the MS Access call example that Albert provided. I also added the HttpGet and HttpPost tags to my web.config file (they were missing). – John Litchfield May 17 '20 at 00:10
Per Albert's instruction, I tested the URL: http://localhost:51075/WebService1.asmx?op=HelloWorld ..and that the data it is supposed to (that always worked, but I may not have been clear on answering that follow-up question earlier). – John Litchfield May 17 '20 at 00:21
Note: If you're running your webservice from VB.NET when you're testing this you have 3 different versions of your web.config that you need to pay attention too. I think they work like this (but I'm still learning about .NET web stuff): A. web.config: This is the one that is used when you run the webservice from IIS. B. web.debug.config: This is the one that is used when you run your .net project in debug mode. C. web.release.config: This is the one that is used when you run your .net project in release mode. – John Litchfield May 17 '20 at 00:21
Someone reading this in the future may want to do this with parameters. I would use the code examples above and get it working this far. Then, check this URL on how to pass the parameters: https://stackoverflow.com/questions/11189451/how-can-i-call-a-web-service-and-pass-parameters-using-the-url I would love to see an example on how to pass variables by Reference. If someone has a link or an example on how to do that from Access, that would be awesome. t'm not certain yet if it can be done. Thanks again for the help everyone. – John Litchfield May 17 '20 at 00:24
1

Also, if your webservice is written in C# like mine is, you need to remember that the method call from your consumer application is Case Sensitive. – John Litchfield May 17 '20 at 16:18

Consuming an ASP.NET Webservice from an Access Application using VBA

1 Answers1