consuming web service with Access 2010

[gr...@technologyworks.co.nz]

I'm confused.... I though this web stuff was supposed to be getting easier, but there is absolutely nothing out there on how to "consume" a web service with Access 2010

I have an app in Access 2003 on a client site

The client sites IT section has separately developed some custom web services using both SOAP and WCF RIA to enable inserting pdf documents directly into their document management system (TRIM)

So now I need to to write some VBA code to consume this web service and poke my pdf documents (generated from Access report) into their document management system.

Searching on the web for examples of using SOAP and WCF RIA with Access and there some examples of using SOAP with Access 2003, and using the Office Web Services toolkit, but I understand this toolkit has been dropped with Office 2010 and we are now expected to use Visual Studio and Dot.net to do this web stuff!

I intend upgrading my App to Access 2010 (assume best to go to accdb rather than mdb), but just what do I have to do to to enable "consuming" web services????


Any help much appreciated

Grant

[Albert D. Kallal]

wrote in message
news:dbfd3334-5739-4827...@googlegroups.com...

>I intend upgrading my App to Access 2010 (assume best to go to accdb rather
>than mdb)

You will be writing VBA - no reason why Access 2000 or 13 years later using
Access 2013 will make a difference.

>but just what do I have to do to to enable "consuming" web services????

Depending on how complex the web service calls are, often if you have just a
few basic calls, then using a XML library is often most simple, less code
and is light weight.

And if you use a built in xml library, then you likely don't need to install
any third party software.

I like using the MSXML library, since it is installed on near any computer
with a browser.

Here is a example to grab the current weather from the NOAA web service.


Public Sub GetWeather3()

Dim objXML As Object
Dim strWeatherStation As String
Dim strURL As String

Set objXML = CreateObject("MSXML2.XMLHTTP")

strURL = "http://w1.weather.gov/xml/current_obs/"
strWeatherStation = "KLGA"

objXML.Open "GET", strURL & strWeatherStation & ".xml", False
objXML.send

With objXML.responseXML
Debug.Print "Weather Station Location = " &
.SelectSingleNode("//location").Text
Debug.Print "Temp in F = " & .SelectSingleNode("//temp_f").Text
Debug.Print "Temp in C = " & .SelectSingleNode("//temp_c").Text
Debug.Print "Weather is " & .SelectSingleNode("//weather").Text

End With


Try pasting the above into a code module and run it. It should run and spit
out the current temp and weather.

I think the above is MUCH less work then using + building some code in
.net. However, using .net will give you inteli-sense and .net has a ton of
features built in that naturally lets you consume a web service. The idea
would be to write the code in .net, and then create a com object that you
use in Access. If you comfortable with .net, then this likely would be the
path of least resistance for you. The "main" problem with this approach then
is you have to assume that .net is installed on the target computer, and now
you have a com object hat you must also install on the target computer for
your Access application to work.

If you not rather familiar and comfortable with .net, and ALSO you don't
want the distribution hassle of a com object to be included with your Access
application, then I would consider using the above MSXML library. There
often a bit more "hand coding" and "hand holding" with above MSXML, but for
simple SOAP (web services) calls, I think the library is rather light weight
and not really a lot of code either. The bonus part as noted is above will
work with just pure VBA - no add-ins need be installed since we can quite
much assume MSXML is installed for use with browsers.

You can do a BingGoogle for some more MSXML examples, but you don't have to
use .net nor do you need the older SOAP add in (both could do the above web
service call to NOAA to obtain the current weather for a given weather
station - but as noted, since everything comes back as XML, then often using
the XML library will suffice.

You can make soap calls as the above shows, often the issue is setting up
the request header - grab the send/response headers from the given
documentation (assuming they have a a xml document WSDL).

The reason why .net (or even that old SOAP tool kit) is nice is they tend to
auto-generate a object with all of the web services methods and property's
based on the built in WSDL document. - of which you then can in code have
inteli-sense.

I will admit that Access really needs a nice built in XML and web services
system - but the MSXML works quite well in a pinch.

Best regards,

--
Albert D. Kallal
Edmonton, Alberta Canada
PleaseNoS...@msn.com