Copyright: (C) 1993-1997 Visio Corporation
Citation: Visio Wizard Help (C) 1993-97 Visio Corporation


Open Database Connectivity and the Database Wizard

Open Database Connectivity (ODBC) is a standard Microsoft interface that allows applications to access, view, and modify data from databases.

For the Database Wizard to link Visio shapes and drawings to a database, the database must be an ODBC data source.

You can set up a database as an ODBC-data source by running the ODBC Database Administrator from the Windows 95 Control Panel. For details, choose Help > Visio Help, click Index, then type "ODBC."


Selecting a master to represent database records or serve as the basis for new masters
Selecting a master to represent database records

The master you select must already be linked to a record in the database. Each field in the database record must be linked to a ShapeSheet cell in the master.

Stencil Choose the stencil that contains the linked master.

Browse Click to locate the stencil that contains the linked master.

Masters The linked masters from the stencil you choose display here. Click to select the master you want to use to represent the database records.

If you haven't previously run the Database Wizard to link a master to a database record, exit the wizard now. Choose Tools > Macros > Database > Database Wizard. On the first screen, click Next. On the second screen, choose Link A Shape To A Database Record, click Next, then follow the wizard screens. Link each field in the database to a ShapeSheet cell.


Selecting database link and naming options

Keep Database Links In New Masters Check to maintain links between new masters and the database records they represent. If you maintain links, you can add on-drop events and right-mouse actions to masters (and their instances) to keep the values in database records and the shapes' ShapeSheet spreadsheets synchronized.

If you don't maintain the links, the masters' ShapeSheet cells contain the data that existed in the database fields at the time you generated the new masters but won't contain any reference to the database table in which the values can be found. The benefit of not maintaining the links is that the masters can be distributed widely. If you make changes to the database, however, you must run the Database Wizard again and generate new masters that reflect the changes.

Generate Names From Primary Key Fields Click to name the new masters after the primary-key fields in the database table. For example, if your database has a primary-key field called Name that includes Blue Block, Red Block, and Green Block records, the masters will be named Blue Block, Red Block, and Green Block. Master names can only be 31 characters in length. If the key field names are longer than 31 characters, the master names are truncated.

Base Names On The Original Visio Master Click to name the new masters after the master on which you based those new masters. For example, if the original master is the Box shape, the new masters are named Box1, Box2, Box3, and so on.


Selecting a master to serve as the basis for new masters

Stencil Choose the stencil that contains the master you want.

Browse Click to locate the stencil that contains the master.

Masters The masters from the stencil you choose display here. Click to select the master you want to use as the basis for new masters.


Selecting actions and events for a drawing page

When you add an action to a drawing page, the action becomes available as a command on a menu. You can choose a command by right-clicking a drawing page that has no shapes selected.

When you add an event to a drawing page, you define how shapes in the drawing respond to particular user actions, such as opening a file.

Page Actions

Refresh Shapes On Page Adds the Refresh command to the drawing page right-click menu. When you right-click the page and choose Refresh, values in the shape's ShapeSheet cells are refreshed to match values in the database records to which they are linked. Choosing Refresh on the drawing page right-click menu refreshes all shapes on the page at once.

Update Shapes On Page Adds the Update command to the drawing page right-click menu. When you right-click the drawing page and choose Update, values in the database records are updated to match values in the ShapeSheet cells to which the records are linked. Choosing Update on the drawing page right-click menu updates database records for all shapes on the page at once.

Events

Refresh Linked Shapes On Document Open Refreshes ShapeSheet cell values for all shapes in your drawing each time you open the drawing. When you refresh shapes, values in each shape's ShapeSheet cells are refreshed to match values in the linked database records.

Periodically Refresh Based On NOW Function Refreshes ShapeSheet cell values for all shapes in a linked drawing at specific intervals defined by the NOW function. When you refresh shapes, values in each shape's ShapeSheet cells are updated to match values in the linked database records. When you choose this option, the wizard adds two right-mouse actions to the drawing page. To start or stop the continuous refresh, right-click the drawing page when no shapes are selected, then choose the action of want.

