Working with data sets in the MapPoint object model

Article
11/16/2006

DataSet objects are the automation representation of any data in MapPoint. You access DataSet objects from the DataSets collection, which is itself accessible from the active Map object. The following sample code shows you how to get the count of data sets in a running MapPoint application:

  Sub GetCountOfDataSets()
    Dim objDataSets As MapPoint.DataSets
	
    'Get the data sets collection and display the count:
    Set objDataSets = objApp.ActiveMap.DataSets
    MsgBox objDataSets.Count
End Sub

Note This and other samples in this topic assume that MapPoint is already running, and that objApp is a MapPoint Application object that has already been initialized.

To create new data set objects, you can import or link to data from other programs.

Importing and linking to external data

To use external data from programs such as Microsoft Excel in MapPoint, you must first either import the data or link to it. Importing data brings it into MapPoint and allows you to use the data but does not maintain a link back to the original data source. If you link to the data, MapPoint maintains a link back to the original data source, allowing you to update your map whenever the original data changes. The following sample code uses the ImportData method of the DataSets collection to import a range of data from one sheet of an Excel file:

  Sub OpenDataSet()
    Dim objDataSets As MapPoint.DataSets
    Dim objDataSet As MapPoint.DataSet
    Dim zDataSource As String

    zDataSource = "C:\MyExcelFile.xls!Sheet1!A1:G4"
    Set objDataSets = objApp.ActiveMap.DataSets
    Set objDataSet = objDataSets.ImportData(zDataSource)
End Sub

This simple example shows how MapPoint can automatically discover the Excel data and determine how to import it. MapPoint will automatically use all the data provided and guess which columns are geographic (for example, an address, city name, or country name). In most cases, MapPoint defaults are adequate to import the data and locate it properly on the map. However, the ImportData method has other options that give you more control over how your data is imported, if you need it. The following example specifies some other options and imports a .csv (comma-delimited text) file:

  Sub OpenDataSet()
    Dim objDataSets As MapPoint.DataSets
    Dim objDataSet As MapPoint.DataSet
    Dim zDataSource As String

    zDataSource = "C:\MyExcelFile.csv"
    Set objDataSets = objApp.ActiveMap.DataSets
    Set objDataSet = objDataSets.ImportData(zDataSource, , _
            geoCountryDefault, _
            geoDelimiterComma, _
            geoImportFirstRowIsHeadings)
End Sub

The individual parameters of the ImportData method are discussed in the ImportData reference topic, but there is one parameter to examine in more detail. The ArrayOfFields parameter allows you to specify each field that you want to import and, optionally, how you want to treat that field. For example, if you want to import only four of the fields, you can pass in just those fields and MapPoint will ignore the other fields in the file that you are importing. To specify how some or all of the fields are used (for example, if you know that a field called Address is a Web address and not a street address), you can tell MapPoint that the field is a data field and not to use it as geographic field. The parameter is a two-column array, where the first column is the name or index of the field in the original data file and the second column tells MapPoint what you want to do with the field.

The following example shows how to import a file, identifying four columns to import, and specifying that the Address field be imported as a data field rather than a geographic column:

  Sub OpenDataSet()
    Dim objDataSets As MapPoint.DataSets
    Dim objDataSet As MapPoint.DataSet
    Dim zDataSource As String
    Dim xFieldArray(1 To 4, 1 To 2) As Variant

    'Use the Name field as the name:
    xFieldArray(1, 1) = "Name"
    xFieldArray(1, 2) = geoFieldName
    'Use the Address field as data:
    xFieldArray(2, 1) = "Address"
    xFieldArray(2, 2) = geoFieldData
    'Use the City field as the city:
    xFieldArray(3, 1) = "City"
    xFieldArray(3, 2) = geoFieldCity
    'Let MapPoint decide how to import the State field:
    xFieldArray(4, 1) = "State"

    zDataSource = "C:\MyExcelFile2.csv"
    Set objDataSets = objApp.ActiveMap.DataSets
    Set objDataSet = objDataSets.ImportData(zDataSource, _
            xFieldArray, _
            geoCountryDefault, _
            geoDelimiterComma, _
            geoImportFirstRowIsHeadings)
End Sub

Note To see how the ImportData method compares to the MapPoint Import Data Wizard, see "Comparing ImportData to the user interface."

