Guided Tour of MPMileage: Databases
MPMileage currently supports four main types of database: Microsoft Access, Microsoft Excel, ODBC connections, and Maptitude data views. 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 business 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 field(s) that specify the source and destination locations. Locations can be specified in terms of pushpin names, street addresses, or geographic coordinates.
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 (e.g. 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 will be filled with suitable names.
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 location options.