The NOW function goes out of date at an interval you set in the VISIO.INI file. Each time the NOW function goes out of date, it returns the current date and time and runs the Database Refresh Add-on, which refreshes the values in the linked ShapeSheet cells.

Tip: To set the interval at which the NOW function goes out of date, change the Animation setting in the [application] section of the VISIO.INI file. The default setting is 60000 milliseconds (one minute).

For details about how adding actions and events affects the drawing page ShapeSheet spreadsheet, choose Help > Visio Help, click Index, then type "Database Wizard actions and events."


Database Drawing Monitor

The Drawing Monitor manages the links bewteen Visio shapes and database records. If you delete a shape, the Monitor informs the database table, which deletes the corresponding record. If you add a shape, the Monitor adds a record to the database table. If you change a value in one of the shape's linked cells, the Monitor informs the database table, which changes the value in the corresponding record.

The Drawing Monitor also helps you track version control. The last valid values for database records are stored in the ODBCKeyMirror and ODBCMirror cells in the User-defined Cells section of the ShapeSheet spreadsheet. When you change a shape, the Monitor compares the Mirror cell values with the database record values. If the database record values have been modified, the Monitor prompts you to discard the shape changes and revert to the values in the database or to update the database record based on values from the shape.

Monitored Drawing Pages Lists the open linked drawing pages. The Drawing Monitor can monitor the database-drawing links for several drawing pages at a time.

Automatically Distribute Shapes On The Page Check to array shapes in the drawing selected in the Monitored Drawing Pages list in rows and columns on the drawing page.

Uncheck if the database table includes fields you've linked to the PinX and PinY cells in the Shape Transform section of the ShapeSheet spreadsheet. These shapes will be dropped at the PinX and PinY location.

Automatically Scale Drawing Page Check to have the wizard set the page size to just accommodate the shapes in the drawing pages selected in the Monitored Drawing Pages list.

Refresh Click to copy new values from database records into ShapeSheet cells, add new shapes based on newly-created database records, distribute shapes on the page, or scale a drawing page for the drawing selected in the Monitored Drawing Pages list.

Remove Click to remove a selected drawing from the Monitored Drawing Pages list. The Monitor stops monitoring the link for the drawing you remove.

Close Click to close the Drawing Monitor and stop monitoring the links between any open drawings and their linked databases. To re-open the Monitor, you must run the Database Wizard and choose the Create a Linked Drawing or Modify an Existing One option.


Selecting an ODBC data source

Only databases that are defined as ODBC data sources and stored in the Windows Registry or as data source name (*.dsn) files appear on this screen.

If the database you want to link to does not appear, click Create Data Source to define the database as a data source without leaving the wizard.

If you have defined a database as a data source and stored it in .dsn format in a location other than the default, click Browse For File DSN to locate the file. The default location for .dsn files is C:\Program Files\Common Files\ODBC\Data Sources.


Selecting a database object

When you're linking a Visio shape or drawing to a database, you must specify a database object, such as a table, for the shape or drawing to link to. For some database programs, such as SQL Server, you must specify a database from the list of databases on the server.

Database Choose the database containing the object you want to link to. This option is available only for database programs, such as SQL Server, where connecting to a data source gives you access to multiple databases.

Owner Choose All Users or the name of an object creator from the drop-down list. Choosing a specific creator limits the number of objects that display in the list below. This option is available only for database programs, such as SQL Server, where you can specify an object creator.

Object Types Check the type of object you want to link a Visio shape or drawing to, then choose the specific object from the list. For details about database objects, see your database program documentation.

Define Table Click if you have no database object defined or if you want to create a new table. You can define a table without leaving the Database Wizard.


Defining a data source table

Table Name Type the name of the new table you want to define.

Name Type the name of a field you want to add to the new table.

Primary Key Check if the field specified under Name is part of the primary key (a field or fields that uniquely identify each record) for the database table.

Unique Check if the field specified under Name cannot have duplicate values.

Required Check if the field specified under Name cannot have a null value.

Type Select a data type for the field specified under Name. The data types you can choose depend on the database driver associated with the database you're linking to.