Linking to data is nearly identical to importing data, except that you must include a primary key field. You use the LinkData method, which has an additional parameter that is the name of the key field (or an integer index to its position in the source data). If you specify a field array, the key field that you specify will be imported even if you skip it in the list of fields.

You can also use the ShowImportWizard or ShowLinkWizard methods on the DataSets collection to display the MapPoint user interface for importing and linking to data.

Note To define territories by importing or linking to data, there are special methods that apply to territories: ImportTerritories, LinkTerritories, ShowImportTerritoriesWizard, and ShowLinkTerritoriesWizard. These behave identically to the methods discussed previously with the exception that one of the fields you are importing must be the territory definition (such as Name or ID).

After you have imported or linked to a set of data, you can use the returned DataSet object to work with the data that was imported or linked to.

Using a DataSet object

Once you have accessed an existing data set or created a new one, you can get information about the fields and data in the data set and perform functions on the data set. For example, you can set the symbol with which imported records will be displayed (by default, you can also change them on a per-record basis) using the Symbol property:

  objDataSet.Symbol = 20 'small blue circle

You can also get information about the fields in the data set using the Fields collection. Use the Fields property of the DataSet object to return the Fields collection, and then use the Item property or the Visual Basic For Each...Next statement to return the individual Field objects from the collection:

  For Each objField in objDataSet.Fields
    strFieldNames = strFieldNames & vbCrLf & objField.Name
Next objField
MsgBox "FieldNames: " & strFieldNames

Some of the interesting information that you can get from the data set is the number of records it has that are matched, unmatched, or skipped. Then you can programmatically match the ones that are not matched, record by record. To work with the data on a record-by-record basis, use a Recordset object.

Using Recordset objects

The way to work with the individual data records in a data set is to create a Recordset object. Recordset objects are created through queries on the data set. The easiest query simply returns all the records in the data set:

  Set objRecordSet = objDataSet.QueryAllRecords

You can also create geographic queries that return all the records within a specified area. You can specify all records within a circle, within a polygon, or within a MapPoint Shape object by using the QueryCircle, QueryPolygon, or QueryShape methods respectively on the DataSet object:

  Set objRecordSet = objDataSet.QueryCircle(objCenterLocation, 15)
Set objRS = objDataSet.QueryPolygon(vArrayOfObjLocations)
Set objRS = objDataSet.QueryShape(objMyShape)

Once you have a Recordset object, you can use it to iterate through the individual records that were returned. The Recordset returns information about one record in the data set at a time. You can change which record the Recordset is currently pointing to by using the MoveFirst and MoveNext methods.

The Fields collection of a record set is identical to the fields in the parent data set, with the exception that the Value property returns the value for the current record.

Now you have enough information to use a Recordset. The following example finds the smallest "Sales" value in a set of records:

  Sub ShowSmallestSales()
	Dim objDS As MapPoint.DataSet
	Dim objRS As MapPoint.Recordset
	Dim dblSmallest As Double
	Dim dblCurrent As Double
	
	Set objDS = objApp.ActiveMap.DataSets(1)
	Set objRS = objDS.QueryAllRecords
	
	objRS.MoveFirst
	If objRS.EOF Then
		MsgBox "There are no records."
		Exit Sub
	End If
	dblSmallest = objRS.Fields("Sales").Value
	Do While Not objRS.EOF
		dblCurrent = objRS.Fields("Sales").Value
		If dblCurren < dblSmallest Then
			dblSmallest = dblCurrent
		End If
		objRS.MoveNext
	Loop
	MsgBox "Smallest Sales value = " & dblSmallest
End Sub

You can also find information about how or whether the records were matched. You can programmatically match any records that were not matched when MapPoint imported or linked to the data. You can, for example, provide the user with a choice of places to match to, programmatically find the right choice, or just accept the first choice MapPoint makes in all cases. You call the Recordset object's CallMatchMethod method to have MapPoint attempt to match the record and then pass you the possible matches if the match was ambiguous.

In the following example, CallMatchMethod is used to always choose the first possible match:

  Sub MatchAllRecords()
    Dim objDS As MapPoint.DataSet
    Dim objRS As MapPoint.Recordset

    Set objDS = objApp.ActiveMap.DataSets(1)
    Set objRS = objDS.QueryAllRecords

    objRS.MoveFirst
    If objRS.EOF Then
        MsgBox "There are no records."
        Exit Sub
    End If
    Do While Not objRS.EOF
		If objRS.IsMatched <> True Then
            objRS.CallMatchMethod "MatchFirst", Me
        End If
        objRS.MoveNext
    Loop
