This example shows you how to use MPMileage to compute the mileages for a Microsoft Access database. The database is based on the sample Clients.mdb database supplied with Microsoft MapPoint 2006. The example requires Microsoft Access 2000 (or later) and Microsoft MapPoint 2006 (or later) North America.
The example uses the sample_clients.mdb and sample_clients.ptm files found in the MPMileage samples file:
http://www.mpmileage.com/downloads/examples.zip
The database file sample_clients.mdb contains two tables: AddressesTable and Mileages. AddressesTable is not required for MPMileage, but it has been included because it was used to create the sample_clients.ptm map. The Mileages table lists the required start and end locations, and will also hold the results.
The map file, sample_clients.ptm, looks like this:
The map file must have a pushpin for each and every required location. The pushpin names must match the names used in the table that stores the required routes. The above example shows the pushpin for "Deanna Meyer" opened up. This includes a number of additional data fields. These will all be ignored. The locations can be in different pushpin sets, although typically they will be all in the same pushpin set (as above).
The Mileages table in sample_clients.mdb initially looks like this:
Each row specifies one route mileage to calculate. The ID field is not used by MPMileage and will be ignored. The Source and Destination fields refer to the required start and end pushpins in the above map file. The Distance, Time, Cost, and Errors fields will hold the calculated results. The Errors field is defined as text. The other three fields can be floating point numeric (recommended) or text.
Setting the MPMileage Parameters
Start MPMileage. The parameters may be different, but you will be presented by a dialog box that looks like this:
Press the Database Change button to display the Database Parameters dialog box. Set the database Type to "Access 1997-2003 (mdb)". Press the Data Source "..." button to select the Access MDB file that will be the data source. MPMileage will load this and scan it for the available tables.
Select the Mileages table from the list of available tables. This table contains our required routes and will store our results. MPMileage will scan the table and fill the remaining controls with the possible column options. Select the Locations as "Pushpins" and select the MapPoint file (sample_clients.ptm or samples_clients_2010.ptm) that contains the pushpin locations. We will set the columns that hold the start and end locations below.
Then select the required output columns. You must set at least one of the output columns, otherwise MPMileage will not have any work to process. The resulting Database Parameters dialog box will look like this:
Next, set the data fields for the start and end locations (ie. pushpins). These are set by pressing the Set Columns button in the Input columns box. This displays various location drop-down boxes that are used to set the start and end locations. This example works with pushpins, so there are only two drop-down boxes displayed. Set them as follows:
This dialog box will have more drop-down boxes if you had selected street addresses or coordinates for the location types. For example, street addresses require drop-down boxes for streets, cities, zipcodes/postcodes, and country.
Press OK to return to the Database Parameters dialog box, and OK to return to the main dialog box.
Select the required routing parameters. Note that some of these options might be disabled if they are not required for your selected data. For example, the time units are not required if you have not selected route times. Press the Road Speeds, Road Preferences, Rest Stop Times, and Costs to display dialog boxes that let you edit these options. All of these routing options are passed directly to MapPoint.
The completed dialog box should like this:
Start Processing
Press Start to start processing. This is a very short database and should not take long to compute. The resulting Mileages table should look something like this:
The exact numbers may vary according to the parameters that you have selected. The Cost field was created as a Currency type - hence the dollar signs.
Note that all rows were computed okay. MPMileage will added error notes in the Errors field if there were problems finding a location or calculating a route.
