Lesson 2: Managing Data sets

Link multiple shapes to multiple rows

ActivePage.LinkShapesToDataRows vsoDataRecordset.ID, alngDataRowIDs, alngShapeIDs, boolAddDataGraphic

To link shapes, pass the LinkShapesToDataRows method a pair of arrays: one for shapes, and one for data rows. Note that the matching array positions must correspond. As a result, for example, the shape at position 1 in the shape array is linked to the data at position 1 in the data row array. Again, when you call the method, you can optionally specify whether to apply an existing data graphic to linked shapes.

Automatically link shapes and data rows

The help file for the AutomaticLink method gives a coding example for setting up an automatic link where a single column to be matched is specified. Let’s set up an example for matching multiple columns and let’s use the record macro feature of Visio to create the code.

Try it!

1. 
   Open Working with DataRecordsets.vsd and run the macro BuildOrgMacros.AddDataRecordset to create a datarecordset from ORGDATA.xls.
   
2. 
   Drop three rectangles on the page and link them to the first three records in the datarecordset. This will add shape data fields to the rectangles.
   
3. 
   Unlink the three shapes.
   
4. 
   Turn on the macro recorder.
   
5. 
   Select the three shapes.
   
6. 
   Right-click in the External Data window and choose Automatically Link. The Automatically Link wizard is presented.
   
7. 
   On the first page choose Selected Shapes and then Next.
   
8. 
   Choose Data Column = Name and Shape Field = Name. Then click the and button.
   
9. 
   Choose Data Column = Title and Shape Field = Title.
   
10. 
    Click Next and Finish. This will link the three selected shapes.
    
11. 
    Stop the macro recorder and look at the code.
    

ColumnNames1(0) = "Name"

FieldTypes1(0) = visAutoLinkCustPropsLabel

FieldNames1(0) = "Name"

ColumnNames1(1) = "Title"

FieldTypes1(1) = visAutoLinkCustPropsLabel

FieldNames1(1) = "Title"

Application.ActiveWindow.Selection.AutomaticLink 41, ColumnNames1, _

FieldTypes1, FieldNames1, visAutoLinkReplaceExistingLinks, _

IDsofLinkedShapes1

We are passing to the method arrays for column names, field types, and field names. In this case we are specifying that multiple columns and fields must match before the shapes can be linked.

The penultimate parameter specifies the auto link behavior. Use values from the VisAutoLinksBehavior enumeration. These enumerated values provide options to customize the method, for example to replace existing links with new ones.

The last parameter returns an array of shape IDs of the linked shapes.

Discovering links

• 
  Page.GetShapesLinkedToData – given a datarecordset, returns an array of shape IDs on a page linked to the datarecordset
  
• 
  Page.GetShapesLinkedToDataRow – given a datarecordset and a row ID, returns an array of shape IDs on a page that are liked to that row
  
• 
  Shape.GetLinkedDataRow – given a datarecordset, returns the ID of the row the shape is linked to
  
• 
  Shape.GetLinkedDataRecordsetIDs – returns a list of IDs of all datarecordsets that are linked to by this shape
  
• 
  Shape.GetCustomPropertyLinkedColumn – given a datarecordset ID and a shape’s Shape Data row ID, returns the name of the data column linked to this shape data row
  
• 
  Shape.GetCustomPropertiesLinkedToData – given a datarecordset ID, returns an array of Shape Data row IDs that are linked to the data columns in the datarecordset
  
• 
  Shape.IsCustomPropertyLinked - given a datarecordset ID and a shape’s Shape Data row ID, returns whether that Shape Data item is linked
  

Breaking links

In addition, actions in the user interface can cause links to be broken, such as deleting a datarecordset or deleting the Shape Data item in a shape that is linked.

Regardless of how the link was broken, the Shape.ShapeDataLinkDeleted event is fired.

A note on the use of IDs in Visio

Shape IDs

Shape UniqueIDs

To convert between shape IDs and unique IDs, you can use two methods of the Page object, ShapeIDsToUniqueIDs and UniqueIDsToShapeIDs.

By default, a shape does not have a unique ID. A shape acquires a unique ID only if you set its UniqueID property. If a Shape object has a unique ID, no other shape in any other document will have the same ID.

DataRecordset IDs

