UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Custom Search
Edit Discussion
> Route Google Map From Access    
Route Google Map From Access

Sometimes you need to display a google map with a route that has already been defined in Access 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:

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 form 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

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.

Edit Discussion
Custom Search
Thank you for your support!
This page has been accessed 5,847 times.  This page was last modified 01:14, 7 February 2012 by Jack Leach. Contributions by Alan Greenwood and JuanSoto  Disclaimers