Linking Shapes and Databases

Visio 2007 can link shape data to database records with a two-way connection that you can keep up to date. In programmatic terms, the link is persistent, which means that after you set up the link between shapes and database records, the connection remains in effect as long as both the drawing file and the database exist. When you change the value of linked shape data, you can refresh the database with the values. Likewise, when the database changes, you can update shapes. Clearly, this functionality is a powerful way to tie drawings and data together. Your drawing file can become a dynamic catalog of information that accurately reflects the contents of a database. At a minimum, you can create a shape-to-database link to import values into shape data fields.

Linking Limitations

There are limits to what Visio 2007 can do when it creates a link between a shape and a database record. Be aware of the following limitations:

• String size You cannot store ODBC strings larger than 64-KB characters in Visio 2007 cells and fields.

• Primary key type You cannot specify a field of type SQL_TIMESTAMP as the primary key.

• Replication IDs You cannot update replication IDs from an Access database.

• Timestamp fields You cannot update Timestamp fields from an Informix database.

• Binary field size You cannot store ODBC binary fields larger than 32 KB in Visio 2007 cells and fields.

Using the Link To ODBC Database Command

Follow these steps to link shape data:

Linking Master Shapes

You can select any shape on the drawing page and link it to a database record, but depending on your needs you can work more efficiently if you link master shapes instead. Each shape or master shape must be linked individually, and setting up links can be a time-consuming process no matter what type of shape you link. However, the advantage of linking master shapes is that you can drag the master onto the page to create many instances of the shape, and each instance can quickly be linked to a different record in the database as it is dropped onto the page.

You can open a master for editing, and then use the Link To ODBC Database command to link it to a database record. Or you can link a shape on the drawing page and then drag it to a stencil to create a master that includes links. Remember, however, that you cannot edit masters that ship on the Visio 2007 stencils. You must first save them to a custom stencil by right-clicking the master, choosing Add To My Shapes, and saving it to a custom stencil. You can also right-click any stencil, choose Save As, and save it as a custom stencil by renaming it. You can set your master so that dragging the shape onto the page causes the Select Database Record dialog box to open. To do so, you must click the Advanced tab in the Link To Database dialog box and then select the Select Record check box under Shape Drop Event, as shown in Figure 24-5.

Adding Shortcut Commands to Linked Shapes

By default, Visio 2007 adds commands to the shortcut menus for linked shapes so that you can more easily manage the database connection. When you use the Link To ODBC Database command, however, you can specify which actions and events you will be able to control from the shortcut menu. Action and event are Visio 2007 programming terms. An action in this context means a command that causes an action to occur, such as refreshing linked shapes. An event is simply something that happens, such as a user opening a drawing file. A drop event occurs when a shape is added to the page (copied and pasted or dragged from a stencil). In the Link To Shapes dialog box, you can click Advanced to display the Advanced dialog box, which contains these additional options, as Figure 24-6 shows.

You can specify the following actions under Shape Actions on the Advanced dialog box:

• Select Record With this command, you can associate a shape with a specific database record and add the record’s values to the shape’s data.

• Refresh Shape With this command, you can update the shape’s data values based on the current database values.

• Update Record With this command, you can write the shape’s data values to the linked database record.

• Delete Shape And Record With this command, you can delete a shape and the database record to which it is linked.

You can specify the following drop events under Shape Drop Event on the Advanced dialog box:

• None When you add a new shape to the page, nothing happens.

• Select Record When you drop or paste a shape, Visio 2007 displays the Select Database Record dialog box, in which you can select a specific database record to link the shape to.

• Refresh Shape When you drop or paste a shape, Visio 2007 updates its link and refreshes data values based on the latest database information.

Linking to a New Table

If you want to create a table in your database and define its fields at the same time you link those fields to shape records, you can do so in the Link To Database dialog box. Instead of selecting a table in the Table/View box, click New to display the New dialog box, shown in Figure 24-7. Here, you can name a new table and identify its fields and their data types. Visio 2007 creates the table in your database and displays its fields in the Link To Database dialog box.

To create a new table, in the Table Name box type a name for the table that doesn’t conflict with the names of other tables in the database. Then type the name of a field you want to include in the Name box, identify its data type in the Type box, and click Add. Make sure that at least one field is identified as a primary key, or Visio 2007 won’t be able to refresh the link.

Understanding How a Visio 2007 Link Works

This section helps you understand exactly how Visio 2007 creates and stores the shape-to-database links that you create. After you link a shape to a database record, Visio 2007 creates a cell named User.ODBCConnection in the User-Defined Cells section of the ShapeSheet window. You can select a linked shape and then choose Window, Show ShapeSheet to see this cell and its contents, as Figure 24-8 shows.

Figure 24-8. Visio 2007 stores the information about a shape–database link as a formula that specifies the primary key, field, and most recent value.

[View full size image]

The formula in the User.ODBCConnection cell is a very long line of code that looks a lot more straightforward when you break each line at the vertical bar like this:

ODBCDataSource=Visio Database Samples
ODBCQualifier=
ODBCTable=Office - Card Details
1
Name=Prop.Name
3
Department=Prop.Department=0
Extension=Prop.Extension=0
Title=Prop.Title=0

This example is taken from the Office Card shape on the Sample Database Shapes stencil. Here’s what Visio 2007 records with each parameter:

• ODBCDataSource=<name> This parameter stores the name of the data source used to link the shape. In the example, the data source is Visio Database Samples, which specifies a the sample Access database that comes with Visio 2007.

• ODBCQualifier=<name> For a data source that includes multiple databases, this parameter would specify the specific database.

• ODBCTable=<name> This parameter identifies the table in the database to which the shape is linked. In the example, the shape is linked to the Office – Card Details table.

• <Number> This parameter shows the number of key fields that have been specified. In the example, only one key field is used. You can select multiple key fields when you link shapes using the Database Wizard rather than the Link To Shapes command.

• Name=<name> This parameter identifies the shape data or ShapeSheet cell that stores the key field. In the example, the Prop.Name shape data is the key field.

• <Number> This parameter identifies the number of fields that are linked to shape data. In the example, three fields are linked. Each field is then described in the remaining parameters.

• <Field name>=<cell name>=<number> Each of the remaining parameters identifies a specific field, the name of the cell where Visio 2007 stores its values (cells that represent shape data are prefaced with “Prop.”), and the manner in which Visio 2007 evaluates the current value of the data. In the example, the Title field is linked to the Prop.Title cell. The number 0 reflects the setting for the Evaluate As options and refers to a string. In programming terms, this number is a constant. A selection of possible values are shown in Table 24-1.