Internet, XML and Dropbox > Service-Oriented Access

Navigation:  Internet, XML and Dropbox >

Service-Oriented Access

Previous pageReturn to chapter overviewNext page

200503_pv3 Peter Vogel        

Lately, Peter Vogel has been developing Web Service-based applications for several clients. He comes back from the frontiers of technology to talk about what it means to the Access developer.

If you've been reading the IT press lately, you've heard a great deal about SOA–Service-Oriented Architectures. You may be wondering whether it means anything to you as an Access developer. The answer is that yes, SOA is very important to you. Like SQL, Web Services changes everything.

This is a topic that's been covered in Smart Access before, including ways of moving data across the Internet using ASP, ADO, and XML and, most recently, using Web Services as demonstrated by accessing the Google Web Service (see "Web Services for the Access Developer" in the July 2002 issue). However, much has changed since those articles were written and much has been made simpler in the process. As a result, it's time to revisit using Web Services in Access. If you're new to Web Services, see the sidebar "Web Services in a Nutshell" for a description that emphasizes how developers interact with Web Services.

Where does Access fit in?

To begin with, you can't build a Web Service with Access, though there's no reason that a Jet database can't be used as the database for a Web Service. In fact, Web Services provide you with an excellent way of making data from an Access application available to clients over the Internet. "Hold it!" I hear you say, "Isn't that what Web applications allow you to do?" The short answer is "Yes–but only if the user retrieved the data in a Web page." As many of you have discovered, creating a Web application that works like an existing Access application is a very different process from creating the original Access application.

This is why Web Services are so important: You don't use a Web browser as the client. Instead, the client requesting a Web Service can be any client at all, including an Access application. This means that to connect to any resource, all you have to do is wrap a Web Service around and then have your Access application call that Web Service. As long as both applications are on the Internet or the same intranet, they'll find each other and pass data between them.

This is where Access becomes part of an SOA: You can access any Web Service from your Access code. Think of Web Services as a kind of SQL that works over the Internet. With SQL you submit a text statement and get back data. If you're running against a full-fledged server-side database (like SQL Server), you can call stored procedures or cause server-side triggers to run, resulting in activities other than just data retrieval and update to occur. With a Web Service, you submit text (as complicated as you need) and get back text (as complicated as you need), triggering whatever other processing is associated with the function in the process.

What may be putting you off of Web Services is the amount of code required, especially the XML-related code. For all but the simplest services, custom XML documents are required both to send data to the Web Service and to deal with the result. For instance, here's the code from the 2002 article on accessing the Google Web Service (see the sidebar "The Google Web Service" for a quick introduction to the Google Web Service). At the time, this was what was required to submit a search request to the Web Service, catch the resulting XML sent back from Google, and load the URLs for the found pages into a listbox:

Dim spcl As MSSOAPLib.SoapClient

Dim Results As MSXML2.IXMLDOMNodeList

Dim ResultItem As MSXML2.IXMLDOMNode

Dim ResultPages As MSXML2.IXMLDOMNodeList

Dim ResultPage As MSXML2.IXMLDOMElement

Set spcl = New MSSOAPLib.SoapClient

spcl.mssoapinit "file://c:/GoogleSearch.wsdl"

Set Results = spcl.doGoogleSearch(strGoogleKey, _

    "Peter Vogel", 0, 10, True, "", _

    True, "lang_en", "", "")

For Each ResultItem In Results

  If ResultItem.nodeName = "resultElements" Then

    Set ResultPages = ResultItem.selectNodes("//item")

    For Each ResultPage In ResultPages

        Me.lstResults.AddItem _



  End If


In Access 2003, the code is much simpler:

Dim ggle As clsws_GoogleSearchService

Dim ggleReturn As struct_GoogleSearchResult

Dim gglResults() As struct_ResultElement

Dim ing As Integer

Set ggle = New clsws_GoogleSearchService

Set ggleReturn = ggle.wsm_doGoogleSearch(strGgleKey, _

       "Peter Vogel", 0, 10, True, "", False, "", "", "")

gglResults = ggleReturn.resultElements

For ing = 0 To UBound(gglResults)

    Me.lstResults.AddItem gglResults(ing).URL


This code first loads the clsws_GoogleSearchService Class and then calls the object's doGoogleSearch method (passing all 10 parameters). The result that comes back is placed in gglResults, an array of struct_ResultElements items. The code then loops through the array, adding each URL to a listbox on the form. Hard to get much simpler.

