Accessing Web Services Under MS Access 2003?

[(PeteCresswell)]

I posted this to comp.databases.ms-access by mistake. I had already
posted something there, but wasn't getting any reaction. So here
it is in the intended group:
======================================================================

I've got the Office XP Web Services Toolkit installed, but it doesn't do
"Complex" data types - which seems tb my real world.

I can make this work ("Simple") data types:
-------------------------------------------------
Sub xx()
Dim mySC As SoapClient
Dim myIndustryName As String

Const mySampleSicCode As String = "100"

DoCmd.Hourglass True

Set mySC = New SoapClient
mySC.mssoapinit "http://ws.invesbot.com/companysearch.asmx?WSDL"

myIndustryName = mySC.GetIndustryName(mySampleSicCode)
MsgBox myIndustryName
Set mySC = Nothing

DoCmd.Hourglass False
End Sub
-------------------------------------------------

But I'm still groping when it comes to "Complex".

On the input side, I'm guessing that I have to concoct the proper XML to convey
the required parms and then feed it to whatever method I'm invoking as a string
just like the "Simple" example above.

For the output, my guess is that I have to set a ref to "Microsoft XML, v3.0"
(Library MSXML2, C:\WINNT\System32\msxml3.dll) and write code to iterate through
the XML string that's returned.

Have I got it right so far? If so, can anybody steer me to a concrete example
written in VBA? All the examples I've come across so far are written from an
HTML perspective and I'm having trouble figuring out what's needed (headers and
so-forth) and what is not on the input side.

 

[Brendan Reynolds]

Hi Pete,

I may not be able to get into this in as much detail as I would like. I've
injured a ligament in my hand and can only type very slowly with one hand
right now.

Your subject says Access 2003, but in the body of your post you say Office
XP. Are you sure you're using the right version of the Web Services Toolkit
for your version of Access? You should be using the Office 2003 Web Services
Toolkit with Access 2003.

Are you sure you're using the Web Services Toolkit and not the Soap Toolkit?
The use of SoapClient looks suspect to me. You should be able to set a web
reference to the web service (the toolkit adds a 'Web Service References...'
command to the Tools menu in the VBA IDE) and the toolkit creates a class
that wraps the service. (This automatically generated wrapper class is
called 'clsws_FXWSService' in the example code below.) In Access 2003,
creating the web reference automatically adds references to the Soap library
and the MSXML library, you shouldn't have to add those references manually.
I can't remember for sure if the same is true in Access 2002.

Finally, here's some sample code that works with this web service ...

http://www.newyorkfed.org/markets/fxrates/WebService/v1_0/FXWS.wsdl

Public Sub TestSub1()

Const strcSQL As String = "INSERT INTO tblTest " & _
"(TestCur1, TestCur2, TestDate, TestVal) VALUES("

Dim service As clsws_FXWSService
Dim doc As MSXML2.DOMDocument60
Dim nodeList As MSXML2.IXMLDOMNodeList
Dim node As MSXML2.IXMLDOMNode
Dim strDate As String
Dim dtmDate As Date
Dim strSQL As String

Set service = New clsws_FXWSService
Set doc = New MSXML2.DOMDocument60
doc.SetProperty "SelectionLanguage", "XPath"
doc.SetProperty "SelectionNamespaces", _
"xmlns:frbny='http://www.newyorkfed.org/xml/schemas/FX/utility'"
doc.LoadXml service.wsm_getAllLatestNoonRates
Set nodeList = doc.selectNodes("//frbny:Series")
strDate =
nodeList.Item(0).selectSingleNode("frbny:Obs/frbny:TIME_PERIOD").Text
dtmDate = CDate(strDate)
CurrentProject.Connection.Execute _
"DELETE * FROM tblTest WHERE TestDate = #" & _
Format$(dtmDate, "mm/dd/yyyy") & "#", , adCmdText
For Each node In nodeList
strSQL = strcSQL & _
"""" & node.selectSingleNode("@UNIT").Text & """" & ", " & _
"""" & node.selectSingleNode("frbny:Key/frbny:CURR").Text & """"
& ", " & _
"#" & Format$(dtmDate, "mm/dd/yyyy") & "#" & ", " & _
node.selectSingleNode("frbny:Obs/frbny:OBS_VALUE").Text & ")"
Debug.Print strSQL
CurrentProject.Connection.Execute strSQL, , adCmdText
Next node

End Sub

 

[(PeteCresswell)]

Per Brendan Reynolds:

Finally, here's some sample code that works with this web service ...

Thanks a lot. Got it running and now it's starting to make more sense to me.

The "Document" is what shows up when I point my browser at
http://www.newyorkfed.org/markets/fxrates/WebService/v1_0/FXWS.wsdl

That's where the methods are defined and, I'm guessing, any parameters get set -
in fact, that seems to be the whole ball of wax...

My next step is to find an example of something that requires one or more parms
to be specified.

 

[Albert D.Kallal]

