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.