If you want to use the For...Each construct to process the loop (without losing IntelliSense), the code is only slightly more complicated. You first need to declare two more variables (the Variant that's required to work with the For...Each statement, and a struct_ResultElement to give you IntelliSense support when accessing the results from the Web Service):

Dim gglResult As struct_ResultElement

Dim gglIterator As Variant

With those variables declared, inside the For...Each loop you set the struct_ResultElement to the Variant that you're using to work through the collection. As this example shows, this change doesn't seem to gain you much when there's only one line of code inside the loop. However, if you're reading more than just one property from the Web Services result, using this technique rather than repeatedly accessing the gglResults array would marginally speed up your application:

For Each gglIterator In gglResults

    Set gglResult = gglIterator

    Me.lstResults.AddItem gglResult.URL

    '…accessing other properties on gglResult


The difference between the 2002 version and this version is that the 2002 version uses the struct_GoogleSearchResult, struct_ResultElement, and clsws_GoogleSearchService objects. Those objects let me completely avoid having to deal with the underlying XML used to communicate with the Web Services. These object types were generated by the Microsoft Office 2003 Web Services Toolkit 2.01. While the toolkit may not make using a Web Service ridiculously easy, it certainly eliminates the need to interact with the underlying XML. Instead, you can just set and read properties on objects.

Making SOAP easy to use

To use the toolkit, you need to follow three steps:

1. Download the toolkit from or find it by searching on the Microsoft site for "Microsoft Office 2003 Web Services Toolkit."

2. After you download and install the toolkit, in your Tools | References, check off Microsoft Office SOAP Type Library v3.0.

3. From the Tools menu, select Web Service References. This brings up the dialog shown in Figure 1.

Figure 1

This dialog is one of the ways that integrating your Access application with a Web Service becomes easier. Back in 2002, in order to connect to the Google Web Service I had to download the WSDL file from the Google Web site and feed the WSDL to a set of special objects that handled the communication with the Web Service. As a result, I had to know where to find the WSDL document and how to use it to create a Web Service. With the toolkit, you just need to enter a search term in the Keywords dialog and the Toolkit will search the UDDI libraries for references. On the other hand, if you do know the URL for the WSDL document or the service isn't listed in the UDDI libraries, you can enter the URL for the WSDL file into the Web Service URL textbox at the bottom of the dialog.

In Figure 1, you can see the results of searching for "google." On the right-hand side of the dialog is a list of the available Web Services and the list of the functions that can be called for each. This search is handled through the UDDI (Universal Discovery, Description, and Integration) network, a set of peer-to-peer libraries that contains references to and descriptions of WSDL documents. Clicking on the More button reveals some options that control how the search is done (including the ability to switch to your company's UDDI server if you have one).

Not all Web Services are as simple as the Google search service and not all Web Services are listed in the UDDI system. In Figure 2 you can see the results of entering the URL for the Amazon search service's WSDL into the dialog–this service supports more than two dozen functions (and this is just one part of the Amazon Web Service functionality). If your company has built Web Services but hasn't bothered setting up an UDDI service, this is the mechanism that you'd use to connect your Access application with your company's Web Service.


Figure 2

Clicking the Add button at the bottom of the dialog causes all the classes and data structures necessary to work with the Web Service to be created and added to your project. As in the Google example, the classes that contain the actual code for building/decoding the messages and communicating with a Web Service have the prefix "clsws_". Since Google has only one Web Service, only one clsws class is generated; for Amazon's many services, many clsws class modules will be created. Each function on the Web Service is represented by an identically named method in the class module generated for it. Based on the information in the WSDL file, the Toolkit also makes sure that these methods have parameters and return types that match the functions on the Web Service.

The class modules whose names begin with "struct_" contain declarations for a set of public variables to hold data for the Web Service. Because the variables are declared as Public, they appear as properties on the struct objects. These objects are used for two purposes:

• You load these properties with data and then pass the objects as parameters to functions on the Web Service that require complex XML.

• The code in the clsws class module populates the properties of these classes with the data from the XML documents returned by the Web Service.

Also generated by the Toolkit is a class module prefixed with "clasof_Factory_". This class module includes utility code that accepts an XML document, finds a reference to an object type in the document, creates an instance of that object, and then sets the properties on that object from data in the XML document. You never access this code directly, but this is, for instance, where the struct_ classes are created and have their properties set when the result comes back from the Web Service.

The Toolkit isn't perfect. If, for instance, a Web Service that you've been accessing updates its WSDL, you can pick up the new description of the Web Service using the Toolkit. However, you'll either have to manually delete the class modules generated the last time that you ran the Toolkit for this Web Service or let the Toolkit generate new classes with a digit added to the end (for example, clsws_GoogleSearchService2). The first method is time-consuming, but the only code that you'll have to rewrite is code related to actual changes in the Web Service (for instance, if some function requires a new parameter, you'll have to update your code to supply that parameter). Accepting the new class names will require you to rewrite all the code that explicitly references those classes–and then rewrite any code that's affected by changes to the Web Service.

One other warning: The Toolkit's dialog includes a Test button, allowing you to select a function in the left-hand list and then send a request to the function by clicking the Test button. Unfortunately, the button isn't much use because it calls the function on the Web Service without providing any parameters. Since almost all Web Service functions require one or more parameters, the result of clicking the Test button is usually an "invalid request" message. Still, the Test button does tell you that the Web Service is accepting messages.

Even with those complaints, the Toolkit does make snapping your Access application into an SOA easy to do. Effectively, you can now call routines anywhere on your company's intranet or extranet (or even on the Internet) as easily as you call functions in your own code. To demonstrate this, the sample database in this article will let you call the Google Web Service, provided you get your own access key from Google first.


Sidebar: Web Services in a Nutshell

The building block of SOA is the Web Service. A Web Service is a collection of functions that you can call over the Internet, which is the "Web" part of "Web Service." You can build a Web Service with either of Microsoft's tools for creating Web-based applications, ASP and ASP.NET, and run your service on IIS. But you can also build a Web Service using any one of a number of different toolsets and run it on any Web server that you choose (though not all combinations of toolsets and Web servers are compatible).

To call a Web Service function, you send an HTTP message to the Web Service, referencing the Web Service by its URL–just like requesting a Web page from a Web site. The message must be sent in a particular XML format called SOAP (Simple Object Access Protocol). All that the current version of SOAP does is provide an envelope around an XML document that contains the data that makes up your request. It's the responsibility of the Web Service to open the envelope, extract the XML document, and decide what to do about it. When processing is complete, the Web Service creates another XML document, wraps it in another SOAP envelope, and sends it back to the requesting client.

This is the "Service" part of "Web Service." To use a Web Service, you send a request as an XML document and you get back an XML document. Of course, the Web Service is free to do any processing on the server that the request asks for–retrieving/inserting/deleting data in a database, or performing any other kind of activity.

So what makes this an "architecture"? The architectural aspect comes from your application's ability to call any service no matter where that service is located. You can create a structure of services that's accessible to any application (security permitting). The ability to call a Web Service is enabled through WSDL (Web Services Description Language). A WSDL document describes a Web Service–or, at least, the functions that you can call and the formats of both the request and response XML documents One of the benefits of working with WSDL is that you never have to deal directly with any of the XML request document, the XML response document, or the SOAP envelope (see Figure 3). Web Service support tools can analyze the WSDL document and create objects that will take care of all the ugly details of the messaging. This is, for instance, what the Office SOAP Toolkit does for you.

200503_pv3Figure 3

WSDL makes accessing a Web Service ridiculously easy: Your program or application development tool reads the WSDL file, and your program instantly configures itself to issue requests to the Web Service. You can now access that service without having to know where the service is located because the WSDL document provides the URL where the service can be contacted. In fact, your application can now access any other service, no matter where those other services are located.

Sidebar: The Google Web Service

The Google Web Service supports three functions, the primary one being doGoogleSearch. This function accepts 10 parameters (all of them required, since the Web Service specification doesn't support optional parameters). These parameters support the options available on the Google Advanced Search page.

In order to access the service you must request a user key, which can be done at The service is free, though you're limited to 1,000 queries per day. The sample call to the Google Web Service in the article passes the key as the first parameter to the doGoogleSearch function, followed by the search term ("Peter Vogel"), and then the parameters that control how the search is done (maximum number of results to retrieve, whether filtering for "adult" content is applied, and so on).


Your download file is called 503VOGEL1.ZIP in the file

This is found  at