Row IDs within a DataRecordset

Rows of data within a DataRecordset generally have unique IDs starting with a value of 1, but they may not run consecutively depending on recent refresh operations.

Because shapes are linked by their shape IDs to specific data rows, when Visio refreshes linked data, it must determine which rows in the linked data recordset or recordsets were added, changed, or removed since the last time the data was refreshed.

To identify these rows, Visio uses the row IDs assigned to the rows in the data recordset. Visio can assign these row IDs two ways, depending on whether you designated primary keys for the data recordset when you created it.

Refreshing Data Recordsets That Do Not Have Primary Keys

When you create a data recordset, Visio assigns row IDs to all the rows in the recordset based on the existing order of the rows in the data source. Accordingly, the first row in the recordset is always assigned row ID 1, the second is assigned row ID 2, and so on.

Subsequently, data rows can be added or removed from the original data source. Then, when data is refreshed, the data recordset reflects those changes. As a result, row order in the data recordset may change.

For example, in a five-row data recordset, if the fourth row in the data source is removed, when Visio refreshes the data recordset connected to that data source, the fifth row in the data recordset becomes the new fourth row and is assigned row ID 4. Row ID 5 is removed from the data recordset. As a result, shapes linked to row ID 5 lose their links, and shapes linked to row ID 4 now get data from the row previously in the fifth position.

As you can see, not assigning primary keys to data recordsets when you create them can result in broken links between shapes and data, or in Visio linking shapes to rows other than the ones to which you want them linked.

Refreshing Data Recordsets That Have Primary Keys

You can help prevent these broken or mismatched links by assigning primary keys to data recordsets. A primary key identifies the name of the data column or columns that contain unique identifiers for each row. The value in the primary key column for each row uniquely identifies that row in the data recordset. Primary keys are often ID values, but you can set any column or combination of columns to be the primary key. However, to get consistent results when you refresh data, it is essential that you make the primary key column value (or set of values for multiple primary key columns) unique for each row.

As a result, when you refresh or when Visio refreshes a data recordset that includes primary keys, its rows retain the same row IDs they had before the refresh operation. Because Visio links shapes to data rows by ID—shape ID to row ID—and because row IDs remain the same after a refresh operation, data-linked shapes remain linked to the correct row. Note that row IDs are never recycled for a given a data recordset.

25.Managing Refresh

DataRecordsets can be refreshed programmatically using the DataRecordset.Refresh method. Calling this method executes the query string associated with the datarecordset and then updates the linked shapes with the data returned by the query.

When a datarecordset is created it does not refresh automatically. To enable automatic refresh set its refresh interval to some value greater than zero.

'Set the refresh interval to 2 minutes

vsoDataRecorset.RefreshInterval = 2

To turn off automatic refresh set the refresh interval to zero.

'Turn off automatic refresh

vsoDataRecorset.RefreshInterval = 0

If calling Refresh results in conflicts, Visio displays the Refresh Conflicts task pane in the UI, unless you set the RefreshSettings property to include the visRefreshNoReconciliationUI (=2) enumerated value. By default RefreshSettings=0.

As a developer, you should reconcile refresh conflicts programmatically by using the GetAllRefreshConflicts, GetMatchingRowsForRefreshConflict, and RemoveRefreshConflict methods.

'Don’t display refresh conflicts in the Refresh Conflicts task pane

vsoDataRecorset.RefreshSettings = visRefreshNoReconciliationUI

Identify changes and resolve conflicts

When a datarecordset is refreshed conflicts are displayed in the Refresh Conflicts task pane.

If a data row has been deleted and a shape was linked to it, that shape is displayed and the user is prompted for what to do with it.

If a new data row is added that is a duplicate of a previous row that was linked, the link is removed and the user is presented with the duplicate data and prompted for how to deal with it.

If a new data row is added that is not a duplicate, there is no conflict. From the user interface the new record is apparent by looking at the External Data window and noting the unlinked records.

The automation model provides tools to handle each of these scenarios.

GetAllRefreshConflicts

This method returns an array of shapes that are linked to data rows in a datarecordset that have non-resolved conflicts after a datarecordset is refreshed.

