Welcome to MapForums!

Register, sign in, or use Facebook Connect above to join in and participate in the forum.

When you are signed in, this message, the ads in this row, and the red-underlined link ads all go away.

 Subscribe to receive our newsletter. Subscribe Unsubscribe

## Access VBA Programming Part II - Calculating a Distance Matrix

This is a discussion on Access VBA Programming Part II - Calculating a Distance Matrix within the MP2K Magazine Articles forums, part of the Map Forums category; In the last article we geocoded a set of Al's Beef locations , all but one of which are in ...

1. ## Access VBA Programming Part II - Calculating a Distance Matrix

In the last article we geocoded a set of Al's Beef locations, all but one of which are in Chicago or nearby suburbs, which makes sense as Chicago is mentioned in the very first sentence of the Wikipedia entry for "Italian beef". Indeed, Al's Beef is mentioned in the very first sentence of the section on Italian beef's origins.

We are now going to use the results of the geocoding, i.e. the Latitude Longitude values, and calculate the driving distance between each of the stores and put the results in a new table. There are 14 locations, so by my rough estimate we have 14 * 13 or 182 distances to calculate. If they each take 1 second, the program should complete in just a few minutes.

Conceivably you could get away with half that number as you don't necessarily need the distance from the Park Ridge store to the Niles location AND the distance from the Niles store to the Park Ridge location, but because of one way streets and they layout of On / Off ramps on interstates there will be differences.

From a practical perspective, a distance matrix like this might be useful for logistics or supplies, for instance, if one store runs out of hot giardiniera (which would be a disaster) you could quickly see what are the three closest stores and call to see which one can provide speedy pepper reinforcements.

Here is the code --

Code:
```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.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```
You can see there's two recordsets, both of the same table, and two loops, one inside the other. I'm just using CurrentDb.Execute to fire off SQL Statements, because that's much more straightforward to me than DAO TableDefs and what not.

A screenshot of the resulting table is above and it has just three columns: ID1, ID2, and the distance. If you need help transforming this into a traditional distance matrix e.g. with 14 rows and 14 columns, let me know by commenting below and I can help you figure this out.

So, most of the MapPoint bits in here should be very familiar to MapPoint programmers, and between the last article and this one we've shown programmatically how perform the following Access operations --

• loop over a table
• update existing records
• create a new table
• insert records into a new table

What else would you like to see, either on the MapPoint side or with operations in Access? If there are any requests, we can certainly publish a Part III or Part IV and make "Access VBA Programming" a regular feature.
Last edited by Eric Frost; 09-11-2013 at 05:37 PM.

2. Junior Member White Belt
Join Date
Jul 2014
Posts
1

## Re: Access VBA Programming Part II - Calculating a Distance Matrix

Hi Eric,

I am using a modified version of your code tailored to my tables, but it seems like the LOC1 and LOC2 variables are not being assigned the coordinates (or anything) - it always states in debug mode that they are set to "Nothing".

I'm using Access 2003 and Mappoint 2004 - any idea what may be causing this? I've pasted my code below, any help would be greatly appreciated.

Code:
```Dim APP As MapPoint.Application
Dim MAP As MapPoint.MAP
Dim RTE As MapPoint.Route
Dim LOC1 As MapPoint.Location
Dim LOC2 As MapPoint.Location

Set APP = CreateObject("MapPoint.Application")
APP.Visible = False
Set MAP = APP.ActiveMap
Set RTE = MAP.ActiveRoute

Dim rs1, rs2 As Recordset
Set rs1 = CurrentDb.OpenRecordset("Tech Points", dbOpenDynaset, dbSeeChanges)
Set rs2 = CurrentDb.OpenRecordset("Service Review Available Techs", dbOpenDynaset, dbSeeChanges)

DoCmd.RunSQL "DROP TABLE SvcRev_Distances"
Dim sql As String
sql = "CREATE TABLE SvcRev_Distances (ID1 INTEGER, ID2 INTEGER, Distance Float)"
CurrentDb.Execute sql

Do Until rs1.EOF = True
Set LOC1 = MAP.GetLocation(rs1("Latitude"), rs1("Longitude"))
rs2.MoveFirst
Do Until rs2.EOF = True
If rs1("resource_id") = rs2("resource_id") Then
Set LOC2 = MAP.GetLocation(rs2("Latitude"), rs2("Longitude"))
RTE.Calculate
sql = "INSERT INTO SvcRev_Distances (ID1, ID2, Distance) VALUES (" & rs1("resource_id") & ", " & rs2("workorder_activity_id") & ", " & RTE.Distance & ")"
CurrentDb.Execute sql
End If
rs2.MoveNext
RTE.Clear
Loop
rs1.MoveNext
Loop
MAP.Saved = False```
Last edited by Eric Frost; 07-27-2014 at 09:24 AM.

3. ## Re: Access VBA Programming Part II - Calculating a Distance Matrix

Sublimer,

Welcome to the forums!

I know the answer to this!

The Latitude and Longitude properties were not added to MapPoint until version 2006. I'm surprised it does not throw an error.

In any case, the code as is will not work with any version prior to MapPoint 2006.

Hope this helps!

Eric
Last edited by Eric Frost; 07-29-2014 at 09:16 PM.