473,725 Members | 2,238 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

Nov 13 '05 #1
12 3470
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

Nov 13 '05 #2
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 (jm*******@gmai l.com)

Thanks,
Johnny

Nov 13 '05 #3
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

Nov 13 '05 #4
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

Nov 13 '05 #5
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

Nov 13 '05 #6
rkc
Johnny Meredith wrote:
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 (jm*******@gmai l.com)


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>
Nov 13 '05 #7
"Johnny Meredith" <jm*******@gmai l.com> wrote
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.)


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
Nov 13 '05 #8
"Johnny Meredith" <jm*******@gmai l.com> wrote
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.)


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
Nov 13 '05 #9
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 licensekeystruc t as New SILicenseInfo
licensekeystruc t.licensekey = "xyz"

Dim ws as new HistroicalQuote s
ws.LicneseInfo = licensekeystruc t

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

Nov 13 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
2202
by: Anand Natrajan | last post by:
Hi! I have a VB .NET client that is accessing an rpc/encoded service deployed in Axis 1.2. One of the operations, whoami, returns a string array. The server-side Java signature of this method is String whoami(MyPrincipal principal) My .NET client is unable to access the results of that array. The odd thing is that the very same client was able to access the results when the same service was deloyed in Axis 1.1. I've asked the Axis folks...
63
5913
by: Jerome | last post by:
Hi, I'm a bit confused ... when would I rather write an database application using MS Access and Visual Basic and when (and why) would I rather write it using Visual Studio .Net? Is it as easy in Visual Studio to create reports and labels as it's in Access?` The advantage of VS.net is that not every user needs Access, right? And that would eliminate the Access version problem as well I guess.
64
5246
by: John | last post by:
Hi What future does access have after the release of vs 2005/sql 2005? MS doesn't seem to have done anything major with access lately and presumably hoping that everyone migrates to vs/sql. Any comments? Thanks
52
9976
by: Neil | last post by:
We are running an Access 2000 MDB with a SQL 7 back end. Our network guy is upgrading to Windows Server 2003 and wants to upgrade Office and SQL Server at the same time. We're moving to SQL Server 2005, and, since he already has licenses for Office Pro 2002, he wants to upgrade to that. I've been saying that we need to upgrade to Access 2003, not 2002, even if Office is kept at 2002. We are also looking to do a fair amount of...
3
3024
by: Bruce | last post by:
I am building a WinForms app that uses Web Services access to a server for most of its data input/output, but I also need to persist some of its data to the local disk (basically as a cache of some of the Web Services data) in XML format. Since the size of the XML local store could be rather large, I'd prefer to have a random access mechanism for reading and writing to it. It seems that XMLReader /XMLWriter are sequentially fast,...
37
5233
by: jasmith | last post by:
How will Access fair in a year? Two years? .... The new version of Access seems to service non programmers as a wizard interface to quickly create databases via a fancy wizard. Furthermore, why would you even continue to use Access as a backend when you have a much superior option in SQL express? What about as a future front-end development tool? Let's get serious. Microsoft continues to publish numerous articles and videos on how you...
1
463
by: Bob Alston | last post by:
Anyone know of any features of the new Access 2007 that will improve on the ability of Access to work in a web environment??? Bob
9
2163
by: JT | last post by:
Here is the overall structure I will be referring to: End-program ProvideWorkFlow.dll Forms and methods that properly manipulate calls to methods in AccessUtils AccessUtils (a web service) Hide.dll methods and data I want to remain hidden I have a DLL, Hide.dll, that contains methods that I want to handle for
1
2654
by: gihan | last post by:
Hi, I have a problem accessing remote webservice from my asp code. Instead of returning results, it returns list of web methods it has. Wonder where i'm doing wrong. Also note that, this is a ristricted webservice, which need username password to access and I can't use third party tools (llike xmlspy, webservice studio) to access it and debug. I only got is WSDL. Here is the WSDL <wsdl:definitions xmlns="http://schemas.xmlsoap.org/wsdl/"...
0
8882
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9398
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9250
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9165
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9098
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
8080
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6699
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4777
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3216
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.