Conflicts can result when a single shape is linked to more than one row in the same data source, or when a shape is linked to a row in the data source that has been deleted. If the shape would be linked to multiple rows, the link is removed and the conflict is presented in the Refresh Conflicts task pane. Use GetMatchingRowsForRefreshConflict to retrieve all of the rows in the datarecordset that match with this shape.

If no conflicts exist, an empty array is returned.

GetMatchingRowsForRefreshConflict

When a shape is potentially linked to multiple rows in a datarecordset after a refresh, the list of matching rows can be retrieved with this method. It returns an array of row IDs from the datarecordset that each match the shape in conflict.

Conflicts can also occur when a previously data-linked row from the data recordset is removed. When this occurs, the method returns an empty array.

RemoveRefreshConflict

Once a conflict has been dealt with it can be removed from the list by using the RemoveRefreshConflict method of the shape.

DataRecordsetChanged event and DataRecordsetChanged object

Retrieving conflict information is great for when changes to the datarecordset results in conflicts, but this does not help when new rows are added to the datarecordset. For example, when a new row is added the user may want to add a new shape that is linked to the new rows.

Fortunately, there is a way to retrieve this information programmatically. Use the DataRecordsetChanged event to detect that the datarecordset has changed. Within the DataRecordsetChanged event you have access to the DataRecordsetChanged object which contains a wealth of information about what has changed in the datarecordset including data columns that have been added, changed, or deleted, and data rows that have been added or deleted.

Try it!

The sample code in Working with DataRecordsets.vsd has a macro that will Refresh a datarecordset and then check for and process changes using the methods and events just described. If you already have the file open, it is best to close it and then reopen it. Some of the code is not bullet proof, but it does illustrate how to use the methods.

1. 
   Open Working with DataRecordsets.vsd from the Samples folder.
   
2. 
   Run the macro BuildOrgMacros.DoItAll. This builds the organization chart we saw earlier using the OrgData.xls file.
   
3. 
   Run the macro BuildOrgMacros.RefreshDataAndResolveConflicts. This macro refreshes the Org Data datarecordset by modifying the connection string to point to a modified version of the Excel file OrgDataModified.xls and then using the Refresh method.
   
4. 
   The following changes are made to the organizational data:
   
   1. 
      The record for Toby Nixon is changed to Toby Dixon. In the code this is handled as one deleted record and one added record. The original Toby Nixon shape is deleted and a new shape is added.
      
   2. 
      The record for Meng Phua is changed to report to Toby Dixon. There is no conflict created by this change. The datarecordset and the shape data are updated on the refresh, but because the Toby Dixon record is new, the connector has to be changed to point to the correct “Reports_To” shape
      
   3. 
      A new record is added for Fred Taylor who reports to Toby Dixon and a duplicate record for Fred Taylor is also created. If both records are added at the same time there is no conflict because there is no shape linked to these rows. A new shape is added for Fred Taylor. When the second new Fred Taylor shape is processed it is ignored because it is a duplicate.
      
5. 
   Look at the code for RefreshDataAndResolveConflicts. Only the refresh code is here.
   
6. 
   The resolution of the conflicts is handled in the event procedure vsoDataRecordsets_DataRecordsetChanged which is in the ThisDocument module.
   

   Note:

   It is best to put the resolve conflicts code inside the event procedure. The event code does not fire immediately after the Refresh method so if you have some code in the event procedure and some code following the Refresh method, you won’t be able to determine which code is executed first.

7. 
   Look at the event procedure code. Conflicts are handled first using GetAllRefreshConflicts and then processing the conflict list. If a row has been deleted, the corresponding shape is deleted. If duplicate rows are created which have a corresponding shape, the shape is linked to one of the rows and the other row is ignored.
   
8. 
   New rows added to the datarecordset are handled next. If a new row is added which does not represent a duplicate, a new shape is added to the drawing and linked its “Reports_To” shape.
   
9. 
   All resulting changes in the drawing are in the lower right hand area of the drawing page.
   

Warning for VBA users implementing code samples from Help files

Below is the code sample provided in the Help file for GetMatchingRowsForRefreshConflic. Note that after the call to GetAllRefreshConflicts and to GetMatchingRowsForRefreshConflic there is an If test that uses the function IsEmpty to test for an empty array being returned by the methods.

Unfortunately, this test does not work in VBA. The IsEmpty function is designed to work with variables of type Variant and does not reliably return information for other data types. It returns False even when the array has no data.