End Sub

Public Function MatchFirst(Name As String, _
        Results As MapPoint.FindResults) _
        As Long
    MatchFirst = 1
End Function

Note This sample assumes that the code is running in a form or class module and that the class is public.

Using MapPoint demographic data

MapPoint gives you access to its built-in demographic data through the DataSet object as well. However, there are some differences between a demographic data set and one that you have imported or linked to. First, you access a demographic data set differently than you do an external data set. You use the GetDemographics method to create a new demographic data set, as in the following code:

  Set objDemoDataSet = objDataSets.GetDemographics

This gives you a data set that you can use to query the field information or create a data map on. However, you cannot query any of the records of a demographics data set. Calling any of the query methods will fail. Also, because this data set has no representation on the map, it is not part of the DataSets collection, although you do use the DataSets collection to create it. If you then create a data map, the demographic data set will appear in the DataSets collection. You can create as many demographic data sets as you need.

Manual Pushpin DataSet objects

There is one last type of DataSet object in MapPoint. This is a manual, or Pushpin, data set as opposed to imported or linked Pushpin sets, which are discussed above. A Pushpin data set is created in MapPoint without importing or linking to any data from an outside source. This type of data set is created in the MapPoint user interface automatically when you use the Find dialog box to find an address or when you create a Pushpin with the Create Pushpin drawing tool. The default name of this data set is My Pushpins. You can create one in the object model by using the AddPushpinSet method on the DataSets collection. You can add a Pushpin to the default set using the AddPushpin method on the Map object, and then moving that into any other manual set with the MoveTo method on the Pushpin object.

Comparing ImportData to the user interface

One interesting way to learn about the ImportData method is to compare it to the Import Data Wizard user interface. The wizard starts off with a standard File Open dialog box:

Import Data Wizard File Open dialog box

You use the File Open dialog box to select a file. If you open an Excel or Access file that contains multiple tables, then on the following page, you are asked to choose the part of the file that you want to open:

Import Data Wizard Choose Sheet or Range

The ImportData method starts with the DataSourceMoniker parameter. This is the equivalent of the File Open dialog box and the choice of worksheet (or table or range, as the case may be), but in a single parameter. For example, "C:\Coffee.xls!Shops" equates to opening the C:\Coffee.xls spreadsheet file and importing data from the "Shops" worksheet. The ImportFlags parameter also includes options for how you want this string to be read (for example, in this case you could set a flag that said the "Shops" part of the string would be a named range rather than a worksheet). For more information on the import flags, see the ImportData method reference topic.

If you were importing a text file instead of an Excel file, the Import Data Wizard would prompt you for the delimiter (the character used to separate fields of data from one another) at this point:

Import Data Wizard Choose Separator Character

This entire page is equivalent to the Delimiter parameter of the ImportData method. The bottom half of the page does not take input; rather, it simply shows you the effects of your selections. Notice that there are three possible delimiters in the user interface (Tab, Semicolon, and Comma), and there are four choices for the Delimiter parameter (geoDelimiterTab, geoDelimiterSemicolon, geoDelimiterComma, and geoDelimiterDefault). geoDelimiterDefault means to choose the one that MapPoint guesses is the correct one.

The next (and last) page of the Import Data Wizard is where most of the interesting work is done, and it includes all of the other parameters.

Import Data Wizard Column Headings

At the top of the page, you select the country (or countries using the <Multi/Other> choice). This is equivalent to the Country parameter. Next to that is a check box to specify whether the first row of the data contains column headings. This is one of the settings in the ImportFlags parameter.

Finally, the bottom part of the page is the equivalent of the ArrayOfFields parameter. Each field has a column heading (from the source file) and a data type, which specifies how MapPoint should handle the data, including whether it should be skipped, treated as a geographic field, or treated as non-geographic data. This is the same as the array that you can pass in to the ImportData method. For each field that you want to import, you give it a name and tell MapPoint what to do with it.

If you run the Import Data Wizard in MapPoint, you will notice that it is immediately followed by the Data Mapping Wizard, where you can specify how to display the imported data.