Introduction

MS Office is Microsoft's flagship product, and some call it Mr. Gates' baby, not without good reason. This software is evolving all the time. Lo and behold! You can now generate classes that can be used by projects that consume a web service. This tutorial is about accessing and creating projects that extract web service information through a program created for this purpose - Microsoft Web Services Toolkit 2.0. Extracting web service information is possible from all Office XP applications that share the Visual Basic for Applications programming support.

What Is Needed?

Before you begin using MS Access to access web services on the internet, you need to download the Microsoft Office XP Web Services Toolkit 2.0 from the MSDN site. When installed, this tool, together with Windows Office XP, enables you to access the Microsoft .NET Web Service UDDI registry; this is akin to a telephone listing to pull out information about web services. You could either search keywords to locate the service, or you may access by the Web URL of the service, the WSDL, or even through the .NET created ASMX file's URL.

Discovering a Web Service

It is cakewalk to discover a service with this tool. It is assumed that internet connection is available. On the local machine, it is even easier. The tool can be accessed from any office application. All you need to do is to get access to the VBA editor, which in most cases is accessed by going to Tools | Macro | Visual Basic Editor. In Visual Basic Editor’s screen, click on Tools; from the drop-down menu, choose Web Service References. Visual Basic Editor can also be accessed via View | Toolbars | Visual Basic.

For this tutorial, the web service references tool will be invoked from Microsoft Access Office XP. Initially, a MS Access MDB file, TestWebService, is created. Highlight Modules and click on New in the database file menu. This opens the Visual Basic Window with Module1 in view. In this window, the Web Service References can be accessed by going to Tools. As in other Microsoft Office products, there is more than one way of achieving the same result.

From this menu, the Web Service References can be accessed as shown in Figure 1. When you choose this option, you will see the screen in Figure 2.

Figure 1

This is the main window of the Web Services References Tool 2.0. In this window, if you use the first option [Web Service Search Radio Button], you may enter some keywords or even a business name to search for the service.  If the search succeeds, the Search Results area should list all the results. This may depend on the speed of connection, authentication, etc. The default search will be made against the Microsoft's registry, but may be targeted to another by clicking the button More for changing the registry.

Figure 2

You may also choose to use a Web Service URL, in which case you must have information about the WSDL or ASMX file information. For example, I have a web service called test.asmx on my localhost on its FindJay project. When I type this in Figure 3 (this is a part of Figure 2 shown larger) and click on search, I see the results shown in Figure 4.

Figure 3

The above web service is created in Microsoft Visual Studio .NET 2003 by importing a web service file coded in Web Matrix. The code is shown in Listing 1.

Figure 4

In Figure 4, you will see the class name of the web service as well as the Web Methods, although this has only one, called Add. When you place a check mark against the class name and highlight the method name, you will be able to test the web service. This will display in the browser as shown in Figure 5. Here you may click on the hyperlink Add and test the method with the required parameters a and b.

Listing 1

<%@ WebService language="VB" class="Hteksamples" %>
Imports System 
Imports System.Web.Services
Imports System.Xml.Serialization
Public Class Hteksamples
  <WebMethod> Public Function Add(a As Integer, b As Integer) _
  As Integer 
    Return a + b
  End Function
End Class

Figure 5

Interfaces Created in the Application

As mentioned earlier, a variety of applications can leverage this tool for creating web service centric applications. In Figure 2, after testing the web service in Figure 4, you may add this to your application, in this case, the MS Access application. This adds the file clsws_Hteksamples (this is a class file) to the project's node, and will appear as a module in the Main window as shown in Figure 6.

This file will have the following components (abbreviated here):

Private Sub Class_Initialize ( ) 'creates a new soap client 'references derived Proxy information
Private Sub Class Terminate ( ) Private Sub HteksamplesErrorhandler (str_Function as string)Public Function wsm_Add()'proxy function created from the WSDL

Figure 6

Using the Web Services in a Project

As the class file is already added to the project, all that is needed is to instantiate the class using the new keyword as shown in the following code added to Module 1.

Listing 2

Public Function svc ()
  Dim x as New clsws_Hteksamples
  MsgBox (x.wsm_Add (4, 5))
End Function

When you try to run this function, you may be asked to run the macro with the name of the function svc. This will execute the program, the MS Access application screen goes blank, and after some time you see the message box with the return value of 9.

When you leave the application, you will be asked to save the information, and you may save all or some of them with their default names.

Referencing Multiple Web Services in a Project

Let us add one more web service to the project, going through the same steps as before by clicking the Web Services References tool. We use yet another service created in a web service project by VS 2003. Listing 3 shows the Web Service SQRUT, which calculates the square root of the sum of the squares of two numbers. As in the previous case, this can be tested in-situ and then added to the project, which adds a new class file, clsws_SQRUT. Now there are two classes from two web services.

Listing 3

Imports System.Web.Services
Imports System.Math
<System.Web.Services.WebService _
(Namespace:="http://tempuri.org/SQRoot/SQRUT")> _
Public Class SQRUT
  Inherits System.Web.Services.WebService
#Region " Web Services Designer Generated Code "
  Public Sub New()
    MyBase.New()
    'This call is required by the Web Services Designer.
    InitializeComponent()
    'Add your own initialization code after the InitializeComponent() call
  End Sub

With this added, now modify the code in the previous case (with just one web service) as shown in Listing 4.

Listing 4

Option Compare Database
Sub testsvc ()
Dim x As New clsws_Hteksamples
Dim y As New clsws_SQRUT
Dim z As Integer
MsgBox (x.wsm_Add (5, 6))
z = y.wsm_Hypotenuse _
(x.wsm_Add (2, 3), x.wsm_Add (5, 7))
MsgBox (z)
End Sub

Here both the classes are instantiated and their methods combined in the variable z. If this procedure is executed, you will come up with Microsoft Access' Macro editor where a Macro testsvc will be present. This can be run from this window and provides two messages corresponding to the two MsgBox () functions called by testsvc ().

Summary

The addition of this tool takes MS Access to a new level in RAD technology. The access to the web services appears seamless and transparent. While coding, you will notice that intellisense is in effect, which points to early binding, bringing with it all the benefits.  Even otherwise, the methods are made clear in the UI of this tool. Attempts to locate web services using the Keyword / Business Name search did not succeed, probably because of firewall issues. Even business names like Microsoft, IBM, and Intel did not bring up any of the services even with Microsoft's UDDI.