There are several similar methods that return arrays and similar code samples that use IsEmpty to test these arrays. All of these samples will be problematic in VBA.

Public Sub GetMatchingRowsForRefreshConflict_Example()

Dim vsoDataRecordset As Visio.DataRecordset

Dim intRecordsetCount As Integer

Dim intShapeCount As Integer

Dim vsoShapes() As Visio.Shape

Dim intRowCount As Integer

Dim vsoShapeInConflict As Visio.Shape

Dim alngRowIDs() As Long

Dim lngvsoRowID As Long

intRecordsetCount = Visio.ActiveDocument.DataRecordsets.Count

Set vsoDataRecordset = Visio.ActiveDocument.DataRecordsets(intRecordsetCount)

vsoDataRecordset.Refresh

vsoShapes = vsoDataRecordset.GetAllRefreshConflicts

If IsEmpty(vsoShapes) Then

Debug.Print "No conflict"

Else

For intShapeCount = LBound(vsoShapes) To UBound(vsoShapes)

Set vsoShapeInConflict = vsoShapes(intShapeCount)

alngRowIDs = vsoDataRecordset.GetMatchingRowsForRefreshConflict(vsoShapeInConflict)

If IsEmpty(alngRowIDs) Then

Debug.Print "For shape:", vsoShapeInConflict.Name, "Row deleted."

Else

For intRowCount = LBound(alngRowIDs) To UBound(alngRowIDs)

lngvsoRowID = alngRowIDs(intRowCount)

Debug.Print "For shape:", vsoShapeInConflict.Name, "Row ID of row in conflict:", lngvsoRowID

Next intRowCount

End If

Next intShapeCount

End If

End Sub

Alternatives to using IsEmpty

Sometimes, but not always, the UBound function will work and return a value of -1 if the array is empty. However, in some cases the UBound function will throw a ‘subscript out of range’ error.

If using UBound does not work the only reliable alternative is to create an error handler to trap and process the error.

26.Changing data locations

Sometimes data locations change or network connections are lost. Is there a way to build some resiliency into your code so that if your access to the data changes your program does not fall over?

Let’s examine some common scenarios:

1. 
   If the data changes location you may have to change the connection string in your code to point to the new location. We’ve already seen that the connection string can be changed and the datarecordset refreshed so straightforward to build this kind of change into your code.
   
2. 
   You can isolate your program from the data by using a data connection file and using the AddFromConnectionFile method. Connect to the data via a connection file with the assumption that the connection file stays in a fixed location and if the data moves, the connection file is updated with the new location.
   
3. 
   The third variation is when the data becomes unavailable unexpectedly such as when a network connection is lost or if the data is moved, but you don’t know where it is. In this scenario we can’t reconnect to the data, but we can detect that the data could not be found and then fail as gracefully as possible.
   

To detect a failed connection use the Timeout property of the DataConnection object. This property is used to set a timeout interval (default is 15 seconds). If the interval elapses without success an error is raised. An error handler must be written to trap the error. An example follows showing a portion of the subroutine which refreshes a datarecordset and displays the error message when an error occurs:

'Set the connection string into the DataConnection object to point to

'the desired data

vsoDataRecordset.DataConnection.ConnectionString = strConnection

'Set up error handler to catch the timeout error

On Error GoTo ErrorHandler

vsoDataRecordset.DataConnection.Timeout = 15

'Refresh the data. This will fail if the file cannot be reached

vsoDataRecordset.Refresh

Exit Sub

ErrorHandler:

Debug.Print "Error: ", Err.Description

End Sub  

Directory: download
download -> Performance Tuning Guidelines for Windows Server 2008 May 20, 2009 Abstract
download -> Northern England’s set-jetting locations
download -> Bbc archives: Beyond the programme download
download -> Occupational health and safety
download -> Dynatest 3031 lwd light Weight Deflectometer
download -> Forth coming competitive exams
download -> English Olympiad Tasks: 10 th form Аудіювання, 10 клас. Текст
download -> Brush High School Morning Announcements Friday, September 05, 2014 all school
download -> Year 9 The Arts — Music
download -> Years 7 and 8 standard elaborations — Australian Curriculum: Music draft

Download 0.78 Mb.

Share with your friends: