How can I get Yahoo Geocodes into Access?

Hi Eric,

It may be very easy....I simply don't know at this point. I do know that I'd
first need to sign up for an account, in order to throughly test any
converted code.

In the meantime, I went ahead and plugged in some phony latitude and
longitude numbers, into the three records, and was able to run the update
query as planned.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/ex...tributors.html
__________________________________________

"efandango" wrote:

ok,

in my naivety i assumed it would not be too difficult, thanks for taking the
time to look at this for me.

regards

Eric

Tempting as the update query scenario is for me personally, I am mindful that
eventually other users will use this database, and will not want to maintain
the data in Excel as well as Access, so it makes sense to use the already
exisitng data in Access, which is why I was so keen to translate that
essential procedure into something that would work in Access, I am not a
seasoned VBA coder, and as such do not know or understand all of the various
keywords/functions that are available to me in VBA; but somehow, I'm going to
try my best to make it happen.

regards


Eric



"Tom Wickerath" wrote:

Hi Eric,

It may be very easy....I simply don't know at this point. I do know that I'd
first need to sign up for an account, in order to throughly test any
converted code.

In the meantime, I went ahead and plugged in some phony latitude and
longitude numbers, into the three records, and was able to run the update
query as planned.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/ex...tributors.html
__________________________________________

"efandango" wrote:

ok,

in my naivety i assumed it would not be too difficult, thanks for taking the
time to look at this for me.

regards

Eric

Hi Eric,

...and will not want to maintain the data in Excel as well as Access...

You would not need to do this. You'd simply use Excel whenever you needed to
get new latitude and longitude data. There's no reason to have to maintain
the data in Excel, once the latitude, longitude and precision data is brought
into the database.

I am not a seasoned VBA coder, and as such do not know or understand
all of the various keywords/functions that are available to me in VBA...

The Me keyword in Access VBA is a reference to a form or report. The default
property, if not specified, is the name of the object (ie. the name of the
form or report). Therefore, this keyword is not valid in a stand-alone module
(a module that is not associated with a form or report).

In general, you can select a keyword in the VBE (Visual Basic Editor), and
press F1 to open context-sensitive help on the keyword. Make sure to include
Option Explicit as the second line of code in all modules:

Always Use Option Explicit
http://www.access.qbuilt.com/html/ge...tml#VBEOptions

In fact, the author of the sample Excel spreadsheet did not do this, and the
code does not compile properly if one inserts these two very important words
into the existing Excel VBA code.

When working with VBA code, it is good to get in the habit of compiling your
code on a fairly frequent basis. This way, you catch any compile-time errors
as you are working, instead of letting them build up. I also recommend that
you remove the default checks in the VBE options for Background compile and
Compile on demand.

Here are a few resources to get you going on using VBA code:

http://www.seattleaccess.org/downloads.htm
Look for this download:
DAO - Back To Basics Compilation/Demo
by Tom Wickerath, Jan/Feb 2007 --
Download (448 kb) App and Word Doc

and

Access Basics
http://www.accessmvp.com/Strive4Peace/Index.htm


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/ex...tributors.html
__________________________________________

"efandango" wrote:

Tempting as the update query scenario is for me personally, I am mindful that
eventually other users will use this database, and will not want to maintain
the data in Excel as well as Access, so it makes sense to use the already
exisitng data in Access, which is why I was so keen to translate that
essential procedure into something that would work in Access, I am not a
seasoned VBA coder, and as such do not know or understand all of the various
keywords/functions that are available to me in VBA; but somehow, I'm going to
try my best to make it happen.

regards


Eric

Tom,

Appreciate the feedback on this problem, and the comments about not having
to maintain Excel. There were other reasons why I wanted to keep it all
within Access. Witht that in mind, as I said, I persevered (albeit
blindly...), and I actually got it to return a correct geocode on a single
record from my table/query:

I used this procedu

******************
Private Sub btn_Runs_Geocode_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strRun_No As String
Dim lngFN As Long
Dim strFileName As String

lngFN = FreeFile()

strStartRun_No = InputBox("Enter the lower Run No")
strEndRun_No = InputBox("Enter the higher Run No")

strFileName = "Run" & "_" & "Points_" & strStartRun_No & "-" & strEndRun_No

If Len(strStartRun_No) 0 Then
Set db = CurrentDb()
Set qdf = db.QueryDefs("Generate_KML_Points_Groups")
qdf.Parameters("StartRun") = strStartRun_No
qdf.Parameters("EndRun") = strEndRun_No

Set rs = qdf.OpenRecordset(dbOpenDynaset)

Do Until rs.EOF = True
Call YahooAddressLookup(Run_point_Address, Run_Point_Postcode)
'Call sSleep(2000)
rs.MoveNext
Loop

rs.Close

End If

Close #lngFN
MsgBox ("Done")

End Sub

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

That called this function:

*******************
Function YahooAddressLookup(Run_point_Address As String, Run_Point_Postcode
As String) As String


' perform RESTian lookup on Yahoo
Dim response As String

'url =
"http://local.yahooapis.com/MapsService/V1/geocode?appid=efandango&location="
& Run_point_Address & ", " & Run_Point_Postcode & ", London"



'Create Http object
If IsEmpty(http) Then Set http =
CreateObject("WinHttp.WinHttpRequest.5.1")

'Send request To URL
http.Open "GET", url

http.send
'Get response data As a string

response = http.responseText