Length Type the size in bytes of a variable length field.

Decimal Type the number of decimal places supported by the field. This applies only to numeric fields that are not integers.

Fields Lists the fields in the new table you're defining.

Add Click to add the field specified under Name to the table you're defining.

Remove Click to remove the selected field from the table you're defining.

When you finish defining the table, click Create.

For details about defining database tables, see your database program documentation.


Selecting the fields in the primary key

The primary key is the field or combination of fields that uniquely identifies each record. You may already have defined a primary key in your database table.

The Database Wizard needs to match one shape to each record in the database table, so it will try to determine how many fields comprise the primary key and what those fields are. If the wizard can't determine that information automatically, you need to indicate the number of fields and choose the field names from the drop-down lists.

Once you define the primary key, the wizard asks you to choose default values for each field in the key. When you choose values, you set a default record to which your selected master or shape instance will link. You can also click None to choose no default values, if you wish.


Selecting events and actions

You can add actions and on-drop events to linked shapes.

When you add an action, the action becomes available as a command on the menu that appears when you right-click the shape. When you add an on-drop event, you define how a linked shape responds when you copy it or drop it on the drawing page.

Shape Events

Include An On-drop Event With The Shape Check to add a Refresh (to update ShapeSheet cells to match values in database records) or Select (to select a database record) on-drop event to the linked shape.

Refresh Shape On Drop If you're linking a master, check to automatically update the linked ShapeSheet cells for each instance you create when you drop the master. If you're linking a shape instance, check to update the linked ShapeSheet cells for each copy you create when you duplicate the instance.

Select Record On Drop Check to select a new database record each time you drop a linked master or copy a linked shape instance.

Actions

Select Database Record Check to make the Select Database Record command available on the menu that appears when you right-click the linked shape. If you choose the command, you can specify a new database record to link to the shape.

Refresh Shape Cells Check to make the Refresh Shape command available on the menu that appears when you right-click the linked shape. This command looks up the database record to which the shape is linked. If the values in the shape's ShapeSheet cells differ from the values in the database, the ShapeSheet cell values are then refreshed.

If you don't check this option, you can still refresh selected shapes or all the shapes on a page by choosing Tools > Macro > Database > Database Refresh.

Update Database Record Check to make the Update Database Record command available on the menu that appears when you right-click the linked shape. This command looks up the database record to which the shape is linked. If the values in the shape's ShapeSheet cells differ from the values in the database, the database field values are then updated.

If you don't check this option, you can still update database records linked to selected shapes or to all the shapes on a page by choosing Tools > Macro > Database > Database Update.

Delete Shape And Database Record Check to make the Delete Shape And Database Record command available on the menu that appears when you right-click the linked shape. This command deletes the shape and its linked database record simultaneously.


Selecting the cell for storing the primary key value
Selecting the cell(s) for storing the primary-key value(s)

On this wizard screen, you identify the ShapeSheet cells that correspond to each primary-key field in the database. The values for the primary-key fields are copied into the corresponding ShapeSheet cells.

If you're linking a shape or drawing for the first time

The wizard generates a cell name for each ShapeSheet cell and displays the name in the Cell drop-down list.

If you decide to use the generated name, the wizard creates a cell with that name in the Custom Properties section of the linked shape's ShapeSheet spreadsheet.

If you want the primary-key field to be linked to one of the shape's existing custom-property fields (for which a Custom Properties ShapeSheet cell already exists), choose that field from the drop-down list.

If you've already run the wizard to link a shape or drawing

The wizard automatically selects the ShapeSheet cells you originally linked to the primary-key fields. You can choose another cell by selecting a custom property from the drop-down list.


Linking ShapeSheet cells to database fields

On this wizard screen, you identify the ShapeSheet cells that correspond to each field (other than the primary-key fields) in the database. Then, when you link a shape to a database record, the ShapeSheet cells reflect the values in the corresponding fields.

Some database fields contain data that match the data typically stored in an existing ShapeSheet cell. For example, if a database includes a Width field, the data in that field corresponds to data typically stored in the Width cell in the Shape Transform section of the ShapeSheet spreadsheet. In cases like these, select the field, select the matching cell, then click Add to link the field to the cell.

