Hi

I'm using access 07. There is another company my company is to hook up with. My system has to make requests off their system. Here's the problem. I'm really new to this kind of thing. Please correct me, if i'm wrong.

They use something called "SOAP". I believe this is a language similar to xml only better than it (more modern, an improvement on xml). From our system, we will hand their system a customer number, and they return information on this customer. Address, age, phone number etc.

Anyway, this other company, the person at their end, has set me up with a test username and password for their system. They have also, suggested i download and install: soapUI

from here:

http://www.soapui.org/

Anyway, when i open up this program, i set up a new project, it asks me for a wsdl, so i type in a testserver wsdl.

It downloads a schema. I type in the username and password in an example soap template. i also type in the Customer Number. On the screen, i can see it querying the web, and it pulls back data onto the screen.

Ok, i'm happy with all of this. Here is the next stage. I would like for my inhouse users here to be able to make these requests from access & vb. They hit a button on a form in access, the request is made in back, and the response is pushed into a table.

Anyway, i asked this other company have any example code for vb, they said they didn't. So i'll have to figure this one out myself. So,

I went onto google. Seen there was a soap toolkit for windows:

http://www.microsoft.com/download/en/detai...en&id=13456

I downloaded and installed it. I opened, Ms Access 07, i opened the vb editor, and the reference library. I ticked the following references (because they sound useful):

Microsoft Office Soap Library v3.0

Microsoft Soap Type Library v3.0

WinHttp Soap Connector Type Library

Microsoft Soap WSDL File Generator v3.0

Microsoft XML, v6.0

Anyway, i've a bunch of questions.

Can access do what i'm looking off it. Make requests, (like the tests i'm doing on "SoapUI") get the responses and add them to a table in access? I don't have a clue about Soap, so i'll presume there is a reference library, how do i go about making these requests? What are the function names etc? How do i find out?

Has anyone used Soap before? You could help me out?

Regards

Conor

Thats just begging for a gag to be made...

While I've never used SOAP toolkit, I've written system that consumes data from a SOAP web service. This was done by using VBA + MSXML. You've already gotten the schema from WDSL which tell you how you need to form your XML document. You'd build that using a VBA string, and submit it via XMLHTTP object of the MSXML library, then read the response. You can choose to use MSXML's DOM document or read it back as a long string in VBA. I think it's easier to parse out the data via DOMDocument.

Google on MSXML VBA reference for starters on how to use XMLHTTP to submit a request. See if it get you started.

Been a long time since I looked at the Soap toolkit and I never did anything but
play with it, but I believe part of what it does is generate VB6 class files from a wsdl file.

Then all you have to do is use the classes that are generated in your VBA code.

Hi thanks to both of yous.

Banana republic, i'm going to attempt your way 1st.

I have the string ready to go. Not sure how to send it just yet.

Here's my code:

Dim o As New XMLHTTP
Dim s As String

On Error GoTo err_handler


s = s & "<soapenv:Envelope xmlns:soapenv=""http://schemas.xmlsoap.org/soap/envelope/"" xmlns:cor=""http://webservi....."">" & vbCrLf
s = s & "<soapenv:Header/>" & vbCrLf
s = s & "<soapenv:Body>" & vbCrLf




Etc...

How do i push / post it?

Hi banana Republic

I've now got it partly working:

o.Open "POST", "http://q.webservic...l", False, "065631", "TJGV0412"
o.Send s
txtResponseHeaders = o.getAllResponseHeaders
txtResponse = o.responseText
MsgBox "txtResponse: " & txtResponse

This code now spits out a response text file. Just like i was getting with soapUI. Big breakthrough.

Now i just need to parse the return string..

Hi Banana Republic

This DomDocument..what is it exactly? How do i apply it to my situation? I have a returned string with all the data i need in it. How do i split it up into its various parameters using Dom Document?

What does the xml look like? Since an XML file can represent just about anything
it's generally necessary to write parsing code that is specific to the document you
are working with.

Conor -

Create a new DOMDocument, and use loadXML method to put the XML string into the DOMDocument object. You can then loop the nodes collection and therefore transverse the XML that way.

http://msdn.microsoft.com/en-us/library/wi...5(v=vs.85).aspx

See if this get you started.

Almost all web services return XML structures.

The reason why MSXML is a nice choice is that you can pull out the values returned.

Parsing out "nodes" or a return value from a web service is quite easy.

For example, I hit a SharePoint site, and request via web service the list of sub sites. In fact, you want to setup some code to make sending a web request also easy - but it sounds like you have that working.

The DOM and the object model return has a node in my example called called "WEBS" which in fact is a list of web sites. (name and URL).

So, to pluck out that XML value, I do this:



There are a lot of examples of using MSXML to pull out (or even set) values in a xml structure. A BinGoggle should produce many examples. I cannot seem to find a nice MSXML reference I had, but there are lots of examples. You certainly do not have to write the code to parse out the values, and you can usually use code much like the above to pull out the return values.

Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
kallal@msn.com

What's the difference between "parse" and "pull out"?

Ah, ok, that is a good question!

I suppose the real difference here is that using the MSXML library gives structure and thus does the parsing for you.

I consider parsing code is code in which you have to deal with the delimiters, <tags> etc and your code thus has to "parse out" the values you need BETWEEN the delimiters that segments out the data into a useful structure.

This typically suggests you will write code using left$(), right$(), mid$(), split(), instr() etc, or some such combination of those commands.

The data in the sample code I posted really is just formatted markup text (HTML or in fact more correctly XML data). It is tag delimited like most regular markup text (HTML).

In fact most web sites do return markup text and especially the case for web services, they return not only markup text, but almost always in XML format.

A great example in Access would be if you wanted to import a CSV text file. You can open up a CSV (comma delimited) text file directly in Access (open for input# and line input commands) and parse out each delimited data part between each "," (comma). Or you could use the built in import routines (transfer text) and thus import that data into a temp table. At that point you would then JUST write code to "pull out" the values you want from something that now has a given and defined structure and as such does not need parsing code.

You may very well still have to define and use a reocrdset to read the data from that temp table, but such a process is more of just an act of "pulling out" the data in some pre defined structure and I would certainly not consider that type of code "parsing code".

I think I am quite much suggesting that one does not want to write or roll their own HTML or XML parsing code when tools exist to give that data structure for you.

Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
kallal@msn.com

So when you wrote: Parsing out "nodes" or a return value from a web service is quite easy.

What you really meant to say is: Pulling out "nodes" or a return value from a web service is quite easy.
or
Plucking out "nodes" or a return value from a web service is quite easy.

Because the MSXML library does the parsing for you. What's left for the programmer is to pluck or pull.

Interesting.



Sounds like XML to me.

Sure, that sounds about right. However, you cannot pull out those values unless some parsing occurs first. Leaving out the detail that the challenge of parsing still does exist would be unfair to the readers.

Some web services can in fact return just a plain string of text that does not require further parsing. And in fact some cases a web service call can return a csv string (comma delimited).

However in most cases, it will be xml, and this is especially if there is a "table" of repeating data.

So the "act" of parsing still has to occur, it just a question if one is going to write that parsing code or use a library?

I really do think that a soap library or add-in should be part of Access. Looking that the increasing number of web services questions on UA, then the old "soap" tool kit for office does need a re-write (that came out in Access 2002). The problem with the old kit was it produced class modules and VBA (and I think that is the wrong way to do this).

We should be able to point that web object to the web service definition (WSDL), then at that point the object should just produce and show all the methods and properties of that soap object. This should work just like when we set a reference to a com object. So when we use an object like word or even a recordset object the methods + properties should just show up in the VBA editor (this in fact was the "one" good aspect of the soap kit for office, but I just not keen on a code generator).

In fact this inteli-sense issue is why many suggest using .net to create a com object add-in for use with Access (.net will do all this dirty generation work for you). If the web service in question is complex, then in fact using .net to create com object for use with Access is probably the best bet.

However, I REALLY like the MSXML library. This library is quite lightweight and can quite much be assured it is installed on the target machine anyway. So right now the MSXML library is my first choice.

I mean, to have to fire up Visual Studio, learn .net and then create a com object for use with Access is too much dancing. And worse this means you just introduced the issue of distributing an ActiveX part that has to be installed and registered on each machine.

So using MSXML eliminates all of this, and also eliminates the need for having to use + adopt Visual Studio and .net as a development tool. So using .net and VS seems like over kill just to get some silly text back from a web site!!

In my example code, the data returned from the web service is this:

(IMG:https://public.bay.livefilestore.com/y1piskP6fRnbiK8ZAwI5Tg5zzSBzm6Vhn5MrUkuMDV8x8fU7KBFMS9Ew2EokiIN6NWWPloser2CXRLXG4dTL8amAQ/xml2.png)

So, I did not want to "just" suggest that the results are ready for use, but "bridge" of parsing that returned data also does exist here.

My code that pulls out that xml data as a table is thus this:



And, just like a recordset, one could use the column names like this:


And more interesting is you can open that resulting XML file in Excel. Unfortunately MS Access cannot import that above resulting xml into a table directly.

I REALLY should be able to write that text out to a file "as is", and then execute:

application.ImportXML "path name to file",acStructureAndData

So, the xml importing routines in Access also needs some work here. The XML import option in Access comes REAL close, and you in fact get this:

(IMG:https://public.bay.livefilestore.com/y1piskP6fRnbiJyQoZ4o1SaOsSJv1m3gPE0CHIEBtxUXeP36XXdnb-14jejagjUsGUPgEO-M7tICVc9dgWrWS6ZAQ/xml1.png)

So, Access xml import "sees" the tables, but Access is simply unable to import that data structure. So Access in terms of XML import is weak and I think the above import should work with ease and it does not.

So while there are many choices here ranging from using .net to even considering Excel automaton, the best compromise between heavy and lightweight approach(s) seems to be using the MSXML library.

Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
kallal@msn.com

Albert for importing into Access tables, transforming is almost always needed, especially considering that XML is semi-structured and we need to put it in a relational data store. For this case, I say write a XSLT and use TransformXML method (it's a method of Access.Application object, among with ImportXML and ExportXML) or you can use Transform in MSXML. The advantage of writing a XSLT is that you now can write how you want to transform the XML in a declarative sense, not procedural sense, much like what SQL does for us. You can then describe what how data in XML should be restructured so you can then easily import them into correct Access tables, even appending them to the data table. That means you need to distribute XSLT file with your Access application, but that requires no registration to use.

Thanks for the comments BR.

And yes, I do agree that a transform would (could) fix this.

However, I think when data is formatted that the DOM can deal with and see this as a table then such a table should import into Access.

That above table list format is rather common these days.

I mean we have this xml structure:

(IMG:https://public.bay.livefilestore.com/y1p3r0iKm9QT2CTa_lN0Q63Mfs_GMjO2hx18Ds70XNOxKlShcf-NJLz-lMNc-p51FHZSg6JTNqNBPbIXDZ13KYHQg/x2.png)

The DOM code I posted using MSXML sees this as a table, and even Excel without a data transform can open this (it makes a guess as to the needed transform) and you get this:

(IMG:https://public.bay.livefilestore.com/y1p3r0iKm9QT2BYEpJuqP9ZnmPxZOYz7C4AN8WXP9eY0yj7lY5G32UNKUdDIcX-RmH0O_2I_lHv4qe2nonTzTXgVQ/x1.png)

So even Excel gets this right and even the headings/titles are set correct as above shows.

I just think in this kind of case a transform should not be required.

So, I can see the table, Excel can see the table, and the DOM has no trouble either.

I fully admit I am the one lacking here in my abilities to create transforms. It is something I just not mastered or really spent the time to learn. (it in my bucket list so to speak).

So fair is fair.

However, the above is common in DOMs and the above really amounts to a repeating data that looks like a table.

I think Access should be able to import this data and at least make the same guess Excel does. (I mean, if everything else needed a xlst, then I would not have much of a case here).

I often seen xml data like above (especially) from web services. I often seen this format for other table data – because of this, I guess a transform should be easy.

I do realize that a strict XML table generally are surrounded with matching set of tags (in above one would see this:



So, this could very well me thinking about this in the wrong way is simply based on me not having spent time to learn and use xlst transforms.

However, if Excel and MSXML via the DOM can pull out this data in a table format with ease, then I just think Access should also do the same!

My spider sense says I am trying to get a "go past jail" card and not have to "earn" how to write a xlst! (IMG:style_emoticons/default/reading.gif)

Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
kallal@msn.com

Interesting observation, Albert. However, I want to point out a problem that's present in Access. If you've had tried to import a CSV file and everything looks fine in the preview until you actually try to import and blam, it fails. Why? Oh, preview thought one column was a number but it just "happened" that the CSV file had 100 first rows that contained what looked like a number but text do not appear until 101th row. Same problem exists and to a degree is more acute on XML. For example, this is valid XML:


Note how the 2nd order has an extra column? In the importing, Excel can process, notice a new column, shrug and put it down on the next blank column. Access, being relational, will pull out all stops, and say, "whoa, there, pally. I have no room for this miscreant!" Unless the XSD is provided (and it's not always guaranteed that one is given), it is simply impossible to parse the XML and come back with correct schema. You can guess what schema MAY be based on existing data, but you have to cross the fingers that all kind of data are presents to form the full theoretical "row" that may be generated by web service (or whatever). At least CSV is easier because you have well defined column terminator and a row terminator. There is no such thing in XML. You can define what terminates a row (e.g. the Order tag in this case), but you can never be certain you've ran into all possible columns if you don't have the XSD. For Access to work as well as Excel, it would have to most likely read the whole XML to parse all possible columns before it can import the data. If XSD is available, then sure, Access should read it and use it to form the correct table definition and our job would be easy.

Now, I'll concede with your point that Access' Import XML Ui could be improved and made to automatically detect common XML structure such as SOAP, REST or whatever so the data get in directly. But I still think that you're going to end up wanting to write a XSLT anyway so you can get the XML data to conform with the desired table structures. Almost all of my practical use of web services has needed a XSLT so that I could maintain the parent-child relationships among several different tables, all coming from a single XML document.