Go to Top

Map route on Yahoo Maps with Addresses in Access

Yahoo maps is great at mapping out a route you entered through the browser, but what if you need to map the addresses using a pre-defined route in Access? This tip will show you how to build the URL on the fly and then pass it along to the default browser on your PC. Note: for the same technique using Google maps please visit here.

Here’s the code:

Private Sub cmdGoogleMap_Click()
Dim strHyperlink As String
Dim Company As clsCompanyInfo
Dim rs As ADODB.Recordset
Dim strSQL As String
Dim i As Byte

‘Starting address in this case is the address of the company, just replace the code with whatever address you wish, here I use the Company class to get the company’s address.
Set Company = New clsCompanyInfo
With Company
strHyperlink = “http://maps.yahoo.com/dd#mvt=m&q1=” & .Address & “, ” & .City & “, ” & Val(.Zip)
End With

‘Now I need to get the list of addresses for the route, in this case it’s CrewID defined on my firm and for a particular day, just replace strSQL with your own sql code to fetch the addresses you need to map:

strSQL = “SELECT Address, City, State, Zip ” & _
“FROM Customers INNER JOIN jobs ON Customers.CustomerID = Jobs.CustomerID ” & _
“WHERE JobDate = ‘” & Me.txtScheduleDate & “‘ And Jobs.CrewID = ” & Me.cboMapCrew & ” Order By Jobs.Position”
‘OpenMyRecordset is a custom procedure I use to open all of my ADODB recordsets using a default static cursor, replace with your code to open the cursor:
OpenMyRecordset rs, strSQL
i = 2
With rs
Do While .EOF = False
strHyperlink = strHyperlink & IIf(IsNull(!Address), “”, “&q” & i & “=” & !Address & “,” & !State & “,” & Val(!Zip))
i = i + 1
.MoveNext
Loop
End With

Application.FollowHyperlink (strHyperlink)
Set rs = Nothing

End Sub

Route optimization is a different matter all together
As of this writing there is no way to due route optimization for free that I’m aware of, usually you have to pay for a web service that does that.

About Juan Soto

Juan Soto is a Senior Access Developer at IT Impact Inc. and a Microsoft Access MVP. He specializes in Access with SQL Server databases. His passion for Access has led him to helping a wide range of businesses in helping them establish a secure, stable and efficient environment with SQL Server. He's a frequent speaker at Access user groups nationwide and recently spoke at the Orange County SQL Saturday # 73. If you wish to have Juan speak at your next group meeting you can contact him here.

3 Responses to "Map route on Yahoo Maps with Addresses in Access"

  • Hans
    September 27, 2013 - 6:43 pm Reply

    Your article for maps with google does not show completely an I have no possibility to comment there. Please fix.

    • Ben Clothier
      September 27, 2013 - 7:23 pm Reply

      Hans,

      Thanks for letting us know — we fixed the link so it should now work.

  • Isabella
    May 30, 2013 - 6:34 am Reply

    Woah! I’m really digging the template/theme of this blog. It’s simple, yet effective.
    A lot of times it’s tough to get that “perfect balance” between superb usability and appearance. I must say you have done a very good job with this. Also, the blog loads super fast for me on Firefox. Outstanding Blog!

Leave a Reply

Your email address will not be published. Required fields are marked *

 

Contact Us
  • This field is for validation purposes and should be left unchanged.