Consume Geodata web service

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

Re: Consume Geodata web service

Sub Test_GoogleGeoRequest()
Dim addr As String

addr = "11 Place Antonin Poncet, Lyon, FR"
'addr = "5 High Street, Galway, IE"
'addr = "41 Mill St,Marion,NY"
'addr = "Stanley St,Belleville,ON"

If Not MakeGeoRequest(addr) Then
MsgBox "Request failed", vbCritical, "F'd Up Request"
Exit Sub
End If

If HasData Then
Debug.Print Address 'formatted address
Debug.Print CountryNameCode
Debug.Print AdministrativeAreaName 'state name if US address
Debug.Print SubAdministrativeAreaName 'county name if US address
Debug.Print PostalCode 'zip code if US address
Debug.Print Latitude, Longitude
Else
MsgBox "Status Code: " & StatusCode & vbCrLf _
& "No information was found for" & vbCrLf & addr, _
vbInformation, "Address Was Not Found"
End If

End Sub




Option Compare Database
Option Explicit

Const Map_Api_Key = "yourKeyHere"
Private m_Xml As String
Private doc As MSXML2.DOMDocument
'Private doc As Object

Public Property Get RawXML() As String
RawXML = m_Xml
End Property


Public Property Get CountryNameCode() As String
CountryNameCode =
GetNodeText("//Placemark/AddressDetails/Country/CountryNameC ode")
End Property

Public Property Get Address() As String
Address = GetNodeText("//Placemark/address")
End Property

Public Property Get AdministrativeAreaName() As String
AdministrativeAreaName = _

GetNodeText("//Placemark/AddressDetails/Country/Administrati veArea/AdministrativeAreaName")
End Property

Public Property Get SubAdministrativeAreaName() As String
SubAdministrativeAreaName = _

GetNodeText("//Placemark/AddressDetails/Country/Administrati veArea/SubAdministrativeArea/SubAdministrativeAreaName")
End Property

Public Property Get PostalCode() As String
PostalCode = _

GetNodeText("//Placemark/AddressDetails/Country/Administrati veArea/SubAdministrativeArea/PostalCode/PostalCodeNumber")
End Property

Public Property Get StatusCode() As String
StatusCode = GetNodeText("//Status/code")
End Property

Public Property Get HasData() As Boolean
HasData = StatusCode = "200"
End Property

Public Property Get Coordinates() As String
Coordinates = GetNodeText("//Placemark/Point/coordinates")
End Property

Public Property Get Latitude() As String
Latitude = Split(Coordinates, ",")(1)
End Property

Public Property Get Longitude() As String
Longitude = Split(Coordinates, ",")(0)
End Property

Public Function MakeGeoRequest(Address As String) As Boolean
Dim msXml As MSXML2.XMLHTTP
Set msXml = New MSXML2.XMLHTTP
'Dim msXml As Object
'Set msXml = CreateObject("Microsoft.XMLHTTP")

On Error GoTo errHandler

msXml.Open "GET", "http://maps.google.com/maps/geo?q=" & Address &
"&output=xml&key=" & Map_Api_Key, False
msXml.setRequestHeader "Content-Type", "text/html"
msXml.send

'Set doc = New MSXML2.DOMDocument
Set doc = CreateObject("MSXML2.DOMDocument")
m_Xml = msXml.ResponseText
doc.loadXML m_Xml

MakeGeoRequest = True
Exit Function

errHandler:
'return false
End Function

Private Function GetNodeText(path As String) As String
Dim n As IXMLDOMNode
'Dim n As Object
Set n = doc.selectSingleNode(path)
If Not n Is Nothing Then
GetNodeText = n.Text
End If
End Function

Re: Consume Geodata web service

Thank you so much! I'll play with this tonight. Don I need to register
any DLLs to make this work?

