About Visio and Databases

3 4

Shapes are a visual representation of what can be considered a flat-file database. That is, each shape represents one record. Each shape property (including custom properties and ShapeSheet properties) represents one field in a record. A property value corresponds to the value of a field in a shape record. If you're more accustomed to thinking in spreadsheet terms, each shape is like a row, each custom property or ShapeSheet cell is like a column, and each value is a cell's contents. Because Visio organizes shape information in this fashion, it's easy to export data from shapes as well as link external data sources to shapes.

newfeature!  Visio includes several commands and wizards for exchanging data. This chapter focuses primarily on two new options:

• Export To Database. With this command, you can export data from shape properties to a database or file. This command does the same thing as the Database Export Wizard, which is complicated by its multiple-screen approach.
• Link To Database. With this command, you can create a two-way link between database records and shape properties. Two-way means that changes you make in Visio can be written back to the original database, and changes to database records can be used to update shapes. This command provides most of the functionality of the Database Wizard but in one dialog box instead of many wizard screens, which makes it easier to use.

Visio is compatible with any database that supports the Open Database Connectivity (ODBC) standard, which includes Microsoft Access as well as Microsoft Excel.

InsideOut

---

You don't actually need to have a database program to take advantage of Visio's database connectivity tools. Visio can read information from a database file through ODBC whether or not you have a program, such as Microsoft Access or Microsoft SQL Server, installed on your computer. You can still export shape data in a database format and link to records in a database; you just can't open the database to edit it. Visio even includes a sample Access file (Dbsample.mdb) that you can use for testing the database linking feature. The sample file is installed by default in C:\Program Files\Microsoft Office\Visio10\1033\Samples\Visio Extras.

Understanding Shape-Database Connections

Whether you link database records to shapes or export shape data to a database, you work with the cells and values in the ShapeSheet window. If the drawing page represents the graphical view of a shape, the ShapeSheet window represents the spreadsheet view of the same shape. Thus, Visio regards custom properties as yet another shape property that you can view in the ShapeSheet window. It just so happens that custom properties have another interface—the Custom Properties window. When you export data or link to a database, you specify ShapeSheet cells that contain the values you want to use—including custom properties and their values. If you don't typically work in the ShapeSheet view, linking or exporting data forces you to become more familiar with its nomenclature.

Visio stores a custom property in the Custom Properties section of the ShapeSheet window. Visio's internal name for properties—and the name you see when you use the Export To Database or Link To Database command—differs from the name displayed in the Custom Properties window, as Figure 24-1 shows. For example, if you create a property named Manufacturer, Visio refers to the property by the name prop.Manufacturer in the ShapeSheet and uses this name when you export or link properties.

Figure 24-1.  The Custom Properties window displays the property labels, which are stored in ShapeSheet cells along with their value and other settings.

For details about creating custom properties, see "Defining Custom Properties."

Exporting and Linking ShapeSheet Information

You can export or link to any of the built-in shape properties that are stored in the ShapeSheet window for a shape. Besides shape size and position properties, such as width and height, you can export and link to formatting properties, such as fill color or shape text. If you're unfamiliar with the ShapeSheet window, it helps to remember that most ShapeSheet cells have a one-to-one correspondence with a command option or dialog box setting that is familiar. For example, the FillForegnd cell corresponds to the Color setting in the Fill dialog box, as Figure 24-2 shows.

For details about how Visio represents colors in the ShapeSheet, see "How Visio Applies Color to Shapes."

Figure 24-2.  The FillForegnd cell reflects the setting (1, or white) in the Color box of the Fill dialog box.

The ability to export or link to shape formatting properties offers a great deal of flexibility. For example, you can export the Width property for all the dimension lines in an office layout to create a record of room dimensions. Or suppose you have a database of equipment that includes the color of a part. You could link the color records to shapes by specifying the FillForegnd cell so that the database controls the color of a shape.

Tip

---

To find out what a ShapeSheet cell controls, click a cell, and then press F1. Visio displays the help topic about that cell.

When you connect Visio shapes to a database, Visio requires a unique field to associate each shape with the appropriate database record. By default, Visio uses the shape ID that it creates when you add a shape to the drawing page. The shape ID is a sequential number based on the order in which the shape was created on the page. In a drawing where identical shapes can represent different database records, as in a space plan or piping and instrumentation diagram, you can specify an internally generated unique ID called a GUID, which looks something like this:

 {2287DC42-B167-11CE-88E9-0020AFDDD917} 

A GUID is a unique null-terminated, 128-bit number assigned to each shape when you export its data. No two shapes in the same drawing file will have the same GUID.