Some database fields contain data that doesn't match any data typically stored in an existing ShapeSheet cell. In cases like these, click Automatic to have the wizard create new ShapeSheet cells in the Custom Properties section of the Shapesheet for all database fields. Or, double-click an individual field to create a corresponding custom property cell.

Cells Lists the ShapeSheet cells that you can link to a field in a database.

Database Fields Lists all the fields in the database to which you're linking the Visio shape or drawing. You can choose to link some or all of the fields to ShapeSheet cells. However, if your goal is to create a drawing that is a graphical representation of each record in a database table, you must link each field to a ShapeSheet cell.

Evaluate Data As Determines how Visio evaluates data when the data is copied from a database field to a ShapeSheet cell.

If the data in the field is a string, Visio can evaluate it either as a Value or as a Formula. A Formula is simply a string, such as Width, 5in., or 2mm. * 3mm, for which Visio returns a value. If the data in the field is numeric, Visio can evaluate it as a number or as a measurement with specific units attached.

Information about how Visio evaluates data is stored, in the form of numeric codes, in the ODBCLink row in the User-defined Cells section of a linked shape's ShapeSheet spreadsheet. Value is indicated by a 0, Formula is indicated by a 1, and Number is indicated by 32. For a list of the numeric codes corresponding to measurement units, in the online Visio Automation Reference, search for "result property."

Remove To remove a link or links between a ShapeSheet cell and database field, under Links, select the link(s) you want to remove, then click here. You can also double-click a link to remove it and you can select and remove more than one link at a time.


Creating or modifying database links for a drawing page
Specifying options for the monitored drawing

You can choose the following options for distributing the shapes on the page and for monitoring communication between the linked drawing and database.

Automatically Distribute Shapes On The Page Check to have the wizard array the shapes in rows and columns on the drawing page. One shape represents each database record.

Leave this option unchecked when the database table includes fields that specify shape location (that is, if database records are linked to the PinX and PinY cells in the Shape Transform section of the ShapeSheet spreadsheet). Shapes then appear on the page at the specified PinX, PinY locations.

If PinX and PinY are not linked and you don't check this option, all shapes are dropped at 0,0.

Automatically Scale Drawing Page Available only when the previous option is checked. Check to have the wizard set the drawing page size to exactly accommodate the shapes it distributes. When you don't check this option, shapes may be drawn outside page boundaries.

Leave this option unchecked if you're creating linked shapes that include text, such as business cards. Text will not scale properly when you scale the page.

Launch The Drawing Monitor On Document Open Check to start the wizard's Drawing Monitor each time you open the linked drawing you're creating. The Monitor manages communication between the drawing and the database table. When the Monitor is open, changes you make to shapes in the drawing are automatically reflected in database records.

Leave this option unchecked if you want to make changes in the drawing without automatically affecting database records. You can still manually pass changes back and forth between the drawing and the database table by choosing Tool > Macro > Database, then choosing Database Refresh or Database Update.

Add 'Launch Monitor' Right-mouse Action To The Page Check to add a Launch Monitor command to a shortcut menu you can open by right-clicking the drawing page. Then, you can start the Drawing Monitor by right-clicking the page and choosing the command.

Access The Database Table In Read-write Mode Check to have the database change in response to changes in the drawing (in addition to changing the drawing in response to changes in the database). Uncheck if you don't want drawing changes to affect the database.

Automatically Refresh Page Based On Global Setting By choosing Tools > Macro > Database > Database Settings, you can set a global refresh interval that applies to all your drawings that are linked to databases. Refresh interval is the amount of time that elapses before changes made to a database are automatically passed to its linked drawing. Check this option if you want the global refresh setting to apply to the current drawing. Uncheck to refresh the drawing by choosing clicking Refresh on the Drawing Monitor or by choosing Tools > Macro > Database > Database Refresh.


Open Database Connectivity and Database Export

Open Database Connectivity (ODBC) is a standard Microsoft interface with which applications can access, view, and modify data from databases.

