Visio5: Using the Visio Database Wizard with Microsoft Excel (254591)


The information in this article applies to:

  • Microsoft Visio Standard 5.0
  • Microsoft Visio Professional 5.0
  • Microsoft Visio Technical 5.0
  • Microsoft Visio Enterprise 5.0
This article was previously published under Q254591

SUMMARY

You can use the Visio Database Wizard to link Visio shapes and drawings to databases created in applications that support the Microsoft Open Database Connectivity (ODBC) standard. ODBC is an interface that allows applications to access, view, and modify data from a variety of databases. ODBC-compliant database programs include Microsoft Excel, Microsoft Access, Borland Paradox, and Lotus Notes. When you install these programs, you can choose to install their ODBC components at the same time. Visio 5.0 also comes with ODBC components- choose the Custom/Complete installation option to install them with Visio 5.0.

You can use the Visio Database Wizard to perform a variety of tasks. For example, you can:

  • Generate business cards for employees using information stored in a personnel database.

  • Link Visio master shapes to fields in an inventory specifications database. When you link shapes to fields in a database, updating the information in the shape automatically updates the corresponding database field, and vice-versa.

  • Track furniture and equipment by linking an inventory database to an office layout drawing. After you link an inventory database to an office layout, you can access data, such as the serial number, maintenance record, and manufacturer, through the shapes' custom property fields.


The remainder of this article introduces you to the Database Wizard by showing you how to link Visio shapes to records in a database file that you create in Excel, and what happens when you update linked information.

Getting started

To learn about the Database Wizard, this article shows you how to:

  • create a small Excel database and Visio master.

  • link the master to one of the database records.

  • make changes to ShapeSheet cells, which automatically updates the database.

  • make changes to the database, which automatically updates ShapeSheet cells.


To follow the procedures in this article, you need to be familiar with database terminology, Microsoft Excel, and Visio, including how to work with Visio masters and stencils and the ShapeSheet window. In addition, your computer should have:

  • Visio 5.0.

  • Microsoft Excel 5.0 or later.

  • ODBC components, including the Microsoft Excel Driver.


To find out if ODBC components and the Excel driver are installed:

  1. In Windows 95 and Windows 98 or Windows NT 4.0, click Start, point to Settings, and then click Control Panel.

    If the 32bitODBC or ODBC icon appears in Control Panel, ODBC is installed. If the icon does not appear, you can run Visio Setup to install the ODBC components. For more information, search for "Installing ODBC" in Visio Help.
  2. If ODBC is installed, double-click the icon.
  3. Click the ODBC Drivers tab and verify that Microsoft Excel Driver is included in the list. If it is not, please refer to the Excel documentation for information about installing it.

Creating the Excel Database and Visio Master

Before you can create a link with the Database Wizard, you need to create an Excel database and the Visio master that you want to link to it.

To create the demo database in Excel:

  1. Open Excel 5.0 or later and start a new workbook.
  2. In one of the workbook's worksheets, type the following:
    NameDescriptionColor ValueHeightWidth
    Red blockSmall red block21 in2 in
    Green blockMedium-size green block32 in3 in
    Blue blockLarge blue block43 in4 in


  3. To identify the data as a table, select the worksheet cells that contain the data, and then on the Insert menu, point to Name, and then click Define.
  4. In the Define Names dialog box, type Blocks, click Add, and then click OK.
  5. On the File menu, click Save As. In the Save As dialog box, type DBDemo.xls, select a location, and then click Save.
To create the Visio master and stencil file:

  1. If Visio is not running, start it. In the Choose A Drawing Template dialog box, click Blank Drawing, and then click Open.

    If Visio is running, on the File menu, click New, and then click Drawing.
  2. Click the Rectangle tool on the Standard toolbar, and then draw a rectangle on the drawing page. After you draw the rectangle, click the Pointer tool.
  3. On the File menu, point to Stencils, and then click Blank Stencil. A blank stencil appears to the left of the drawing page.
  4. With the Pointer tool, drag the rectangle shape onto the green area of the stencil.

    The rectangle becomes a master named Master.0.
  5. On the File menu, click Save As, and in the Save As dialog box, do the following:
    1. For Save As Type, make sure Stencil (*.vss) appears as the selected file type.

      If Stencil (*.vss) is not the selected type, click Cancel, click the title bar of the Stencil window, click File, and then click Save As again.
    2. For Save In, select a location.
    3. For File Name, type DBDemo.vss.
    4. Click Save, and then click OK in the Properties dialog box.
  6. Click the Drawing window title bar, and then on the File menu, click Save As. In the Save As dialog box, select a location and name the file DBDemo.vsd.
  7. Click Save, and then click OK in the Properties dialog box.

Linking a Record in the Excel Database to the Visio Master

To link a record in the Blocks database that you created in Excel to the master that you created in the Visio stencil, you run the Database Wizard.

In the following procedures, you'll use the Database Wizard to:

  • Select the Visio master you want to link.
  • Define DBDemo.xls as an ODBC data source.
  • Select a primary key - one or more fields in the database that uniquely identify each record.
  • Add events and actions to Visio to control what type of information the drawing and the database exchange.


Tip: For help with the Database Wizard, you can click Help or More Info on a wizard page. If you need more information about the wizard, refer to Chapter 22, "Creating data-driven shapes and drawings," in Using Visio 5.0 Products.

To select the Visio master that you want to link:

  1. In Visio, on the Tools menu, click Macro, click Database, and then click Database Wizard.
  2. On the first wizard page, click Next. On the second page, click Link A Shape to a Database Record, and then click Next.
  3. On the third page, select A Master on a Visio Stencil, and then click Next.
  4. In the drop-down list, click DBDemo.vss - the stencil you created. In the Select A Master To Modify list, select Master.0, and then click Next.
To Define DBDemo.xls as an ODBC data source:

  1. On the next wizard page, click Create Data Source.
  2. In the Create New Data Source dialog box, click User Data Source, and then click Next.
  3. On the next page, select Microsoft Excel Driver (*.xls), and then click Next.
  4. Click Finish.
  5. In the ODBC Microsoft Excel Setup dialog box, do the following:
    1. For Data Source Name, type Blocks.
    2. For Description, type Database Wizard demonstration.
    3. Click Select Workbook.
  6. In the Select Workbook dialog box, do the following:
    1. Find and select DBDemo.xls.
    2. The Read Only check box is selected. Click to clear it.
    3. Click OK, and then click OK in the ODBC Microsoft Setup dialog box.
  7. Click Next.
To choose a key field for the database:

  1. On the next wizard page, for Number Of Fields, select 1, and then click Next.
  2. On the next page, for Field, select Name, and then click Next.
  3. For the default value of the key field, select Red Block, and then click Next.
To add events and actions to the Visio drawing:

  1. On the next page, do the following:
    1. Under Shape Events, check Include an On Drop Event With the Shape and select Refresh Shape On Drop.
    2. Under Right Mouse Actions, check the first three boxes, and then click Next.
  2. The next page shows Prop.Name as the ShapeSheet cell that stores the primary key value. Click Next.
To link ShapeSheet cells to fields in a database record:

  1. On the next wizard page, do the following to specify the cells in the master's ShapeSheet window that link to fields in the Blocks database:
    1. Under Cells, click FillForegnd; under Database Fields, click Color Value; and then click Add.
    2. Under Cells, click Height; under Database Fields click Height; and then click Add.
    3. Under Cells, click Width; under Database Fields, click Width; and then click Add.
    4. Click Automatic, and then click Next.
  2. Click Finish.

    The FillForegnd, Height, and Width ShapeSheet cells are linked to the Color Value, Height, and Width fields in the Excel database. The Description is linked to the new Custom Properties row named Prop.Description. The master icon in the stencil updates to show a shape with a red fill.
To update the Visio stencil and drawing:

  1. In Visio, click the stencil title bar, and then, on the File menu, click Save.
  2. Click the Drawing window title bar, and then, on the File menu, click Save.

    The master named Master.0 on the DBDemo stencil is linked to the Red Blocks record in the Blocks database. Because the master is linked, each instance that you drag onto a drawing page is also linked.

Working with linked records and shapes

This section shows you how to update shapes linked to records and vice-versa, and how to find information about the links in the ShapeSheet window.

To create an instance of the linked master:
  • In Visio, open DBDemo.vsd, and then drag an instance of Master.0 onto the drawing page.

    The rectangle's dimensions (1 in. x 2 in.) and fill color (red) match the dimensions and color fields in the Red Block database record.


To examine the ShapeSheet cells that are linked to the database:

  1. With the shape selected, on the Window menu, click Show ShapeSheet. On the Window menu, click Tile.
  2. Right-click the ShapeSheet window and, on the shortcut menu, click View Sections.
  3. In the Sections dialog box, check Shape Transform, Fill Format, User-defined Cells, and Custom Properties. Click OK.
  4. Examine the ShapeSheet cells that are linked to the database cells:
    1. In the Shape Transform section, Height and Width cells match the Height and Width fields (columns) of the Red Block record (row) in DBDemo.xls.
    2. In the Fill Format section, the FillForegnd cell contains the number 2.

      The FillForegnd cell must contain a number from 0 to 23. This number corresponds to a color in the current color palette (click Color Palette on the Tools menu to find out which color is assigned to each number).
    3. In the User-defined Cells section, the User.ODBCField1 row contains the name of the field that you defined as the primary key, and the User.ODBCKey1 row names the ShapeSheet cell that's linked to this field.

      The User.ODBCLink rows specify other database fields that correspond to ShapeSheet rows and cells.

      The User.ODBCMirror rows store copies of the data recorded in the Custom Properties and other ShapeSheet cells. Mirror cells contain the last valid data Visio retrieved from the database. The wizard uses these cells to determine whether values have changed, either in the database or in the shape, since the most recent synchronization of the data.
    4. In the Custom Properties section, the wizard adds the Prop.Name row for the Name field and the Prop.Description row for the Description field in DBDemo.xls.
  5. Close the ShapeSheet window by clicking the Close box in the upper-right corner of the window.

    NOTE: For more information about ShapeSheet cells, search for "Shapesheet" in Visio Help.
