Chapter 24: Connecting Diagrams and Databases


Besides making great presentation graphics, Microsoft Office Visio 2003 diagrams are visual repositories of shape information. You can export that information to a database or spreadsheet program or update that information based on the contents of a database or spreadsheet program. For example, suppose you want access to the furniture dimensions stored in an office layout to add to a planning database. You can export shape data, such as dimensions and other properties, to a database, which is a one-way procedure. Or suppose you want to associate shapes that represent parts with the specification data stored in a manufacturing database. You can also create a two-way link between database records and shapes. This latter feature gives you the ability to create dynamic diagrams that respond to data from an external source. In effect, your diagram becomes a visual front end to your database.

This chapter discusses the way Visio works with databases so that you can export shape properties and link shapes to databases.

About Visio 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 property (including custom properties and Shape- Sheet 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.

Visio 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 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.

start sidebar
Inside Out
For those without Access

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 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\Visio11\1033.

end sidebar

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.

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

Cross-Reference

For details about creating custom properties, see “Defining Custom Properties,” page 175.

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.

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

Cross-Reference

For details about how Visio represents colors in the ShapeSheet, see “How Visio Applies Color to Shapes,” page 706.

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 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.




Microsoft Office Visio 2003 Inside Out
Microsoft Office Visio 2003 Inside Out (Inside Out (Microsoft))
ISBN: 0735615160
EAN: 2147483647
Year: 2003
Pages: 209

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net