Options for Access export to MapPoint? (1 Viewer)

[Garindan]

Hi all, I have an Access database for customer orders. We would like to output something to Microsoft MapPoint to plan routes for delivery drivers.

Currently I have a form where the database user selects orders to load onto a van, then presses a command button. It outputs to a report for the delivery driver, which lists the orders by postcode.

This is ok but ordered by postcode means the delivery driver sometimes doubles back. So we want to generate a proper route.

So I can just output something from the form in the correct format and import it into MapPoint, or put code on the button to automatically generate something.

I just wondered if anyone has experience with this, or has any idea what would be good to do?

Obviously I have to still give the driver customer details and order details with the generated route.

Many thanks for any help!

 

[gemma-the-husky]

i looked at this a while back, but I am a bit out of date with it now

mappoint (or google) has to take a (series of) postcodes (or lat-longs) If using postcodes, then there is a possibility that some are not detected, and the "true" location is not identified with the correct level of accuracy.

I am not sure whether you can give mappoint a series of postcodes, and ask it to optimise a route.

The nice thing is that all of this is callable within Access. you just need to add a reference to the mappoint library.

I do not think you can use google for free to do this, because the UK postcode database is copyright, and google will only resolve to the "area" postcode, which lacks sufficient accuracy.

mappoint obviously includes licensing for the post-codes. There are other paid-for post-code look up services on the net.

 

[Garindan]

Hi thanks for the reply. Yes MapPoint can generate an optimised route, I have looked into that part. We only have postcodes to use but would be more than happy with that.

The part I feel I will have difficulty with will be the best option to pass those postcodes to MapPoint and how to do that

Any idea's? I can't seem to find much online though I know it can be done.

 

[gemma-the-husky]

as i say - use the mappoint library within access

here's a code snippet I use, to transform an address into a mappable lat/long

[B]dim objSA As Object[/B]
[B]Dim objFR As Object[/B]
[B]Dim point1 As Object[/B]
 
 
'where address is a string consisting of a full address, or just a post code
'this command parses an address into a mappoint object
[B]Set objSA = objMap.ParseStreetAddress(Address)[/B]
 
'this now finds the map location(s) corresponding to that object
[B]Set objFR = objMap.FindAddressResults(objSA.Street, objSA.city, , objSA.region, objSA.PostalCode)[/B]
 
'objfr returns an array of possible matches based on the sdubmitted address
'if the address is vague you can get widely differing geographic results - so you need to interpret the precision of the result.
 
'result quality is a value showing the accuracy of the search.
'I only accept accuracy <=2
'mycoords is my own type of structure storing lat/long etc
 
 
[B]If objFR.ResultsQuality <3 Then[/B]
 
    'use the first returned match (there hopefully will only be one match)  
[B]   Set point1 = objFR.item(1)[/B]
 
[B]   With MyCoords[/B]
[B]       .map_lat = point1.latitude[/B]
[B]       .map_long = point1.longitude[/B]
[B]       .map_precision = objFR.ResultsQuality[/B]
[B]       .map_oktouse = True[/B]
[B]   End With[/B]
[B]end if[/B]

so I can store an array of mycoords, which I can then in due course pass back to mappoint to get a timed route, etc

I did have to spend a fair amount of time puzzling all this out.

note that MS have a useful on line mappoint help resource. There is also a mappoint forum similar to this.


hope this helps

 

[Eric Frost]

So I can just output something from the form in the correct format and import it into MapPoint, or put code on the button to automatically generate something.

Maybe I'm not totally understanding the questions, but MapPoint can import from either an Access Query or Table. With more recent versions of MapPoint if you are doing this repeatedly, it seemed like importing from a query was a little flaky so I have resorted to importing from a table (just have to clear and insert into the table).

Just do something like this:

'table
szConn = objApp.Path & "\MyDatabase.mdb!tblRouteStops"
Set ods = .ImportData(szConn, , geoCountryUnitedStates, , geoImportAccessTable)
'Access database with a query
szConn = objApp.Path & "\MyDatabase!qryRouteStops"
Set ods = .ImportData(szConn, , geoCountryUnitedStates, , geoImportAccessQuery)

Once you get started, if you have any specific questions, please fire away, but in the meantime, here are some general resources:

There are some Access / MapPoint examples on MP2Kmag.com, just search for Access in article titles www DOT mp2kmag DOT com/articles.asp

Also, there are a lot of routing examples in the MapForums Downloads section with full source code, they are in Excel VBA, but after the import from Access, the rest of the code would be the same.

www DOT mapforums DOT com/mapforums-plus-downloads/

hope this helps!

Eric

 

[Garindan]

Thankyou for the replies and I'm very sorry for the slow response! My wife and I have just had a baby which is great. The bad news is I've got to try and get this sorted asap and I'm stuck

I currently have a report which is based on a query. It shows the orders for a delivery, including customer name, address, and order details etc. It currently lists them all ordered by postcode and the delivery driver follows, but postcodes are not always the best route, hence why we want to use mappoint.

The report has to be grouped on the order number so it can show all products ordered (more than one per order), so the underlying query will sometimes have an address/customer etc twice or more as theres one record for each product.

I need to add code to the report so it can generate a route for the orders (using house number, street and postcode) and show the results in the report with the orders. Can this be done?

I don't know where to start Preferably I'd like the report to show the orders/deliveries in the correct order as per the route (sorted by route?) with a map of route included somewhere (at end of report?).

The fields I need to generate a route on are 'House_FlatNumber', 'Street' and 'Postcode'.

Many many thanks for any help!!!!

 

[Eric Frost]

Did you find the Access examples in the articles section of MP2Kmag com?

