Access VBA and Web Services (XML)

Hi all,

I have a database that tracks equity compensation (Stock options, stock
units, etc.) For every issuance/exercise/disposition of these
instruments, I need a high stock price and low stock price in order to
compute the proper gain for tax purposes (average price on a given
day). In the past, this have been done manually at an unacceptable
rate of error (a person goes to bigcharts.com, types all the stuff in,
and then writes down the high/low price). I have found an XML Web
Service that produces the info I need for stupid cheap. My question
is: is it even possible to consume this service in VBA, or should I
just go straight to VB.Net (I have both, but I'm much more experienced
with VBA, and I've never used a Web Service before.)

Thanks,
Johnny

re: Access VBA and Web Services (XML)

Johnny,

You should be able to use VB to send SOAP requests and process XML
responses from the web service using the libraries

Microsoft SOAP Type Library (mssoap1.dll or later)
Microsoft XML (msxml3.dll or later)

You may also want to look at the SOAP toolkit at

http://www.microsoft.com/downloads/d...displaylang=en

If you decide to go the VB route and get stuck, I have a bit of code
that I can send you to get you started.

On the other hand, both providing and consuming web services is
extremely simple using the .NET framework. If you use Visual
Studio.NET, you simply add a URL reference to the web service and the
IDE automatically creates a proxy for the various methods exposed in
the service. At that point you have intellisense to guide you as you
make use of the methods. What could be easier?

If the web services aspect is a major part of your app or if you want
to get more familiar with .NET, it may be better for you to work with
..NET. On the other hand, if the web services component is only a small
part of the puzzle and you are time constrained, you may be better off
accessing the web services using VB in your Access project.

Bill E.
Hollywood, FL

re: Access VBA and Web Services (XML)

What do you think about writing a .Net wrapper for the Web Service and
setting a reference to it in VBA (I only need a small part of what's
offered via this service)? Also, I would love to have the code
snippets if you're offering (jmeredith@gmail.com)

Thanks,
Johnny

re: Access VBA and Web Services (XML)

That's not a bad idea, Johnny. To some degree, it would allow you to
get the best of both worlds. I've heard that you can compile your .NET
components and make them accessible in VB. I think that there are some
options that you must select in Visual Studio (or in the command line
compiler) before you create the assembly. I haven't done this so I'm
not totally familiar with it but you should be able to find information
on it.

The only drawback is that you would need to have the .NET framework
installed on the client machines whereas you wouldn't need it if you
went with the VB approach.

Bill

re: Access VBA and Web Services (XML)

Bill,

I'm going to search for KB,etc. documents on this. Thanks for your
opinions.

All,

Any readers out there done this before? Do you have any
advice/documentations.

Thanks everyone,
Johnny

re: Access VBA and Web Services (XML)

Johnny,

A little bit of follow-up information...

To compile your VB.NET component as a com component that can be used
with VB, you would go into the VS.NET project properties under
Configuration/Build and
turn on "Register for COM interop".

Bill

re: Access VBA and Web Services (XML)

Johnny Meredith wrote:[color=blue]
> What do you think about writing a .Net wrapper for the Web Service and
> setting a reference to it in VBA (I only need a small part of what's
> offered via this service)? Also, I would love to have the code
> snippets if you're offering (jmeredith@gmail.com)[/color]

There is a tool for the VBA IDE that builds proxy classes for
web services as well as doing other helpful things.

<split url>
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/dnxpwst/html/odc_ofwsrt.asp
</split url>

re: Access VBA and Web Services (XML)

"Johnny Meredith" <jmeredith@gmail.com> wrote
[color=blue]
> I have found an XML Web
> Service that produces the info I
> need for stupid cheap. My question
> is: is it even possible to consume
> this service in VBA, or should I
> just go straight to VB.Net (I have
> both, but I'm much more experienced
> with VBA, and I've never used a
> Web Service before.)[/color]

I think, if you explore a little at http://www.mcwtech.com/2004/webcasts,
you'll find this covered in a webcast by Ken Getz and/or Paul Litwin. I am
sure there is coverage in the current edition of their _Access Cookbook_
book. I know Ken's advice is: don't rely on any free Web Service being there
for your business application -- use one you wrote yourself or one that you
pay for.

Larry Linson
Microsoft Access MVP

re: Access VBA and Web Services (XML)

"Johnny Meredith" <jmeredith@gmail.com> wrote
[color=blue]
> I have found an XML Web
> Service that produces the info I
> need for stupid cheap. My question
> is: is it even possible to consume
> this service in VBA, or should I
> just go straight to VB.Net (I have
> both, but I'm much more experienced
> with VBA, and I've never used a
> Web Service before.)[/color]

I think, if you explore a little at http://www.mcwtech.com/2004/webcasts,
you'll find this covered in a webcast by Ken Getz and/or Paul Litwin. I am
sure there is coverage in the current edition of their _Access Cookbook_
book. I know Ken's advice is: don't rely on any free Web Service being there
for your business application -- use one you wrote yourself or one that you
pay for.

Larry Linson
Microsoft Access MVP

re: Access VBA and Web Services (XML)

Bill,

I think I'm going to go the .Net wrapper route. It has been a very
very simple process so far.

I tried using the Web Services Toolkit, but there are a couple of
things I could not figure out:

The web service has a license key of course. In .Net, passing this key
goes like this:

Dim licensekeystruct as New SILicenseInfo
licensekeystruct.licensekey = "xyz"

Dim ws as new HistroicalQuotes
ws.LicneseInfo = licensekeystruct

With VBA, the SILicenseInfo struct was generated when the proxy classes
were built. I've seen some code that uses the SOAP header to input the
license key, but I'm not sure that applies in this situation. Am I
missing something?

Also, one particular struct has a "Date" and an "Open" member. These
words are reserved in VBA. So, the code automatically generated does
not compile until I do something with those two names. I tried
brackets "[" but that didn't work. Have you ran into this before?

These questions are more food-for-thought than anything, as I will
probably use the .Net wrapper approach. Thanks for the help.

Johnny

re: Access VBA and Web Services (XML)

Correction:
<With VBA, the SILicenseInfo struct was > NOT <generated when the proxy
classes

re: Access VBA and Web Services (XML)

Johnny,

I'm afraid that I can't shed any light on this. By the way, which web
service are you using?

Bill

re: Access VBA and Web Services (XML)

StrikeIron Historical Quotes (www.strikeiron.com, search for historical
quotes, comes right up)