Process Management Additional Resources

Visio and SharePoint Designer Workflows

Linking Drawings using the Database Wizard

Overview

Part of the power behind Visio is its ability to communicate with other applications. In this module you will learn about connecting Visio to databases using the Database Wizard. In another module we will cover the Visio feature called Data Linking. That is a distinct feature from the Database Wizard. As you cover this material, keep in mind that Visio has no direct interface into a database. All database communications are being handled via an ODBC OLE Controller.

You can link the data that resides in a particular master shape or instance of a master shape to a database via the transparent ODBC interface. Visio’s Database capabilities can automate this process for you. These include the Link to Database Add-on, the Export to Database Add-on and the Database Wizard. They will link the values of a ShapeSheet’s Shape Data section’s selected rows to a database created in an application compliant with the ODBC standard.

If the database is revised once the linkage is established, you can refresh the values in the ShapeSheet cells to reflect the revisions. If you change the value of a linked Shape Data cell in Visio, you can update the database to reflect the changes.

This section covers techniques for linking shapes to ODBC compliant sources. It explores the following concepts in some detail:

• 
  General concepts for working with databases.
  
• 
  The kind of information that can be stored in Visio ShapeSheet cells.
  
• 
  The steps to follow to successfully connect the database to a Visio shape.
  
• 
  The particular actions and activities that the user can use once the linkage is complete.
  
• 
  The steps to follow to successfully export shape data to a file.
  

Database Concepts

A database is a repository for information. In this section, we are concerned with two general classes of databases: flat files and relational databases.

Flat files are effectively spreadsheets of information. Each column heading in a spreadsheet is the same as a field definition in the database. Each row in the spreadsheet is the same as a record entry in the database.

Relational databases are more complex. A given table may have a field item that is an index into another table, which stores further information relating to that indexed item.

A table is comprised of numerous field definitions. Each field definition has its own data type.

Each table definition also contains one or more key fields. Key fields are unique indexable items that assist in locating information as well as querying and sorting the information contained in the table.

Database Concepts (Continued)

In order for a linkage to be established between Visio and a database, several items must be established and properly initialized.

1. 
   A named data source must be created. This named data source is any ODBC compliant data source such as Access, Microsoft Excel, SQL Server, Oracle, and many others. These data sources must be initialized via the 32-bit ODBC system and given a valid source name.
   
2. 
   The actual database (such as Dbsample.mdb) must be specified.
   
3. 
   The table or query within the database must be identified.
   
4. 
   The primary key field(s) in the record must be identified.
   
5. 
   Finally, the particular record in the selected table or query must be identified.
   

Data Types

Visio’s Shape Data section is capable of storing several data types, but Visio stores data that resides in Shape Data Value cells (and in all Visio ShapeSheet cells) in one of two general manners: as Formulas or as Values.

Formulas are always stored as strings. These strings can be up to 64K in size, and as you will see in a moment, these strings can evaluate to numerous data types.

Values, on the other hand, are stored in many different possible modes. These modes are:

• 
  Strings
  
• 
  Numbers
  
• 
  Boolean Values
  
• 
  Binary Data
  

Numbers can be stored as:

• 
  Numbers
  
• 
  Currency
  
• 
  Dates
  
• 
  Times
  

Boolean values always hold either a TRUE or FALSE value.

Binary data is kept in native binary format.

Visio Cell Values and Formulas

As you know, in Visio ShapeSheet cells, information may be stored either as values or as formulas. The cell values and formulas are handled in slightly differing manners as they relate to database connectivity.

When information is stored as a value, it may be thought of as taking on one of several forms: “Unit-ed” or “Unit-less” numbers, strings, or Booleans.

A “Unit-ed” cell contains values that are number + unit pairs, as in 3.5 in., or 26.825 cm. This represents a scalar distance or vector information.

A “Unit-less” cell contains values, which are numbers only, as in 16, or 3.1415926.

A string is simply a piece of text, such as “Network Router.”

Note:

In the ShapeSheet, a piece of text is surrounded by double-quotation marks, which designate the text as a string.

A Boolean is simply a non-quoted string as TRUE or FALSE only.

When information is stored as formulas, Visio and its database functionality can work the formula in one of two differing manners: either as an interpreted string or as a non-interpreted string. See FORMAT() and FormatResult in the developer reference section for further discussions on string interpretation.

Connections to Visio SmartShapes

When the Link to ODBC Database Add-on is run, it will allow the user to connect fields within records in an ODBC compliant database to individual cells within a Visio ShapeSheet of a master or an instance of a master.

These connections can be made to many different ShapeSheet cells including:

• 
  Shape Data
  
• 
  Shape text
  
• 
  Data1, Data2, and Data3
  
• 
  User-defined cells
  
• 
  Any named Visio ShapeSheet cell
  

Note:

At least one “connection” must be made between a field in a record and a Visio Shape Data row to construct a valid database linkage.

The Link to ODBC Database Add-on

To perform the actual linkage between the data source and a given master or instance of a master in Visio, you use the Link to Database Add-on.

You can attach the data source to:

• 
  A master shape on a stencil opened in edit mode.
  
• 
  A master shape in a Visio drawing’s document stencil.
  
• 
  An instance of any master on a Visio drawing page.
  
• 
  A shape on a page with no connection to a master.
  

Try it! Link to Database

1. 
   Open \Demos\Vol2\Linking Visio Drawings to a Database\Link to Database.vsd.
   
2. 
   Select the shape you would like to create the link within.
   

You have the choice of creating a database link to either the Personal computer shape on the page, or that shape’s master on the document stencil. To select the master in the Document stencil, first right-click the master shape, choose Edit Master > Edit Master Shape, and then select the master shape.

3. 
   From the View tab, choose Add-ons > Visio Extras > Link to ODBC Database…
   
4. 
   On the Link to Database dialog, from the ODBC data source Name list, choose Visio Database Samples.
   
5. 
   You do have the ability to create a new data source at this point (Create button). We will be covering those steps in the next demonstration.
   
6. 
   From the Table/view list, select Network – Computers.
   

The Field links list is populated with the fields present in that table.

Note:

At this point, you can also create a new table in the already established database, modify the details of a field, or delete a field by utilizing their respective dialog buttons.

7. 
   Click the Advanced button.
   
8. 
   From the Advanced dialog, under Shape drop event, click the Select record radio button, and click OK.
   
9. 
   Click the OK button on the Link to Database dialog to complete the linkage process.
   
10. 
    Open the ShapeSheet to view the Shape Data section.
    

Note:

When a shape is linked to a database, the Link to ODBC Database Add-on inserts the Shape Data section, and appropriate cell values, and a User-defined section into the ShapeSheet if they are not already present (or adds rows to them if they are). The User-defined section is where the Wizard stores information about the primary key for the database, the database fields that are linked to the ShapeSheet cells, and the last valid data retrieved from the database.

11. 
    Insert text fields for each property value to be displayed. For example, to add a field after the text “Owner” and link it to the custom property Owner, click in the text in the space following “Owner:” Choose Insert > Field… from the menu. In the Field dialog choose Category > Shape Data. This will display the shape data fields of the shape in the field area. Choose Field > Owner.
    

This will display as text on the shape the shape data fields to the user once the shape data values have been filled in from the database.

12. 
    If you chose to link the master on the document stencil, drag a new instance of it onto the page and select a new record for that shape.
    
13. 
    If you chose to link the shape on the page, make a copy of it on the page and complete the same action.
    

Database Actions and Activities

Once the Link to ODBC Database Add-on has run, the user will find several items added to the shape’s right-click menu. These Action items allow the user to perform the following functions:

• 
  When the Shape is dropped from a stencil, the user can be automatically prompted to select a database record to associate with the instance.
  
• 
  When the database is changed, a user can refresh the shape’s property values with current data.
  
• 
  When the Shape Data value is changed in the Visio SmartShape®, the database can be updated to reflect the change or a new database record can be created based on the new Key value.
  
• 
  When the shape is deleted, the database record can be deleted as well.
  

Note:

Be careful not to delete database records from the Link to Database demonstration’s linked table using the right click menu item. It is a Visio installed sample table.

The Export to Database Add-on

The Visio Export to Database Add-on functionality is an excellent way to take data that you have defined in shapes on a drawing page and export that data to a table in a database, such as with a network diagram.

Note:

When you export shape data from your drawing page to an existing table, Visio takes all the data from that table and replaces it with fields and data from you existing drawing.

Try it! Export to Database

1. 
   Open \Demos\Vol2\Linking Visio Drawings to a Database\Export to Database.vsd.
   
2. 
   From the View tab, choose Add-ons > Visio Extras > Export to Database. If you can see an ODBC Data Source error, choose Close and go on to the next step.
   
3. 
   Click the Create… button.
   
4. 
   On the 1st Wizard screen, choose User Data Source
   
5. 
   On the 2nd Wizard screen, choose Microsoft Access Driver (*.mdb)
   
6. 
   On the ODBC setup dialog, name your data source and click the select button.
   
7. 
   On the Select Database dialog, navigate to the local directory you copied Export Database.mdb, select it, and click OK.
   
8. 
   Back in the ODBC setup dialog, click the OK button.
   
9. 
   In the Export to Database dialog, make sure the cells you want to export are in the list. If they are not, click the Add button, and make your selections. You can also Modify and Delete cells from the export list.
   
10. 
    When all the cells you would like to export data from are in the Cells to export list, click OK.
    

The RMA to update the table in this case is accessible at the page level, providing an update for all shapes on the page.

Lab 2.1 Export Shape Data to Database

Points to Consider

1. 
   Basic Network.vst is a native Visio template.
   
2. 
   To create the new data source, the database table will need to reside locally on your hard drive.
   
3. 
   You can use the existing table in the database, or create a new one in the Export to Database dialog.
   

Summary: Linking Visio Drawings to a Database

Visio shapes can be linked to external data sources using the Database Wizard and the linked data is stored in the shape’s ShapeSheet. Within Visio this information is retained within the shape and stored with the drawing. Shape Data can be modified from within Visio and can be displayed using Text Fields or through the Shape Data Window. Database tables can also be updated from Visio through the linkages created by the Database Wizard.

Shape Data can be exported to a database, creating a new table, using the Export to Database Add-on.

Directory: download
download -> Performance Tuning Guidelines for Windows Server 2008 May 20, 2009 Abstract
download -> Northern England’s set-jetting locations
download -> Bbc archives: Beyond the programme download
download -> Occupational health and safety
download -> Dynatest 3031 lwd light Weight Deflectometer
download -> Forth coming competitive exams
download -> English Olympiad Tasks: 10 th form Аудіювання, 10 клас. Текст
download -> Brush High School Morning Announcements Friday, September 05, 2014 all school
download -> Year 9 The Arts — Music
download -> Years 7 and 8 standard elaborations — Australian Curriculum: Music draft

Download 0.72 Mb.

Share with your friends: