About Visio 2007 and Databases
Shapes are a visual representation of what can be considered a flat-file database. That is, each shape represents one record. Each shape’s data (including shape data and ShapeSheet data) represents one field in a record. A data 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, every type of shape data or ShapeSheet cell is like a column, and each value is a cell’s content. Because Visio 2007 organizes shape information in this fashion, it’s easy to export data from shapes as well as link external data sources to shapes.
Visio 2007 includes several commands and wizards for exchanging data. This chapter focuses primarily on two options:
Export To Database With this command, you can export data from shape data to a database or file. This command does the same thing as the Database Export Wizard, which is complicated by its multiple-page approach.
Link To ODBC Database With this command, you can create a two-way link between database records and shape data. Two-way means that changes you make in Visio 2007 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 in one dialog box, which makes it easier to use.
Visio 2007 is compatible with any database that supports the Open Database Connectivity (ODBC) standard, which includes Microsoft Office Access as well as Microsoft Office Excel.
Inside Out: Those without Access can still connect with Visio 2007You don’t actually need to have a database program to take advantage of Visio 2007’s database connectivity tools. Visio 2007 can read information from a database file through ODBC whether or not you have a program, such as 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 2007 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 FilesMicrosoft OfficeOffice121033. |
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 2007 regards information about each shape as yet another type of shape data that you can view in the ShapeSheet window. It just so happens that regular shape data have another interface—the Shape Data window. When you export data or link to a database, you specify ShapeSheet cells that contain the values you want to use—including shape data 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 2007 stores data in the Shape Data section of the ShapeSheet window. Visio 2007’s internal name for data—and the name you see when you use the Export To Database or Link To ODBC Database command—differs from the name displayed in the Shape Data window, as Figure 24-1 shows.
Figure 24-1. The Shape Data window displays the data labels, which are stored in ShapeSheet cells along with their value and other settings.
For details about creating shape data, see the section titled “Defining Shape Data” in Chapter 6, “Storing Data in Diagrams.”
Exporting and Linking ShapeSheet Information
You can export or link to any of the built-in shape data that are stored in the ShapeSheet window for a shape. Besides shape size and position data, such as width and height, you can export and link to formatting data, 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.
Figure 24-2. The FillForegnd cell reflects the setting (1, or white) in the Color box of the Fill dialog box.
For details about how Visio 2007 represents colors in the ShapeSheet window, see the section titled “How Visio 2007 Applies Color to Shapes” in Chapter 23, “Defining Styles, Patterns, and Colors.”
The ability to export or link to shape formatting data offers a great deal of flexibility. For example, you can export the Width data 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.
Note
To find out what a ShapeSheet cell controls, click a cell, and then press F1. Visio 2007 displays the Help topic about that cell.
When you connect Visio 2007 shapes to a database, Visio 2007 requires a unique field to associate each shape with the appropriate database record. By default, Visio 2007 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 globally unique identifier (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.