Consume Geodata web service

[Lauren Quantrell]

I'm trying to get geodata from Google or Yahoo's web service using an
Access (2000-2003) application. I have this working fine in vb.net but
have no idea how to do this in Access.

I'm looking for any example code from someone who has done this in
Access, or for any code examples of using HTTP POST from Access so I
can see how this is done.

These web services return straight XML and do not use SOAP and do not
use expose a WSDL.

Any help is appreciated.
lq

[rkc]

Lauren Quantrell wrote:

There's an error in the posted code:
Corrected as follows:

Public Property Get PostalCode() As String
PostalCode = _
GetNodeText("//Placemark/AddressDetails/Country/AdministrativeArea/
SubAdministrativeArea//PostalCode/PostalCodeNumber")
End Property

The more you learn about XPath queries the shorter all those paths
could be made.

[rkc]

Lauren Quantrell wrote:

There's an error in the posted code:
Corrected as follows:

Public Property Get PostalCode() As String
PostalCode = _
GetNodeText("//Placemark/AddressDetails/Country/AdministrativeArea/
SubAdministrativeArea//PostalCode/PostalCodeNumber")
End Property

Something else you need to know if you're actually plugging that code
into an application.

First: You shouldn't. You asked for an example. It's just a lame example.

Second: More than one Placement node can be returned if there is more
than one hit for the address you make a request for.
If that ever happens you will get weird results using that code.

Go to Google's Map Api information pages and read. That is what I did.

[Lauren Quantrell]

On Jan 5, 11:02*am, rkc <[e-mail]:

Lauren Quantrell wrote:

There's an error in the posted code:
Corrected as follows:

Public Property Get PostalCode() As String
* * PostalCode = _
* * GetNodeText("//Placemark/AddressDetails/Country/AdministrativeArea/

rkc,
Freaky how I can pass the XML string to a sproc in vb.net but not from
Access. I must be missing something. Double double quotes, triple
double quates I've tried...
lq

SubAdministrativeArea//PostalCode/PostalCodeNumber")
End Property

Something else you need to know if you're actually plugging that code
into an application.

First: You shouldn't. You asked for an example. It's just a lame example.

Second: More than one Placement node can be returned if there is more
than one hit for the address you make a request for.
If that ever happens you will get weird results using that code.

Go to Google's Map Api information pages and read. That is what I did.

[rkc]

Lauren Quantrell wrote:

rkc,
Thanks. I appreciate that on the multiple placement nodes. I have
taken the code as a starting point and have it working well with a
number of web services.
I've hit a wall on passing the XML to a sproc though.

Using OPENXML with the returned XML string from the geonames web
service works fine when I copy and paste it into a declaration within
the sproc, but I can't get it to work when I try to pass the returned
XML string as a parameter from MS Access. I have tried removing the
Char(10) and massaging the double quotes. There must be something
simple I've overlooked?

Example that works:

ALTER PROCEDURE myXMLSprocName

Sorry. I can't even pretend to know anything about how SQL Server
handles XML.

[lyle]

On Jan 7, 5:35 pm, Lauren Quantrell <[e-mail]>
wrote:

Freaky how I can pass the XML string to a sproc in vb.net but not from
Access.

Could you explain how you are getting the XML string in Access? When
I sign up for a Google Map API key I am told:
This key is good for all URLs in this directory:
http://www.whatever.ca/
Were I to use rkc's code from an application based there, I expect it
would work. But, TTBOMK I can't run Access from http:/www.whatever.ca,
and when I try it from somewhere else, such as my workstation I get
{"name":"11 Place Antonin Poncet, Lyon, FR ","Status":{"code":
610,"request":"geocode"}}
returned.

BTW, although this string has many "'s, it's easily passed to a Stored
Procedure.

[rkc]

lyle wrote:

On Jan 7, 5:35 pm, Lauren Quantrell <[e-mail]>
wrote:

>Freaky how I can pass the XML string to a sproc in vb.net but not from
Access.

Could you explain how you are getting the XML string in Access? When
I sign up for a Google Map API key I am told:
This key is good for all URLs in this directory:
http://www.whatever.ca/
Were I to use rkc's code from an application based there, I expect it
would work. But, TTBOMK I can't run Access from http:/www.whatever.ca,
and when I try it from somewhere else, such as my workstation I get
{"name":"11 Place Antonin Poncet, Lyon, FR ","Status":{"code":
610,"request":"geocode"}}

The code I posted specifically asks for XML in return.
With this: &output=xml

What you posted is JSON.

[rkc]

lyle fairfield wrote:

rkc <rk*@rkcny.yabba.dabba.do.comwrote in
news:47**********************@roadrunner.com:

>lyle wrote:

>>On Jan 7, 5:35 pm, Lauren Quantrell <[e-mail]>
wrote:

Freaky how I can pass the XML string to a sproc in vb.net but not
from Access.
Could you explain how you are getting the XML string in Access? When
I sign up for a Google Map API key I am told:
This key is good for all URLs in this directory:
http://www.whatever.ca/
Were I to use rkc's code from an application based there, I expect it
would work. But, TTBOMK I can't run Access from
http:/www.whatever.ca, and when I try it from somewhere else, such as
my workstation I get
{"name":"11 Place Antonin Poncet, Lyon, FR ","Status":{"code":
610,"request":"geocode"}}

The code I posted specifically asks for XML in return.
With this: &output=xml

What you posted is JSON.

But is the code allowed to get the XML when run from anywhere but the
site (url) for which the key is enabled? Perhaps it can and I made a
syntax or other error?

I run it directly from a Module in Access and it works just fine.
The status code you posted 610 indicates:
The given Key is either invalid or does not match the domain for which
it was given.

I have never had that error returned. Apparently the code works for
Lauren also. I just used rkcny.com as the domain when I applied for
the key. I have not even tried using the service from a web page yet.

The Geonames.org service seems to be no strings attached so you might
want to just use that instead for testing with SQL Server.

[lyle]

On Jan 7, 9:15 pm, rkc <[e-mail]:

lyle fairfield wrote:

rkc <[e-mail] in
news:47**********************@roadrunner.com:

lyle wrote:
On Jan 7, 5:35 pm, Lauren Quantrell <[e-mail]>
wrote:

>>Freaky how I can pass the XML string to a sproc in vb.net but not
from Access.
Could you explain how you are getting the XML string in Access? When
I sign up for a Google Map API key I am told:
This key is good for all URLs in this directory:
http://www.whatever.ca/
Were I to use rkc's code from an application based there, I expect it
would work. But, TTBOMK I can't run Access from
http:/www.whatever.ca, and when I try it from somewhere else, such as
my workstation I get
{"name":"11 Place Antonin Poncet, Lyon, FR ","Status":{"code":
610,"request":"geocode"}}
The code I posted specifically asks for XML in return.
With this: &output=xml

What you posted is JSON.

But is the code allowed to get the XML when run from anywhere but the
site (url) for which the key is enabled? Perhaps it can and I made a
syntax or other error?

I run it directly from a Module in Access and it works just fine.
The status code you posted 610 indicates:
The given Key is either invalid or does not match the domain for which
it was given.

I have never had that error returned. Apparently the code works for
Lauren also. I just used rkcny.com as the domain when I applied for
the key. I have not even tried using the service from a web page yet.

The Geonames.org service seems to be no strings attached so you might
want to just use that instead for testing with SQL Server.

What is rkcny.com? Is it by-chance a url for your own computer?

[rkc]

lyle wrote:

What is rkcny.com? Is it by-chance a url for your own computer?

No. http://www.rkcny.com. Hosted by Brinkster.com

[Lauren Quantrell]

I applied for my own Google key, not using yours. The domain I'm
running it from is http://localhost/ which Google accepts.

On Jan 7, 9:15*pm, rkc <[e-mail]:

lyle fairfield wrote:

rkc <[e-mail] in
news:47**********************@roadrunner.com:

lyle wrote:
On Jan 7, 5:35 pm, Lauren Quantrell <[e-mail]>
wrote:

>>Freaky how I can pass the XML string to a sproc in vb.net but not
from Access.
Could you explain how you are getting the XML string in Access? *When
I sign up for a Google Map API key I am told:
This key is good for all URLs in this directory:
http://www.whatever.ca/
Were I to use rkc's code from an application based there, I expect it
would work. But, TTBOMK I can't run Access from
http:/www.whatever.ca, and when I try it from somewhere else, such as
my workstation I get
*{"name":"11 Place Antonin Poncet, Lyon, FR ","Status":{"code":
610,"request":"geocode"}}
The code I posted specifically asks for XML in return.
With this: &output=xml

What you posted is JSON.

But is the code allowed to get the XML when run from anywhere but the
site (url) for which the key is enabled? Perhaps it can and I made a
syntax or other error?

I run it directly from a Module in Access and it works just fine.
The status code you posted 610 indicates:
The given Key is either invalid or does not match the domain for which
it was given.

I have never had that error returned. Apparently the code works for
Lauren also. I just used rkcny.com as the domain when I applied for
the key. I have not even tried using the service from a web page yet.

The Geonames.org service seems to be no strings attached so you might
want to just use that instead for testing with SQL Server.- Hide quoted text -

- Show quoted text -

[rkc]

Lauren Quantrell wrote:

I applied for my own Google key, not using yours. The domain I'm
running it from is http://localhost/ which Google accepts.

Obviously. I didn't post my key.
It's interesting that Google accepts localhost. I Wouldn't
have thought to try that.

[Lauren Quantrell]

I have this working nicely with a number of web services now after the
hint on how to pass the string parameter properly to OPENXML in a
sproc. Much thanks!

Now comes the question of how to apply a timeout to this process from
within the aplication. I notice some of these web services are not
causing a time out when the servies are down so therefore the
applciation locks up and the only escape is CLT+ALT+DEL <yikes>!
On Jan 7, 10:09*pm, rkc <[e-mail]:

Lauren Quantrell wrote:

I applied for my own Google key, not using yours. The domain I'm
running it from ishttp://localhost/which Google accepts.

Obviously. I didn't post my key.
It's interesting that Google accepts localhost. I Wouldn't
have thought to try that.

[rkc]

Lauren Quantrell wrote:

I have this working nicely with a number of web services now after the
hint on how to pass the string parameter properly to OPENXML in a
sproc. Much thanks!

Now comes the question of how to apply a timeout to this process from
within the aplication. I notice some of these web services are not
causing a time out when the servies are down so therefore the
applciation locks up and the only escape is CLT+ALT+DEL <yikes>!

The XMLHTTP object has a readyState property that returns 4 when the
ResponseText property is available. What you need to do is set the
ASync property of the Open method to TRUE so that your application
does not sit and wait for a response. Then in a Form's Timer Event you
pole the readyState property until it returns 4. When it does you run
code to do whatever it is you are doing with the ResponseText now.
You can then add code that decides when you have waited long enough
without a response and call the Abort method.

[rkc]

rkc wrote:

Lauren Quantrell wrote:

>I have this working nicely with a number of web services now after the
hint on how to pass the string parameter properly to OPENXML in a
sproc. Much thanks!

Now comes the question of how to apply a timeout to this process from
within the aplication. I notice some of these web services are not
causing a time out when the servies are down so therefore the
applciation locks up and the only escape is CLT+ALT+DEL <yikes>!

The XMLHTTP object has a readyState property that returns 4 when the
ResponseText property is available. What you need to do is set the
ASync property of the Open method to TRUE so that your application
does not sit and wait for a response. Then in a Form's Timer Event you
pole the readyState property until it returns 4. When it does you run
code to do whatever it is you are doing with the ResponseText now.
You can then add code that decides when you have waited long enough
without a response and call the Abort method.

Correction: Async parameter of the Open method.

[Lauren Quantrell]

ah, perfect. Thanks again!