The Database Export Wizard exports Visio data to database tables created in ODBC-compliant database applications such as Microsoft Access 7.0, Microsoft SQL Server, and Oracle SQL Server.

To find out if your database application is ODBC-compliant, check the application's documentation.


Selecting shapes to export data from

All Shapes On The Page Click to export data from all shapes on the current drawing page.

Selected Shapes On The Page Click, then click Select Now to select the shapes with data you want to export.

All Shapes On One Or More Layers Click to export data only for shapes assigned to specific layers.

Layers Available only when you choose All Shapes On One Or More Layers. Select the layers that include shapes with data you want to export. Click a layer to select it. Shift+Click to select multiple layers.


Selecting data to export

On this screen, you choose the custom-property fields and ShapeSheet cells that contain data you want to export to the database table.

Show All Cells Check to display all the cells in the ShapeSheet spreadsheets of the shapes for which you're exporting data. Uncheck to display only the shapes' custom-property fields. Custom-property field names begin with "Prop."

Visio Cells And Fields Lists the ShapeSheet cells and custom-property fields associated with the shapes for which you're exporting data. To indicate the cells and fields containing data you want to export, select them, then click Add. To export data from all cells and fields, click Add All.

Cells And Fields To Export Lists the cells and fields containing data the wizard will export. To remove a cell or field from this category, select it, then click Remove. To remove all cells and fields, click Remove All.

A custom-property field is a database field in which you can enter data relevant to a shape. Many Visio masters come with custom-property fields assigned. For example, shapes on the flowchart stencils have Cost, Duration, and Resources custom-property fields. You can also add custom-property fields of your own using the Custom Properties Editor. To run the editor, choose Tools > Macro > Custom Properties Editor.

After a shape has custom-property fields, you can add data to the fields. To add data, right-click the shape, then choose Properties. Enter the data in the Custom Properties dialog box.


Selecting or creating an ODBC data source

If the database you want to export to isn't listed on this screen

  The database doesn't exist, or

  The database is not defined as an ODBC data source.

To create a new database and define it as an ODBC data source, or to define an existing database as an ODBC data source, click Create New Data Source, then follow the screens.


Specifying export table details

On this screen, you indicate the name of the database table to which you want the shape data exported and choose the type of data you want in the key field for the table.

Database The name of the ODBC data source you identified on the previous screen appears here.

Table Name If the database has tables, they are listed in the lower text box. To export shape data to an existing table, select the table in the lower text box. The name appears in the upper text box. To create a new table and export shape data to that table, type a name for the new table in the upper text box.

Key Field The wizard creates a key field in the database table called ShapeKey. You can type a new name for the field. The key field is the field that uniquely identifies each shape.

Key Type Choose either ShapeID or GUID. ShapeID is a number assigned to a shape by Visio according to the order in which the shape was created on the page. A GUID is a unique null-terminated 39-character string assigned to each shape when you export its data. No two shapes will ever have the same GUID but because ShapeID is assigned by creation order, if you delete a shape then drop another, the second shape will have the same ShapeID as the deleted one.

Make Key Field The Primary Key For The Table By making the key field a primary key field, you can increase the speed with which your database application can access the field. Not all database applications can mark fields as primary key fields.


Specifying the export mapping details

On this screen, you specify how you want the data to be interpreted in the database table.

For each ShapeSheet cell or custom-property field from which you're exporting data, the wizard assigns a default data type and a name, type, and size for the field the data will occupy in the database. You can change the defaults if you want.

Visio Data Select the cell or field for which you want to specify details.

Evaluate Data As Choose how the data from the selected cell or field will be evaluated in the database.

Field Name Type a name (or column heading) for the database field the ShapeSheet cell or custom-property field data will be exported to. By default, the name is the cell or field name.

Field Type Choose a type for the database field.

Field Size Type a number that indicates how many characters a person can enter in the database field.

Field Decimal Type a number that indicates how many digits to the right of the decimal point a person can enter in the database field. Available only when the database table includes a SQL_NUMERIC or SQL_DECIMAL field.