There are also a lot of basic Excel VBA examples, the coding is practically identical to what it would be in Access VBA --http www mp2kmag com / excel

Eric

 

[Garindan]

Thanks Eric, yes I've looked through a fair amount of stuff at mp2kmag and I'm starting to get an idea of how it works. Thankyou.

I think its just that I'm a novice with this and whilst I can do basic VBA code I struggle with some things.

I can't really work out how to get started. I mean can I place code in the 'on open' or 'on load' of the report to generate a route? Or will the code have to be behind a command button on a form, which will generate a route and add it to a report?

As I said, I can start to understand some of the code, I'm just unsure of how to go about achieving what I want.

If you have any ideas of how I could do it/get started I would be extremely grateful!

 

[Eric Frost]

I don't mean to sound mercenary, but it this a situation where you'd like to pay someone like me to spend an hour or even just half an hour via shared desktop getting you started? write me at eric.frost AT mp2kmag com

 

[Eric Frost]

Sorry again for the "mercenary" offer, I'm just not sure what to tell you

You see the working examples, can you build off those?

If not, do you have a specific question? then I'd be glad to help via the forum!

On my side, I am actually quite often or at least a couple times per year where I or someone who has promoted me says I can do stuff and I find myself point blank STUCK on something e.g. NodeJS despite hours of hunting and I wish I could pay something for an hour of consulting/training to get me over the hump.

Forums only go so far, they are good for very specific and non-crucial help and are an excellent repository for FAQ-type information, IMHO. In any case, let me know if you'd like some direct help!

Eric
http www MapForums com

 

[Garindan]

Thanks Eric, and don't worry, I might consider it soon

I have now created a new query 'qselOrderRoutePlanner' which only holds the names and addresses of people for delivery. So no erroneous data such as delivery date, items, etc etc. Just the name and address.

This I feel makes it much more straightforward. Now all I want to do is have code on a form button which will 'On Click' read the addresses from that query and generate an optimised route.

So its more straightforward. But I am unsure how I can code it to have the results of that route outputted to a report, preferably with a simple map? But at the least with a list of addresses on the route in the correct 'optimised' order. Also, I need to be able to choose the delivery depot as a start and end point for the route. This can be fixed in the code if needed, but it would be nice to have a box to input start and end, with the address/postcode pre-filled to save time. This would then give the option of changing start/end point if required.

I'll email all this to you too. Cheers.

 

[Garindan]

Thankyou for your help Eric! The hour or so I spent with you on Skype/Teamviewer was very worthwhile, and I couldn't have done it without you! Very many thanks!

 

[Eric Frost]

Just to follow up, here are some new Access VBA and MapPoint examples.

The first shows how to geocode or obtain the lat/lon from an address and write it back into MapPoint.

Sub Geocode()
  Dim APP As MapPoint.Application
  Dim MAP As MapPoint.MAP
  Dim FAR As MapPoint.FindResults
  Dim LOC As MapPoint.Location
  
  Set APP = CreateObject("MapPoint.Application")
  APP.Visible = True
  Set MAP = APP.ActiveMap
  
  Dim rs As Recordset
  Set rs = CurrentDb.OpenRecordset("SELECT * FROM [Al's Beef]")

  Do Until rs.EOF = True
    Set FAR = MAP.FindAddressResults(rs("Address"), rs("City"), , rs("State"), rs("Zip"))
    Set LOC = FAR(1)
    rs.Edit
    rs!MP_Latitude = LOC.Latitude
    rs!MP_Longitude = LOC.Logitude
    rs!MP_MatchedTo = GetGeoFieldType(LOC.Type)
    rs!MP_Quality = GetGeoQuality(FAR.ResultsQuality)
    rs!MP_Address = LOC.StreetAddress.Value
    rs.Update
    rs.MoveNext
  Loop
End Sub

I think the code is fairly self-explanatory, but there is also a related article with some humor here in the "MP2K Magazine Articles" section on MapForums com

Also, here's an example showing how to calculate driving distances --

Sub CalculateDistances()
  Dim APP As MapPoint.Application
  Dim MAP As MapPoint.MAP
  Dim RTE As MapPoint.Route
  Dim LOC1, LOC2 As MapPoint.Location
  
  Set APP = CreateObject("MapPoint.Application")
  APP.Visible = True
  Set MAP = APP.ActiveMap
  Set RTE = MAP.ActiveRoute
  
  Dim rs1, rs2 As Recordset
  Set rs1 = CurrentDb.OpenRecordset("SELECT * FROM [Al's Beef]")
  Set rs2 = CurrentDb.OpenRecordset("SELECT * FROM [Al's Beef]")
  
  Dim sql As String
  sql = "CREATE TABLE AB_Distances (ID1 INTEGER, ID2 INTEGER, Distance Float)"
  CurrentDb.Execute sql

  Do Until rs1.EOF = True
    Set LOC1 = MAP.GetLocation(rs1("MP_Latitude"), rs1("MP_Longitude"))
    rs2.MoveFirst 'reset
    Do Until rs2.EOF = True
      If rs1("ID") <> rs2("ID") Then 'don't bother to calculate a store's distance to itself
        Set LOC2 = MAP.GetLocation(rs2("MP_Latitude"), rs2("MP_Longitude"))
        RTE.Waypoints.Add LOC1
        RTE.Waypoints.Add LOC2
        RTE.Calculate
        sql = "INSERT INTO AB_Distances (ID1, ID2, Distance) VALUES (" & rs1("ID") & ", " & rs2("ID") & ", " & RTE.Distance & ")"
        CurrentDb.Execute sql
      End If
      rs2.MoveNext
      RTE.Clear
    Loop
    rs1.MoveNext
  Loop
  MAP.Saved = True
  Debug.Print "finished"
End Sub