Remember, you want to use the soap add in from the *code* editor in
ms-access.

You simply go

tools->web services references

Choose the web service URL radio button, and paste in your url you had of

http://ws.invesbot.com/companysearch.asmx?WSDL

save your module. Ms-access will generate a class object with all of the
methods pre built for you!!!

Now, here is some test code (I tried this as we type)

Sub test1()

Dim webC As New clsws_CompanySearch

Debug.Print webC.wsm_GetIndustryName("1000)


End Sub

The result in the deug window is
For 1000, we get
Metal Mining

So, you don't have to write the code if you let the access soap add-in do
all the work for you...
(2 lines of code is all we needed!!!).

You could list out all sic numbers and categories such as

Sub test1()

Dim webC As New clsws_CompanySearch
Dim webData As MSXML2.IXMLDOMNodeList

Dim intIndustryCount As Integer
Dim intFields As Integer

Dim i As Integer
Dim j As Integer

Set webData = webC.wsm_GetAllIndustries
intIndustryCount = webData(0).childNodes.length - 1

intFields = webData(0).childNodes(0).childNodes.length - 1
For i = 0 To intFields
Debug.Print webData(0).childNodes(0).childNodes(i).nodeName & " ";
Next i
Debug.Print

For j = 0 To intIndustryCount
For i = 0 To intFields
Debug.Print webData(0).childNodes(j).childNodes(i).Text & " ";
Next i
Debug.Print
Next j

End Sub

You can also view the raw xml.....

Debug.Print webData(0).XML

 

[Albert D.Kallal]

had to run out for dinner...but am back....

Here is code snip that would import all of the sic codes, and descriptions
into a access table

Sub test2()


Dim webC As New clsws_CompanySearch
Dim webData As MSXML2.IXMLDOMNodeList

Dim intF As Integer
Dim strF As String

strF = "c:\sic.xml"
Set webData = webC.wsm_GetAllIndustries

If Dir(strF) <> "" Then
Kill strF
End If

intF = FreeFile()
Open strF For Output As intF
Print #1, webData(0).XML
Close intF

Application.ImportXML strF, acStructureAndData

End Sub

When you run the above...you will have a nice new ms-access table called
industry built for you. This xml and soap with ms-access is really far
out.....

 

[Brendan Reynolds]

The class the toolkit creates for you is just a standard VBA class, there's
nothing special about the way you pass parameters to it. Just pass them as
you would to any built-in or custom class. For example, the web service I
used in my previous example has a method that takes a currency code as a
parameter. There's example code showing how to call that method below.

The tricky part, in my experience, is not the calling of the service, but
the parsing of the result. XML itself is a relatively straight-forward
technology, but to make good use of it you have to use a number of other,
related technologies, such as XPath in this example.

Public Sub TestSub3()

Const strcSQL As String = "INSERT INTO tblTest " & _
"(TestCur1, TestCur2, TestDate, TestVal) VALUES("

Dim service As clsws_FXWSService '<- toolkit-generated class

'might need to use DOMDocument40 or DOMDocument30
'with earlier versions of MSXML
Dim doc As MSXML2.DOMDocument60

Dim nodeList As MSXML2.IXMLDOMNodeList
Dim node As MSXML2.IXMLDOMNode
Dim strDate As String
Dim dtmDate As Date
Dim strSQL As String

Set service = New clsws_FXWSService
Set doc = New MSXML2.DOMDocument60
doc.SetProperty "SelectionLanguage", "XPath"
doc.SetProperty "SelectionNamespaces", _
"xmlns:frbny='http://www.newyorkfed.org/xml/schemas/FX/utility'"

doc.LoadXml service.wsm_getLatestNoonRate("EUR") '<- parameter passed
here

Set nodeList = doc.selectNodes("//frbny:Series")
strDate =
nodeList.Item(0).selectSingleNode("frbny:Obs/frbny:TIME_PERIOD").Text
dtmDate = CDate(strDate)
CurrentProject.Connection.Execute _
"DELETE * FROM tblTest WHERE TestDate = #" & _
Format$(dtmDate, "mm/dd/yyyy") & "#", , adCmdText
For Each node In nodeList
strSQL = strcSQL & _
"""" & node.selectSingleNode("@UNIT").Text & """" & ", " & _
"""" & node.selectSingleNode("frbny:Key/frbny:CURR").Text & """"
& ", " & _
"#" & Format$(dtmDate, "mm/dd/yyyy") & "#" & ", " & _
node.selectSingleNode("frbny:Obs/frbny:OBS_VALUE").Text & ")"
Debug.Print strSQL
CurrentProject.Connection.Execute strSQL, , adCmdText
Next node

End Sub

 

[(PeteCresswell)]

Per Albert D.Kallal:

save your module. Ms-access will generate a class object with all of the
methods pre built for you!!!

That's how I started out.

I created the self-contained routine just as an exercise so I could get down to
what actually has to be done.