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
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
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.
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.
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.
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.
<%@ WebService language="VB" class="Hteksamples" %>
Public Class Hteksamples
<WebMethod> Public Function Add(a As Integer, b As Integer) _
Return a + b
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
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
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.
Public Function svc ()
Dim x as New clsws_Hteksamples
MsgBox (x.wsm_Add (4, 5))
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.
Public Class SQRUT
#Region " Web Services Designer Generated Code "
Public Sub New()
'This call is required by the Web Services Designer.
'Add your own initialization code after the InitializeComponent() call
With this added, now modify the code in the previous case
(with just one web service) as shown in 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))
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 ().
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.