On Jan 4, 1:15=A0am, rkc wrote:
> Sub Test_GoogleGeoRequest()
> =A0 =A0Dim addr As String
>
> =A0 =A0addr =3D "11 Place Antonin Poncet, Lyon, FR"
> =A0 =A0'addr =3D "5 High Street, Galway, IE"
> =A0 =A0'addr =3D "41 Mill St,Marion,NY"
> =A0 =A0'addr =3D "Stanley St,Belleville,ON"
>
> =A0 =A0If Not MakeGeoRequest(addr) Then
> =A0 =A0 =A0MsgBox "Request failed", vbCritical, "F'd Up Request"
> =A0 =A0 =A0Exit Sub
> =A0 =A0End If
>
> =A0 =A0If HasData Then
> =A0 =A0 =A0 =A0Debug.Print Address 'formatted address
> =A0 =A0 =A0 =A0Debug.Print CountryNameCode
> =A0 =A0 =A0 =A0Debug.Print AdministrativeAreaName =A0'state name if US add=
ress
> =A0 =A0 =A0 =A0Debug.Print SubAdministrativeAreaName 'county name if US ad=
dress
> =A0 =A0 =A0 =A0Debug.Print PostalCode 'zip code if US address
> =A0 =A0 =A0 =A0Debug.Print Latitude, Longitude
> =A0 =A0Else
> =A0 =A0 =A0MsgBox "Status Code: =A0" & StatusCode & vbCrLf _
> =A0 =A0 =A0 & "No information was found for" & vbCrLf & addr, _
> =A0 =A0 =A0 vbInformation, "Address Was Not Found"
> =A0 =A0End If
>
> End Sub
>
>
>
> Option Compare Database
> Option Explicit
>
> Const Map_Api_Key =3D "yourKeyHere"
> Private m_Xml As String
> Private doc As MSXML2.DOMDocument
> 'Private doc As Object
>
> Public Property Get RawXML() As String
> =A0 =A0RawXML =3D m_Xml
> End Property
>
> Public Property Get CountryNameCode() As String
> =A0 =A0CountryNameCode =3D
> GetNodeText("//Placemark/AddressDetails/Country/CountryNameC ode")
> End Property
>
> Public Property Get Address() As String
> =A0 =A0Address =3D GetNodeText("//Placemark/address")
> End Property
>
> Public Property Get AdministrativeAreaName() As String
> =A0 =A0AdministrativeAreaName =3D _
>
> GetNodeText("//Placemark/AddressDetails/Country/Administrati veArea/Adminis=
t=ADrativeAreaName")
> End Property
>
> Public Property Get SubAdministrativeAreaName() As String
> =A0 =A0SubAdministrativeAreaName =3D _
>
> GetNodeText("//Placemark/AddressDetails/Country/Administrati veArea/SubAdmi=
n=ADistrativeArea/SubAdministrativeAreaName")
> End Property
>
> Public Property Get PostalCode() As String
> =A0 =A0PostalCode =3D _
>
> GetNodeText("//Placemark/AddressDetails/Country/Administrati veArea/SubAdmi=
n=ADistrativeArea/PostalCode/PostalCodeNumber")
> End Property
>
> Public Property Get StatusCode() As String
> =A0 =A0StatusCode =3D GetNodeText("//Status/code")
> End Property
>
> Public Property Get HasData() As Boolean
> =A0 =A0HasData =3D StatusCode =3D "200"
> End Property
>
> Public Property Get Coordinates() As String
> =A0 =A0Coordinates =3D GetNodeText("//Placemark/Point/coordinates")
> End Property
>
> Public Property Get Latitude() As String
> =A0 =A0Latitude =3D Split(Coordinates, ",")(1)
> End Property
>
> Public Property Get Longitude() As String
> =A0 =A0Longitude =3D Split(Coordinates, ",")(0)
> End Property
>
> Public Function MakeGeoRequest(Address As String) As Boolean
> =A0 =A0Dim msXml As MSXML2.XMLHTTP
> =A0 =A0Set msXml =3D New MSXML2.XMLHTTP
> =A0 =A0'Dim msXml As Object
> =A0 =A0'Set msXml =3D CreateObject("Microsoft.XMLHTTP")
>
> =A0 =A0On Error GoTo errHandler
>
> =A0 =A0msXml.Open "GET", "http://maps.google.com/maps/geo?q=3D" & Address =
&
> "&output=3Dxml&key=3D" & Map_Api_Key, False
> =A0 =A0msXml.setRequestHeader "Content-Type", "text/html"
> =A0 =A0msXml.send
>
> =A0 =A0'Set doc =3D New MSXML2.DOMDocument
> =A0 =A0Set doc =3D CreateObject("MSXML2.DOMDocument")
> =A0 =A0m_Xml =3D msXml.ResponseText
> =A0 =A0doc.loadXML m_Xml
>
> =A0 =A0MakeGeoRequest =3D True
> =A0 =A0Exit Function
>
> errHandler:
> =A0 =A0'return false
> End Function
>
> Private Function GetNodeText(path As String) As String
> =A0 =A0Dim n As IXMLDOMNode
> =A0 =A0'Dim n As Object
> =A0 =A0Set n =3D doc.selectSingleNode(path)
> =A0 =A0If Not n Is Nothing Then
> =A0 =A0 =A0GetNodeText =3D n.Text
> =A0 =A0End If
> End Function
>
>

Re: Consume Geodata web service

rkc,
Thanks again! It took seconds to get this up and running. This is a
huge help!
I created a reference to "Microsoft XML, v.5.0" and it works
beautifully.

Do you know if there's a way to do this with late binding, eliminating
the need to create the reference for distributed applications?
lq


On Jan 4, 1:15=A0am, rkc wrote:
> Sub Test_GoogleGeoRequest()
> =A0 =A0Dim addr As String
>
> =A0 =A0addr =3D "11 Place Antonin Poncet, Lyon, FR"
> =A0 =A0'addr =3D "5 High Street, Galway, IE"
> =A0 =A0'addr =3D "41 Mill St,Marion,NY"
> =A0 =A0'addr =3D "Stanley St,Belleville,ON"
>
> =A0 =A0If Not MakeGeoRequest(addr) Then
> =A0 =A0 =A0MsgBox "Request failed", vbCritical, "F'd Up Request"
> =A0 =A0 =A0Exit Sub
> =A0 =A0End If
>
> =A0 =A0If HasData Then
> =A0 =A0 =A0 =A0Debug.Print Address 'formatted address
> =A0 =A0 =A0 =A0Debug.Print CountryNameCode
> =A0 =A0 =A0 =A0Debug.Print AdministrativeAreaName =A0'state name if US add=
ress
> =A0 =A0 =A0 =A0Debug.Print SubAdministrativeAreaName 'county name if US ad=
dress
> =A0 =A0 =A0 =A0Debug.Print PostalCode 'zip code if US address
> =A0 =A0 =A0 =A0Debug.Print Latitude, Longitude
> =A0 =A0Else
> =A0 =A0 =A0MsgBox "Status Code: =A0" & StatusCode & vbCrLf _
> =A0 =A0 =A0 & "No information was found for" & vbCrLf & addr, _
> =A0 =A0 =A0 vbInformation, "Address Was Not Found"
> =A0 =A0End If
>
> End Sub
>
>
>
> Option Compare Database
> Option Explicit
>
> Const Map_Api_Key =3D "yourKeyHere"
> Private m_Xml As String
> Private doc As MSXML2.DOMDocument
> 'Private doc As Object
>
> Public Property Get RawXML() As String
> =A0 =A0RawXML =3D m_Xml
> End Property
>
> Public Property Get CountryNameCode() As String
> =A0 =A0CountryNameCode =3D
> GetNodeText("//Placemark/AddressDetails/Country/CountryNameC ode")
> End Property
>
> Public Property Get Address() As String
> =A0 =A0Address =3D GetNodeText("//Placemark/address")
> End Property
>
> Public Property Get AdministrativeAreaName() As String
> =A0 =A0AdministrativeAreaName =3D _
>
> GetNodeText("//Placemark/AddressDetails/Country/Administrati veArea/Adminis=
t=ADrativeAreaName")
> End Property
>
> Public Property Get SubAdministrativeAreaName() As String
> =A0 =A0SubAdministrativeAreaName =3D _
>
> GetNodeText("//Placemark/AddressDetails/Country/Administrati veArea/SubAdmi=
n=ADistrativeArea/SubAdministrativeAreaName")
> End Property
>
> Public Property Get PostalCode() As String
> =A0 =A0PostalCode =3D _
>
> GetNodeText("//Placemark/AddressDetails/Country/Administrati veArea/SubAdmi=
n=ADistrativeArea/PostalCode/PostalCodeNumber")
> End Property
>
> Public Property Get StatusCode() As String
> =A0 =A0StatusCode =3D GetNodeText("//Status/code")
> End Property
>
> Public Property Get HasData() As Boolean
> =A0 =A0HasData =3D StatusCode =3D "200"
> End Property
>
> Public Property Get Coordinates() As String
> =A0 =A0Coordinates =3D GetNodeText("//Placemark/Point/coordinates")
> End Property
>
> Public Property Get Latitude() As String
> =A0 =A0Latitude =3D Split(Coordinates, ",")(1)
> End Property
>
> Public Property Get Longitude() As String
> =A0 =A0Longitude =3D Split(Coordinates, ",")(0)
> End Property
>
> Public Function MakeGeoRequest(Address As String) As Boolean
> =A0 =A0Dim msXml As MSXML2.XMLHTTP
> =A0 =A0Set msXml =3D New MSXML2.XMLHTTP
> =A0 =A0'Dim msXml As Object
> =A0 =A0'Set msXml =3D CreateObject("Microsoft.XMLHTTP")
>
> =A0 =A0On Error GoTo errHandler
>
> =A0 =A0msXml.Open "GET", "http://maps.google.com/maps/geo?q=3D" & Address =
&
> "&output=3Dxml&key=3D" & Map_Api_Key, False
> =A0 =A0msXml.setRequestHeader "Content-Type", "text/html"
> =A0 =A0msXml.send
>
> =A0 =A0'Set doc =3D New MSXML2.DOMDocument
> =A0 =A0Set doc =3D CreateObject("MSXML2.DOMDocument")
> =A0 =A0m_Xml =3D msXml.ResponseText
> =A0 =A0doc.loadXML m_Xml
>
> =A0 =A0MakeGeoRequest =3D True
> =A0 =A0Exit Function
>
> errHandler:
> =A0 =A0'return false
> End Function
>
> Private Function GetNodeText(path As String) As String
> =A0 =A0Dim n As IXMLDOMNode
> =A0 =A0'Dim n As Object
> =A0 =A0Set n =3D doc.selectSingleNode(path)
> =A0 =A0If Not n Is Nothing Then
> =A0 =A0 =A0GetNodeText =3D n.Text
> =A0 =A0End If
> End Function
>
>

Re: Consume Geodata web service

rkc,
Sorry on the late binding question, my fingers work fasster than my
brain. I see you already did this. I removed the reference to the
Microsoft XML DLL and uncommented out your late binding methods and it
works beautifully, with our without the DLL.
Thanks again,
lq

On Jan 4, 1:15=A0am, rkc wrote:
> Sub Test_GoogleGeoRequest()
> =A0 =A0Dim addr As String
>
> =A0 =A0addr =3D "11 Place Antonin Poncet, Lyon, FR"
> =A0 =A0'addr =3D "5 High Street, Galway, IE"
> =A0 =A0'addr =3D "41 Mill St,Marion,NY"
> =A0 =A0'addr =3D "Stanley St,Belleville,ON"
>
> =A0 =A0If Not MakeGeoRequest(addr) Then
> =A0 =A0 =A0MsgBox "Request failed", vbCritical, "F'd Up Request"
> =A0 =A0 =A0Exit Sub
> =A0 =A0End If
>
> =A0 =A0If HasData Then
> =A0 =A0 =A0 =A0Debug.Print Address 'formatted address
> =A0 =A0 =A0 =A0Debug.Print CountryNameCode
> =A0 =A0 =A0 =A0Debug.Print AdministrativeAreaName =A0'state name if US add=
ress
> =A0 =A0 =A0 =A0Debug.Print SubAdministrativeAreaName 'county name if US ad=
dress
> =A0 =A0 =A0 =A0Debug.Print PostalCode 'zip code if US address
> =A0 =A0 =A0 =A0Debug.Print Latitude, Longitude
> =A0 =A0Else
> =A0 =A0 =A0MsgBox "Status Code: =A0" & StatusCode & vbCrLf _
> =A0 =A0 =A0 & "No information was found for" & vbCrLf & addr, _
> =A0 =A0 =A0 vbInformation, "Address Was Not Found"
> =A0 =A0End If
>
> End Sub
>
>
>
> Option Compare Database
> Option Explicit
>
> Const Map_Api_Key =3D "yourKeyHere"
> Private m_Xml As String
> Private doc As MSXML2.DOMDocument
> 'Private doc As Object
>
> Public Property Get RawXML() As String
> =A0 =A0RawXML =3D m_Xml
> End Property
>
> Public Property Get CountryNameCode() As String
> =A0 =A0CountryNameCode =3D
> GetNodeText("//Placemark/AddressDetails/Country/CountryNameC ode")
> End Property
>
> Public Property Get Address() As String
> =A0 =A0Address =3D GetNodeText("//Placemark/address")
> End Property
>
> Public Property Get AdministrativeAreaName() As String
> =A0 =A0AdministrativeAreaName =3D _
>
> GetNodeText("//Placemark/AddressDetails/Country/Administrati veArea/Adminis=
t=ADrativeAreaName")
> End Property
>
> Public Property Get SubAdministrativeAreaName() As String
> =A0 =A0SubAdministrativeAreaName =3D _
>
> GetNodeText("//Placemark/AddressDetails/Country/Administrati veArea/SubAdmi=
n=ADistrativeArea/SubAdministrativeAreaName")
> End Property
>
> Public Property Get PostalCode() As String
> =A0 =A0PostalCode =3D _
>
> GetNodeText("//Placemark/AddressDetails/Country/Administrati veArea/SubAdmi=
n=ADistrativeArea/PostalCode/PostalCodeNumber")
> End Property
>
> Public Property Get StatusCode() As String
> =A0 =A0StatusCode =3D GetNodeText("//Status/code")
> End Property
>
> Public Property Get HasData() As Boolean
> =A0 =A0HasData =3D StatusCode =3D "200"
> End Property
>
> Public Property Get Coordinates() As String
> =A0 =A0Coordinates =3D GetNodeText("//Placemark/Point/coordinates")
> End Property
>
> Public Property Get Latitude() As String
> =A0 =A0Latitude =3D Split(Coordinates, ",")(1)
> End Property
>
> Public Property Get Longitude() As String
> =A0 =A0Longitude =3D Split(Coordinates, ",")(0)
> End Property
>
> Public Function MakeGeoRequest(Address As String) As Boolean
> =A0 =A0Dim msXml As MSXML2.XMLHTTP
> =A0 =A0Set msXml =3D New MSXML2.XMLHTTP
> =A0 =A0'Dim msXml As Object
> =A0 =A0'Set msXml =3D CreateObject("Microsoft.XMLHTTP")
>
> =A0 =A0On Error GoTo errHandler
>
> =A0 =A0msXml.Open "GET", "http://maps.google.com/maps/geo?q=3D" & Address =
&
> "&output=3Dxml&key=3D" & Map_Api_Key, False
> =A0 =A0msXml.setRequestHeader "Content-Type", "text/html"
> =A0 =A0msXml.send
>
> =A0 =A0'Set doc =3D New MSXML2.DOMDocument
> =A0 =A0Set doc =3D CreateObject("MSXML2.DOMDocument")
> =A0 =A0m_Xml =3D msXml.ResponseText
> =A0 =A0doc.loadXML m_Xml
>
> =A0 =A0MakeGeoRequest =3D True
> =A0 =A0Exit Function
>
> errHandler:
> =A0 =A0'return false
> End Function
>
> Private Function GetNodeText(path As String) As String
> =A0 =A0Dim n As IXMLDOMNode
> =A0 =A0'Dim n As Object
> =A0 =A0Set n =3D doc.selectSingleNode(path)
> =A0 =A0If Not n Is Nothing Then
> =A0 =A0 =A0GetNodeText =3D n.Text
> =A0 =A0End If
> End Function
>
>

Re: Consume Geodata web service

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

Public Property Get PostalCode() As String
PostalCode =3D _
GetNodeText("//Placemark/AddressDetails/Country/Administrati veArea/
SubAdministrativeArea//PostalCode/PostalCodeNumber")
End Property



On Jan 4, 1:15=A0am, rkc wrote:
> Sub Test_GoogleGeoRequest()
> =A0 =A0Dim addr As String
>
> =A0 =A0addr =3D "11 Place Antonin Poncet, Lyon, FR"
> =A0 =A0'addr =3D "5 High Street, Galway, IE"
> =A0 =A0'addr =3D "41 Mill St,Marion,NY"
> =A0 =A0'addr =3D "Stanley St,Belleville,ON"
>
> =A0 =A0If Not MakeGeoRequest(addr) Then
> =A0 =A0 =A0MsgBox "Request failed", vbCritical, "F'd Up Request"
> =A0 =A0 =A0Exit Sub
> =A0 =A0End If
>
> =A0 =A0If HasData Then
> =A0 =A0 =A0 =A0Debug.Print Address 'formatted address
> =A0 =A0 =A0 =A0Debug.Print CountryNameCode
> =A0 =A0 =A0 =A0Debug.Print AdministrativeAreaName =A0'state name if US add=
ress
> =A0 =A0 =A0 =A0Debug.Print SubAdministrativeAreaName 'county name if US ad=
dress
> =A0 =A0 =A0 =A0Debug.Print PostalCode 'zip code if US address
> =A0 =A0 =A0 =A0Debug.Print Latitude, Longitude
> =A0 =A0Else
> =A0 =A0 =A0MsgBox "Status Code: =A0" & StatusCode & vbCrLf _
> =A0 =A0 =A0 & "No information was found for" & vbCrLf & addr, _
> =A0 =A0 =A0 vbInformation, "Address Was Not Found"
> =A0 =A0End If
>
> End Sub
>
>
>
> Option Compare Database
> Option Explicit
>
> Const Map_Api_Key =3D "yourKeyHere"
> Private m_Xml As String
> Private doc As MSXML2.DOMDocument
> 'Private doc As Object
>
> Public Property Get RawXML() As String
> =A0 =A0RawXML =3D m_Xml
> End Property
>
> Public Property Get CountryNameCode() As String
> =A0 =A0CountryNameCode =3D
> GetNodeText("//Placemark/AddressDetails/Country/CountryNameC ode")
> End Property
>
> Public Property Get Address() As String
> =A0 =A0Address =3D GetNodeText("//Placemark/address")
> End Property
>
> Public Property Get AdministrativeAreaName() As String
> =A0 =A0AdministrativeAreaName =3D _
>
> GetNodeText("//Placemark/AddressDetails/Country/Administrati veArea/Adminis=
t=ADrativeAreaName")
> End Property
>
> Public Property Get SubAdministrativeAreaName() As String
> =A0 =A0SubAdministrativeAreaName =3D _
>
> GetNodeText("//Placemark/AddressDetails/Country/Administrati veArea/SubAdmi=
n=ADistrativeArea/SubAdministrativeAreaName")
> End Property
>
> Public Property Get PostalCode() As String
> =A0 =A0PostalCode =3D _
>
> GetNodeText("//Placemark/AddressDetails/Country/Administrati veArea/SubAdmi=
n=ADistrativeArea/PostalCode/PostalCodeNumber")
> End Property
>
> Public Property Get StatusCode() As String
> =A0 =A0StatusCode =3D GetNodeText("//Status/code")
> End Property
>
> Public Property Get HasData() As Boolean
> =A0 =A0HasData =3D StatusCode =3D "200"
> End Property
>
> Public Property Get Coordinates() As String
> =A0 =A0Coordinates =3D GetNodeText("//Placemark/Point/coordinates")
> End Property
>
> Public Property Get Latitude() As String
> =A0 =A0Latitude =3D Split(Coordinates, ",")(1)
> End Property
>
> Public Property Get Longitude() As String
> =A0 =A0Longitude =3D Split(Coordinates, ",")(0)
> End Property
>
> Public Function MakeGeoRequest(Address As String) As Boolean
> =A0 =A0Dim msXml As MSXML2.XMLHTTP
> =A0 =A0Set msXml =3D New MSXML2.XMLHTTP
> =A0 =A0'Dim msXml As Object
> =A0 =A0'Set msXml =3D CreateObject("Microsoft.XMLHTTP")
>
> =A0 =A0On Error GoTo errHandler
>
> =A0 =A0msXml.Open "GET", "http://maps.google.com/maps/geo?q=3D" & Address =
&
> "&output=3Dxml&key=3D" & Map_Api_Key, False
> =A0 =A0msXml.setRequestHeader "Content-Type", "text/html"
> =A0 =A0msXml.send
>
> =A0 =A0'Set doc =3D New MSXML2.DOMDocument
> =A0 =A0Set doc =3D CreateObject("MSXML2.DOMDocument")
> =A0 =A0m_Xml =3D msXml.ResponseText
> =A0 =A0doc.loadXML m_Xml
>
> =A0 =A0MakeGeoRequest =3D True
> =A0 =A0Exit Function
>
> errHandler:
> =A0 =A0'return false
> End Function
>
> Private Function GetNodeText(path As String) As String
> =A0 =A0Dim n As IXMLDOMNode
> =A0 =A0'Dim n As Object
> =A0 =A0Set n =3D doc.selectSingleNode(path)
> =A0 =A0If Not n Is Nothing Then
> =A0 =A0 =A0GetNodeText =3D n.Text
> =A0 =A0End If
> End Function
>
>

Re: Consume Geodata web service

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/Administrati veArea/
> SubAdministrativeArea//PostalCode/PostalCodeNumber")
> End Property

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

Re: Consume Geodata web service

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/Administrati veArea/
> 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.

Re: Consume Geodata web service

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

AS

DECLARE @XMLDoc varchar(2000)
SET @XMLDoc =3D ' >

3

South Pasadena
34.1161196
-118.1503488
5397717
US
United States
P
PPL
city, village,...
populated place
24994

201
CA
California
037
Los Angeles County
America/Los_Angeles timezone>

'

-- Create an internal representation of the XML document:

EXEC sp_xml_preparedocument @iXMLDoc OUTPUT, @XMLDoc

-- Execute a SELECT statement using OPENXML rowset provider:

SELECT *
FROM
OPENXML (@iXMLDoc, '/geonames/geoname',2)
WITH (
geonameId varchar(10),
name varchar(200),
adminName1 varchar(60),
adminCode1 varchar(10),
adminName2 varchar(60),
adminCode2 varchar(10),
lat varchar(200),
lng varchar(200),
population varchar(200),
elevation varchar(200),
fcode varchar(10)
)

-- Remove the internal representation of the XML document:

EXEC sp_xml_removedocument @iXMLDoc


Example that DOESN'T work:

ALTER PROCEDURE myXMLSprocName
@XMLDoc varchar(2000)

AS

..


On Jan 5, 11:02=A0am, rkc wrote:
> Lauren Quantrell wrote:
> > There's an error in the posted code:
> > Corrected as follows:
>
> > Public Property Get PostalCode() As String
> > =A0 =A0 PostalCode =3D _
> > =A0 =A0 GetNodeText("//Placemark/AddressDetails/Country/Administrati veAr=
ea/
> > 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.

Re: Consume Geodata web service

On Jan 5, 11:02=A0am, rkc wrote:
> Lauren Quantrell wrote:
> > There's an error in the posted code:
> > Corrected as follows:
>
> > Public Property Get PostalCode() As String
> > =A0 =A0 PostalCode =3D _
> > =A0 =A0 GetNodeText("//Placemark/AddressDetails/Country/Administrati veAr=
ea/
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.

Re: Consume Geodata web service

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.

Re: Consume Geodata web service

On Jan 7, 5:35 pm, Lauren Quantrell
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.

Re: Consume Geodata web service

lyle wrote:
> On Jan 7, 5:35 pm, Lauren Quantrell
> 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.

Re: Consume Geodata web service

rkc wrote in
news:4782cdc2$0$5191$4c368faf@roadrunner.com:

> lyle wrote:
>> On Jan 7, 5:35 pm, Lauren Quantrell
>> 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?

the sproc:

*****************************

ALTER PROCEDURE StoredProcedure2
@SomeString varchar(2000)
AS
SELECT @SomeString
RETURN

*****************************

the module:

*****************************

Sub temp()
Dim m As ADODB.Command
Dim p As ADODB.Parameter
Dim r As ADODB.Recordset

Set m = New ADODB.Command
Set r = New ADODB.Recordset

Dim msXml As Object
Set msXml = CreateObject("Microsoft.XMLHTTP")

With msXml
.Open "GET",
"http://maps.google.com/maps/geo?q=11%20Place%20Antonin%20Po ncet,%20L
yon,%20FR&output=XML&Key=ABQIAAAAIVR6w0F1D6_5ACjh8cfCAxRih5r 4htKrFgk6
952vfhjjxQ666xRcMbDkfB3YAfbB_0YTb2twdVaJ6g", False .setRequestHeader
"Content-Type", "text/html" .send
End With

With m
.ActiveConnection = CurrentProject.Connection
.CommandType = adCmdStoredProc
.CommandText = "StoredProcedure2"
Set p = .CreateParameter("@SomeString", adVarChar, adParamInput,
2000, msXml.ResponseText) .Parameters.Append p
End With

With r
.Open m
End With

Debug.Print r(0)

End Sub

*****************************

prints

{"name":"11 Place Antonin Poncet, Lyon,
FR","Status":{"code":610,"request":"geocode"}}

*****************************

TTBOMK this what Google returns when an request is rejected.
But, I'm not familiar enough with this to make any definite opinion.

There may be other ways to get this information from within Access. One
is to use the WebBrowser Control and its document properties. That gets
specific information but I've never tried to extract XML with it.

Re: Consume Geodata web service

lyle fairfield wrote:
> rkc wrote in
> news:4782cdc2$0$5191$4c368faf@roadrunner.com:
>
>> lyle wrote:
>>> On Jan 7, 5:35 pm, Lauren Quantrell
>>> 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.

Re: Consume Geodata web service

On Jan 7, 9:15 pm, rkc wrote:
> lyle fairfield wrote:
> > rkc wrote in
> >news:4782cdc2$0$5191$4c368faf@roadrunner.com:
>
> >> lyle wrote:
> >>> On Jan 7, 5:35 pm, Lauren Quantrell
> >>> 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?

Re: Consume Geodata web service

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

Re: Consume Geodata web service

Google error response:
-- (610) G_GEO_BAD_KEY The given key is either invalid or does not
match the domain for which it was given.


On Jan 7, 9:01=A0pm, lyle fairfield wrote:
> rkc wrote innews:4782cdc2$0$5191$4c368faf@=
roadrunner.com:
>
>
>
>
>
> > lyle wrote:
> >> On Jan 7, 5:35 pm, Lauren Quantrell
> >> 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? =A0When=

> >> 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
> >> =A0{"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=3Dxml
>
> > 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?
>
> the sproc:
>
> *****************************
>
> ALTER PROCEDURE StoredProcedure2
> =A0@SomeString varchar(2000)
> AS
> =A0 =A0 =A0 =A0 SELECT @SomeString
> =A0 =A0 =A0 =A0 RETURN
>
> *****************************
>
> the module:
>
> *****************************
>
> Sub temp()
> Dim m As ADODB.Command
> Dim p As ADODB.Parameter
> Dim r As ADODB.Recordset
>
> Set m =3D New ADODB.Command
> Set r =3D New ADODB.Recordset
>
> Dim msXml As Object
> Set msXml =3D CreateObject("Microsoft.XMLHTTP")
>
> With msXml
> =A0 =A0 .Open "GET",
> =A0 =A0 "http://maps.google.com/maps/geo?q=3D11%20Place%20Antonin%20 Poncet=
,%20L
> =A0 =A0 yon,%20FR&output=3DXML&Key=3DABQIAAAAIVR6w0F1D6_5ACjh8cfCAxR ih5r4h=
tKrFgk6
> =A0 =A0 952vfhjjxQ666xRcMbDkfB3YAfbB_0YTb2twdVaJ6g", False .setRequestHead=
er
> =A0 =A0 "Content-Type", "text/html" .send
> End With
>
> With m
> =A0 =A0 .ActiveConnection =3D CurrentProject.Connection
> =A0 =A0 .CommandType =3D adCmdStoredProc
> =A0 =A0 .CommandText =3D "StoredProcedure2"
> =A0 =A0 Set p =3D .CreateParameter("@SomeString", adVarChar, adParamInput,=

> =A0 =A0 2000, msXml.ResponseText) .Parameters.Append p
> End With
>
> With r
> =A0 =A0 .Open m
> End With
>
> Debug.Print r(0)
>
> End Sub
>
> *****************************
>
> prints
>

> {"name":"11 Place Antonin Poncet, Lyon,
> FR","Status":{"code":610,"request":"geocode"}}
>
> *****************************
>
> TTBOMK this what Google returns when an request is rejected.
> But, I'm not familiar enough with this to make any definite opinion.
>
> There may be other ways to get this information from within Access. One
> is to use the WebBrowser Control and its document properties. That gets
> specific information but I've never tried to extract XML with it.- Hide qu=
oted text -
>
> - Show quoted text -

Re: Consume Geodata web service

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=A0pm, rkc wrote:
> lyle fairfield wrote:
> > rkc wrote in
> >news:4782cdc2$0$5191$4c368faf@roadrunner.com:
>
> >> lyle wrote:
> >>> On Jan 7, 5:35 pm, Lauren Quantrell
> >>> 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? =A0Whe=
n
> >>> 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
> >>> =A0{"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=3Dxml
>
> >> 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 te=
xt -
>
> - Show quoted text -

Re: Consume Geodata web service

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.

Re: Consume Geodata web service

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 !


On Jan 7, 10:09=A0pm, rkc wrote:
> 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.

Re: Consume Geodata web service

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 !

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.

Re: Consume Geodata web service

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 !
>
> 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.

Re: Consume Geodata web service

ah, perfect. Thanks again!