You can use Microsoft Office Visio to create a one- or two-way link between shapes in a drawing and records in a database table. By linking shapes to records, you can see your data in both a visual form (your drawing) and a tabular form (in your database table). After you've established the links, you can keep the drawing and table synchronized so that changes you make to either of them are reflected in both.
Four ways to create a linked drawing
You can use the Database Wizard and the Link to Database command to create a linked drawing in four different ways.
Link shapes in an existing drawing to a database table
You can use either the Database Wizard or the Link to Database command to link an existing drawing, such as an office plan or
a flowchart of your organization, or to database information, such as equipment lists or
personnel records. You can either link to an existing database table, or you can create
one from within the wizard and fill it out later.
Create one master shape that can link to any record in a database table
You can use the Database Wizard to link a master, such as a computer shape or a person
shape, or to database information, such as equipment lists or personnel records. You can
then drag the master onto the drawing page, choosing which database record you want
each instance of the shape to represent as you go. You can either link to an existing database table, or
you can create one from within the wizard and fill it out later.
Create a separate master shape for each record in an existing database table
If you want to represent many instances of the same record, you can use the Database Wizard to create a stencil that contains a separate master
shape for each record in an existing database table. For example, if you have a database table
of office chairs with each record (or row) representing a different model, you might want to
create many instances of each model in your office plan.
Create a drawing that represents an existing database table
You can use the Database Wizard to create a drawing that contains a separate shape for
each record in an existing database table.
Keeping your linked drawing and database table synchronized
As you go through the wizard you will have opportunities to add helpful ways to keep
your drawing and database synchronized. You can add right-mouse actions, on-drop
events, and automatic updating settings. You can choose to update the database to
reflect changes you made to shapes’ custom properties, or you can choose to refresh the
shapes with changes to the data in the linked table.
How shapes, records, tables, and fields relate in a database
Each shape in a drawing is represented by a record (or row) in a database table, and
each ShapeSheet cell is represented by a field (or column). Data in a ShapeSheet cell
appears as a value in the corresponding database table field.
The Visio database connections and ODBC
The Database Wizard and the Link to Database command share shape data with tables
created in database programs that are Open Database Connectivity (ODBC)-compliant, such as Microsoft Office Access and
Microsoft SQL Server.
ODBC is an industry standard that Microsoft programs can
use to access, view, and modify data from databases.
To determine whether your database program is ODBC-compliant, check the program's
documentation.
How is data stored in shapes?
When you link a shape to a database, you actually link cells in the shape's ShapeSheet (ShapeSheet: The spreadsheet that contains information about a shape; for example, its dimensions, angle, and center of rotation and the styles that determine the shape's appearance.) spreadsheet to fields in a database table. You choose which ShapeSheet cells are linked to fields in the database. Although you are usually linking shape custom properties to database fields, you can also link other ShapeSheet cells to database fields.
If no appropriate ShapeSheet cells exist, the Database Wizard or the Link to Database command creates new cells in the Custom Properties section of the ShapeSheet spreadsheet.
For example, if you have a database for colored blocks that includes fields for Block Name, Color, Height, and Width:
- In the Shape Transform section, the ShapeSheet spreadsheet includes cells for Height and Width.
- In the Fill Format section, the ShapeSheet spreadsheet includes a cell called FillForegnd.
In the process of linking a master to the colored blocks database (using the Database Wizard), you can choose to connect the Height, Width, and FillForegnd cells in the ShapeSheet spreadsheet to the Height, Width, and Color fields in the database. Because the ShapeSheet spreadsheet contains no cells that correspond to Block Name, you can have the wizard create such a cell in the Custom Properties section of the ShapeSheet spreadsheet.
Values in ShapeSheet cells match values in the database records to which the cells are connected. If you change the shape, you can pass the new values to the database. If you change the database, you can pass the new values to the shape's ShapeSheet spreadsheet.
User-Defined Cells section in the ShapeSheet
When Visio links a shape to a database, it also creates a User-Defined Cells section in the ShapeSheet spreadsheet, if that section doesn't already exist for the shape. In this section, Visio stores information about:
- The primary key for the database table.
- Which database fields are associated with which ShapeSheet cells.
- The last valid data retrieved from the database.
United Kingdom
Printer-Friendly Version
Hide All
