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

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.google.com/maps?f=d&hl=en&saddr=" & .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 = 1
    With rs
        Do While .EOF = False
            If i = 1 Then  'The second address requires specific syntax
                strHyperlink = strHyperlink & "&daddr=" & !Address & "," & !City & "," & !Zip
            Else
                strHyperlink = strHyperlink & "+to:" & !Address & "," & !City & "," & !Zip
            End If
            i = i + 1
            .MoveNext
        Loop
    End With
   
    Application.FollowHyperlink (strHyperlink)
    Set rs = Nothing

End Sub

That's all there is to it, if you need the same code but for Yahoo maps, please click here:
http://accessexperts.net/show_news.php?id=80&user=Website

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.