Guided Tour of MPMileage: Databases
MPMileage currently supports three main types of database: Microsoft Access, Microsoft Excel, and ODBC connections. Both the old 'Jet' engine (Office 2003 and earlier) and the more modern 'Ace' engine (Office 2007 and later) are supported. Excel is supported because it is a popular data source amongst the MapPoint user community, but it is recommended that you use a proper database (ie. Access or a suitable ODBC connection) instead. This is because MPMileage is designed for database operations, and Excel is not very efficient when used as a database. The lack of default strong typing in Excel can make it easy to mis-configure, and the lack of database indexes in Excel can result in huge processing inefficiencies.
This is a sample (empty) input database. It is defined as a table called 'Mileages' in Microsoft Access. Each row refers to one route to calculate. Each row must have a source and a destination field. These must be text fields, but they can have any name you wish. These refer to locations in the MapPoint map file. Here we use peoples' names. Pushpins in the map file have exactly the same names. MPMileage will use these names to find the corresponding pushpins, and use the pushpin locations for the route calculation.
Further fields are defined to store the results. Four possible results can be returned, and we use all four in this example (distance, time, cost, errors). The distance, time, and cost fields are typically numbers, but text fields are also accepted. The errors field is a text field that stores the result of each calculation. "Okay" indicates success, but other values can indicate that a problem was occurred when trying to find one of the locations or the route.
Press the Change button in the Database section of the main panel, to display the Database Parameters dialog box. This is illustrated on the right. The dialog box is used to set all the database parameters. Start at the top and work your way down. As you enter information (eg. the database type, file, table), further information and options will be filled out.
First, select the Data Source Type. MPMileage will fill this drop-down box with the supported database options. This example is from a PC with Microsoft Office 2003 installed, so Access 1997-2003 (and Excel 1997-2003) will appear in the options. Next enter the actual Data Source. Use the "..." button to select a suitable file - eg. an Access .mdb file (sample_clients.mdb in this example).
MPMileage will connect to the selected data source, and list the available database tables in the Table drop-down box. Here, the Mileages table has been selected.
After a table is selected, MPMileage will scan the table for suitable data fields. The remaining drop-down boxes (Input Columns and Output Columns) will be filled with suitable names. For example, the Start Locations and End Locations will only be filled with the names of text data fields. Both input columns and at least one output column must be set.
The final selection (Row Selection) allows specific groups of rows to be selected. The default of all rows is usually used, and results in the processing of all data rows. This option can be used to select rows for processing according to their value in the Errors column. You can select "all rows except" (eg. "select all rows which are not set to Okay") or you can select "all rows equal to" (eg. "select all rows which have a specific error"). This option is intended to be used when a previous batch needs to be re-processed due to location errors, but it could also be used to split a project into processing batches.
Next we look at the MapPoint routing options.
Buy MPMileage Now!
