View Full Version : MapPoint in MS Access - Any options for choosing from a selection of routes or recalculating route?

09-11-2013, 10:15 AM
Hi all, a couple of years ago Eric Frost helped me to add MapPoint to an Access database to calculate delivery routes. It's mostly worked flawlessly, but occasionally it comes up with routes that are not the best. I have been asked to try and solve this.

I wondered if there was any way to have Mappoint display a couple of routes to choose from in Access, similar to a gps device, or failing that, just a button to press to recalculate the route?

We have found sometimes that running the code again (through a button on an Access form) has produced different (and better) results.

Any idea's on if/how I can do this? The code I have at the moment is below...

Private Sub btnOpenDelivRpt_Click()

If Nz(Me!txtSelected, 0) = 0 Then
MsgBox "Please select an Order to print", vbOKOnly, "Error"
If MsgBox("Please make sure the right Orders have been selected. This will create a Delivery Route and Orders Report. Continue?", vbOKCancel, "Warning") = vbOK Then

Dim oApp As MapPoint.Application
Set oApp = CreateObject("MapPoint.Application")
oApp.Visible = True 'Or False - chooses whether to display MapPoint or not

Dim omap As MapPoint.Map
Set omap = oApp.ActiveMap

Dim path, szconn As String
path = Application.CurrentDb.Name

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qselOrderRoutePlanner", "c:\temp\temp.xls", True

Dim ods As MapPoint.DataSet
szconn = "c:\temp\temp.xls!qselOrderRoutePlanner"
Set ods = omap.DataSets.ImportData(szconn, , geoCountryUnitedKingdom, , geoImportExcelSheet)

Dim depot As MapPoint.Location
Set depot = omap.FindAddressResults("East Moors Lane", , , , "BH24 2SB", "United Kingdom")(1)

Dim ort As MapPoint.Route
Set ort = omap.ActiveRoute
ort.Waypoints.Add depot, "Start"

Dim rs As MapPoint.Recordset
Set rs = ods.QueryAllRecords

Do While Not rs.EOF
Dim cust As MapPoint.Location
Set cust = omap.FindAddressResults(rs.Fields("Address"), rs.Fields("Town_City"), , rs.Fields("County"), rs.Fields("PostCode"), geoCountryUnitedKingdom)(1)
ort.Waypoints.Add cust, rs.Fields("CustomerNumber")

ort.Waypoints.Add depot, "End"

Dim wyp As MapPoint.Waypoint
Debug.Print "unoptimized"
For Each wyp In ort.Waypoints
Debug.Print wyp.Name


Debug.Print "optimized"
For Each wyp In ort.Waypoints
Debug.Print wyp.Name


Dim sqlcmd As String
sqlcmd = "Delete * from tblRouteOrderResults"

Set db = CurrentDb
db.Execute sqlcmd

Dim i As Integer
For Each wyp In ort.Waypoints

If wyp.Name <> "Start" And wyp.Name <> "End" Then
i = i + 1
sqlcmd = "INSERT INTO tblRouteOrderResults ( CustomerNumber, RouteOrder ) SELECT " & wyp.Name & " AS Expr1, " & i & " AS Expr2;"
db.Execute sqlcmd
End If

oApp.Height = 800
oApp.Width = 600
oApp.ItineraryVisible = False 'directions on top
oApp.PaneState = geoPaneNone 'left side
'omap.DataSets(1).ZoomTo 'zoom in to pushpins

'omap.CopyMap 'this would be to way to do it with Excel
omap.SaveAs "c:\temp\temp.html", geoFormatHTMLMapAndDirections

omap.Saved = True

DoCmd.OpenReport "rptOrderDeliveries", acViewReport

Me.Visible = False

End If

End If
End Sub

Eric Frost
09-11-2013, 05:17 PM
I don't know if you might Optimize twice or Optimize Calculate Optimize again if it's going to come up with different results? I think there is also now a method for reversing the route, you can see if that makes a difference.

So I'm not too optimistic you're going to get different results, but you could try different things and also save the map to a new file and then compare all of them later.

Would you be interested in working via remote desktop to modify the code?