' capture the latitude by regex matching the values in the tags
geo:lat and geo:long
lat = RegExMatch(response, "Latitude([\.\-0-9]+)/Latitude")
lng = RegExMatch(response, "Longitude([\.\-0-9]+)/Longitude")
precision = RegExMatch(response, "precision=""([a-z0-9+]+)""")

' return a comma delimited string
' if values not found, this will return ","
YahooAddressLookup = lat & "," & lng & "," & precision
If (precision "" And precision "state") Then
[address_latitude] = lat
[address_longitude] = lng
Else
[address_latitude] = "not found"
[address_longitude] = "not found"
End If

End Function
*******************

But for some reason I cannot get it to loop and go get the geocode for the
next record, I guess if i'm honest, I am just stumbling around trying to
conquer this problem while also trying to learn VBA, it's frustrating being
in this chicken and egg situation because when I ask for help, i don't know
enough to provide the right kind of question (ref: my original question), and
I somestimes sound like I know what I'm doing, when in fact I don't really,
most succes so far has been more by accident than design.

regards

Eric






"Tom Wickerath" wrote:

Hi Eric,

...and will not want to maintain the data in Excel as well as Access...

You would not need to do this. You'd simply use Excel whenever you needed to
get new latitude and longitude data. There's no reason to have to maintain
the data in Excel, once the latitude, longitude and precision data is brought
into the database.

I am not a seasoned VBA coder, and as such do not know or understand
all of the various keywords/functions that are available to me in VBA...

The Me keyword in Access VBA is a reference to a form or report. The default
property, if not specified, is the name of the object (ie. the name of the
form or report). Therefore, this keyword is not valid in a stand-alone module
(a module that is not associated with a form or report).

In general, you can select a keyword in the VBE (Visual Basic Editor), and
press F1 to open context-sensitive help on the keyword. Make sure to include
Option Explicit as the second line of code in all modules:

Always Use Option Explicit
http://www.access.qbuilt.com/html/ge...tml#VBEOptions

In fact, the author of the sample Excel spreadsheet did not do this, and the
code does not compile properly if one inserts these two very important words
into the existing Excel VBA code.

When working with VBA code, it is good to get in the habit of compiling your
code on a fairly frequent basis. This way, you catch any compile-time errors
as you are working, instead of letting them build up. I also recommend that
you remove the default checks in the VBE options for Background compile and
Compile on demand.

Here are a few resources to get you going on using VBA code:

http://www.seattleaccess.org/downloads.htm
Look for this download:
DAO - Back To Basics Compilation/Demo
by Tom Wickerath, Jan/Feb 2007 --
Download (448 kb) App and Word Doc

and

Access Basics
http://www.accessmvp.com/Strive4Peace/Index.htm


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/ex...tributors.html
__________________________________________

"efandango" wrote:

Tempting as the update query scenario is for me personally, I am mindful that
eventually other users will use this database, and will not want to maintain
the data in Excel as well as Access, so it makes sense to use the already
exisitng data in Access, which is why I was so keen to translate that
essential procedure into something that would work in Access, I am not a
seasoned VBA coder, and as such do not know or understand all of the various
keywords/functions that are available to me in VBA; but somehow, I'm going to
try my best to make it happen.

regards


Eric

Hi Eric,

Congratulations on the success that you've had so far!

Tell you what....I can go ahead and take a look at your Access application,
if you are willing to send me a compacted and zipped copy by e-mail. I'll
also need the credentials (username / password) to run your code (since I
really don't have the time or energy at this point to establish an account
for myself).

If you are interested, send me a private e-mail message with a valid
reply-to address. My e-mail address is available at the bottom of the
contributor's page indicated below. Please do not post your e-mail address
(or mine) to a newsgroup reply. Doing so will only attract the unwanted
attention of spammers.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/ex...tributors.html
__________________________________________

"efandango" wrote:

Tom,

Appreciate the feedback on this problem, and the comments about not having
to maintain Excel. There were other reasons why I wanted to keep it all
within Access. Witht that in mind, as I said, I persevered (albeit
blindly...), and I actually got it to return a correct geocode on a single
record from my table/query:

snip

Tom,

That is very nice of you to make the offer, which I will gratefully take you
up on. As regards my database I have completely seperated the geocoding
elements into a new DB for you, So there is simply 1 table, 1 Form, 1 Query.
They are very basic rough n ready forms because I was more concerned with
getting the gecoding engine 'looping working'

I have filled you in with the other details you requested in the email that
I have now sent to you

regards

Eric



"Tom Wickerath" wrote:

Hi Eric,

Congratulations on the success that you've had so far!

Tell you what....I can go ahead and take a look at your Access application,
if you are willing to send me a compacted and zipped copy by e-mail. I'll
also need the credentials (username / password) to run your code (since I
really don't have the time or energy at this point to establish an account
for myself).

If you are interested, send me a private e-mail message with a valid
reply-to address. My e-mail address is available at the bottom of the
contributor's page indicated below. Please do not post your e-mail address
(or mine) to a newsgroup reply. Doing so will only attract the unwanted
attention of spammers.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/ex...tributors.html
__________________________________________

"efandango" wrote:

Tom,

Appreciate the feedback on this problem, and the comments about not having
to maintain Excel. There were other reasons why I wanted to keep it all
within Access. Witht that in mind, as I said, I persevered (albeit
blindly...), and I actually got it to return a correct geocode on a single
record from my table/query:

snip