Updating Database Records from the Visio Drawing

You can update records in the database by changing the shape's attributes in the Visio drawing. The Name and Description fields in the database are linked to the shape's custom property data. To update these fields, you change the custom property data.

To change the Name and Description fields:

  1. In the Visio drawing, select the rectangle and, on the Shape menu, click Custom Properties.
  2. In the Description section of the Custom Properties dialog box, type Small rose block, and then click OK.
  3. Right-click the rectangle and click Update Database Record.

    When you open DBDemo.xls, the Description field for the shape now shows "Small rose block." To update the fields other than Name and Description, you can change the shape's attribute in the Visio drawing. For example, changing the fill color in the Visio drawing changes the number in the Color Value field in the database.
To update database records by changing shape attributes:

  1. In the Visio drawing, select the rectangle and, on the Format menu, click Fill.
  2. In the Fill dialog box, for Foreground, select white, and then click OK.
  3. Right-click the rectangle, and then click Update Database Record.

    When you open DBDemo.xls, the Color Value field shows the number 1--the value for white.
TIP: You can update database fields for all shapes on a drawing page by pointing to Macro on the Tools menu, and then clicking Database Update.

Adding Records to the Database

In previous sections, you learned how to update existing database records by modifying the Visio shape that's linked to it. This section shows you how to add new records from the Visio drawing.

To add a new record, you change the attribute of the linked shape that you selected for the primary key to a value that does not already exist in the database.

To add a record to the database:

  1. Select the rectangle on the Visio drawing page and, on the Shape menu, point to Size, and then click Position.
  2. In the Size & Position dialog box, enter 4.5 in. for the width and 0.5 in. for the height. Click OK.

    The shape resizes to show the new dimensions.
  3. On the Format menu, click Fill. In the Fill section of the Fill dialog box, select yellow for the foreground color, and then click OK.
  4. On the Shape menu, click Custom Properties. In the Custom Properties dialog box, type Yellow Block for the shape's name and New yellow shape for the description, and then click OK.
  5. Right-click the shape, and then click Update Database Record.

    A message box appears asking if you want to insert a record into the database table.
  6. Click Yes.

    The Database Wizard adds the Yellow Block record to the Blocks.xls database.
You can also change values in the database to update the shape in the Visio drawing.

To update a shape by changing database record values:

  1. In Blocks.xls, do the following:
    1. Change the Height and Width fields in the Red Block record to 1.5 inches.
    2. Change the number in the Color Value cell of the Blue Block record from 4 (blue) to 6 (magenta).
    3. Save the file.
  2. In the Visio drawing, right-click the blue rectangle, and then click Refresh Shape Properties.

    The shape's fill color changes from blue to magenta.
  3. Drag a new instance of the rectangle from the stencil onto the drawing page.

    The new shape is a red square with 1.5-in. sides.
  4. With the square shape selected, on the Shape menu, click Custom Properties. In the Custom Properties dialog box, type Yellow Block for the name, and then click OK.
  5. Right-click the square shape, and then click Refresh Shape Properties.

    The shape's fill color and dimensions change to reflect the dimensions in the Height, Width, and Color Value fields of the Yellow Block database record.
TIP: You can simultaneously refresh all the shapes on a drawing page by pointing to Run Add-on on the Tools menu, and then clicking Database Refresh.

To create a drawing based on Blocks.xls:

  1. In Visio, on the Tools menu, click Macro, Database, and then click Database Wizard.
  2. On the first wizard page, click Next. On the second page, click Create a Linked Drawing or Modify an Existing One, and then click Next.
  3. On the page that appears, click Create A New Drawing, and then click Next. Click Next again.
  4. On the page that asks you to choose the options to use for the monitored drawing, check the options you want. For a description of each option, click More Info.

    After you check the options you want, click Next.
  5. On the page that appears, select Blocks as the data source for the drawing, and then click Next. Click Next again.
  6. The next page that appears asks you to choose a Visio master shape to represent records from the data source. Under stencil, select DBDemo.vss; under Masters, select Master.0. Click Next, and then click Finish.
The wizard creates a new drawing and opens the Database Drawing Monitor. Click More Info in this window for information about its purpose and how to use it to update database fields and shape properties. If you close the Database Drawing Monitor and want to open it again, right-click the database drawing page, and then click Launch Database Monitor.
Modification Type:MajorLast Reviewed:11/25/2003
Keywords:KB254591
©2003 Microsoft Corporation. All rights reserved.