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 http://accessexperts.net/show_news.php?id=79&user=Website.

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.