Integrating Visio 2007 and Access 2007

Summary: Use Automation code to create a Visio drawing from data defining the contents of a drawing and the connectivity between shapes stored within Access tables. (18 printed pages)

Visimation, Inc.

October 2006

Applies to: Microsoft Office Visio 2007, Microsoft Office Access 2007

Contents

  • Overview

  • Generating Visio Diagrams from Shape and Connectivity Data Stored in Access Tables

  • Visio Template

  • Conclusion

  • About the Author

  • Additional Resources

Overview

By default, Microsoft Office Visio 2007 and Microsoft Office Access 2007 provide integration capabilities. Using the Visio feature, Link Data to Shapes, you can link shapes in a Visio drawing to data stored outside Visio, such as an Access database. You can achieve a greater degree of integration by using Automation code. Linking to Access data is similar to linking to Microsoft Office Excel data. For an example of using the Data Selector Wizard to link to Excel data, see Integrating Visio 2007: Introduction to Integrating Visio with Other Microsoft Programs.

Generating Visio Diagrams from Shape and Connectivity Data Stored in Access Tables

The following sample code generates a Visio diagram from data stored in Access database tables. The database contains two tables: an Items table that contains information about which shapes to include in the drawing and a Connections table that contains information about connectivity between the shapes. The shapes are placed in the drawing from a template. The Dynamic Connector in Visio connects the shapes in the Visio drawing. The locations of shapes on the drawing page are not stored in the Access tables. Instead, you can use the Layout method in Visio to position the shapes on the drawing page.

The tables contain fields that you must copy into the shape data of the shapes. The shapes themselves use this information to set the shape text and properties.

NoteNote

In previous versions of Visio, shape data was known as custom properties.

Visio Template

Building capabilities such as shape data and text fields into the shapes reduces the amount of code required to create automated solutions. For example, no code is required to display text on the shapes because text fields defined within the shape masters provide the text. Also, you do not need to set the color of the shape. Shape color is derived from the Status field for the shape. The formulas for deriving the color are also built into the shapes.

Figure 1. Visio template with preconfigured shapes

Visio template with preconfigured shapes

Figure 2. Finished drawing image

Finished drawing image

Data Stored Externally to Visio in Access Tables

Figure 3 shows the Items table. Each record in the Items table corresponds to one shape in the drawing. The shape in the drawing is an instance of the Visio master, named Item. Visio Template shows the template image.

Figure 3. The Items table

The Items table

The Items table contains the following fields:

  • ID. A value that uniquely identifies the record in the table. This value is generated by Access.

  • Name. The name given to the instance of the shape master in the Visio drawing that this record is associated with. The value is stored as shape data within the shape instance and displayed as text on the shape, using a text field in Visio.

  • Status. Stored as shape data within the shape instance. The shape's color is derived from this value based on formulas in the Visio ShapeSheet spreadsheet.

  • Amount. A quantity value stored as shape data within the shape instance. This is displayed as text on the shape, using a text field in Visio.

  • Document. A Web address stored as shape data within the shape instance. A hyperlink defined in the shape master refers to this value.

Figure 4. The Connections table

The Connections table

Figure 4 shows the Connections table. Each record in the Connections table corresponds to one connection shape in the drawing. The connector is an instance of the Visio master named Connection. To view the template image, see Visio Template.

The Connections table contains the following fields:

  • ID. A value that uniquely identifies the record in the table. This value is generated by Access.

  • Name. Stored as shape data within the connector and displayed as text on the shape, using a text field in Visio

  • ItemFrom. Stored as shape data within the connector. It contains the ID value of the Item shape where the connector starts.

  • ItemTo. Stored as shape data within the connector. It contains the ID value of the Item shape where the connector ends.

Code Example: Reading Data and Generating a Visio Drawing

using System;
using System.Data;
using System.Data.OleDb;
using System.Collections;
using System.Windows.Forms;

using Visio = Microsoft.Office.Interop.Visio;

The drawing creation is initiated by using an external event such as clicking a button on a form (not shown in this example) to call the GenerateDrawing method below. This is a high-level entry point that reads the data and builds the Visio drawing. The setUpDocument method initializes a new Visio document. The readData method reads the data from the Access database into internal structures. The columnsExist method validates that the required fields are defined in the database tables. The buildVisioDrawing method uses the data from the internal structures and builds the Visio drawing.

internal static void GenerateDrawing(
    string fileName)
{
    // Open a document.
    Visio.Document newDocument = setUpDocument();
    if (newDocument != null)
    {
        // Link the database information to the document's
        // DataRecordset collection.
        if ((readData(newDocument, fileName)) &&
   (columnsExist(newDocument)))
        {
            // Create the Visio drawing.
            buildVisioDrawing(newDocument);
        }
    }
}

The setUpDocument method launches Visio and creates a drawing based on a template. The template name is held in the constant _VisioTemplateName.

private const string _VisioTemplateName = "WhitePaperSample_Access.vst";

private static Visio.Document setUpDocument()
{
    Visio.Document newDocument = null;

    try
    {
        // Open Visio.
        Visio.Application visioApplication = new Visio.Application();

        // Open a new document based on the template.
        newDocument = visioApplication.Documents.OpenEx(
            Application.StartupPath + "\\" + _VisioTemplateName,
            (short)Visio.VisOpenSaveArgs.visOpenCopy);
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }

    return newDocument;
}

The readData method reads the data from the Access database into internal data structures. The parameter fileName contains the name of the Access file. It returns a value of True if the data is read successfully; otherwise it returns False.

// Database constants.
// These include the connection, table, and column values.
private const string _ADOConnectionString =
    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};User Id=admin;Password=;";
private const string _ADOSelectString = "Select * From {0}";

private const string _ItemsTableName = "Items";
private const string _ConnectionsTableName = "Connections";

// DataRecordset indicies.
// These are the indicies into the Visio document's DataRecordset 
// collection for the Item and Connections data.
private static int _ItemsDataRecordsetIndex;
private static int _ConnectionsDataRecordsetIndex;

private static bool readData(
    Visio.Document visioDocument,
    string fileName)
{
    bool itemsReadSuccessful = false;
    bool connectionsReadSuccessful = false;

    try
    {
        // Build the connection string.
        string connectionString = 
            string.Format(_ADOConnectionString, fileName);

        // Link the items to the Visio document by adding a data
        // recordset, using the connection and command information.
        visioDocument.DataRecordsets.Add(connectionString,
            string.Format(_ADOSelectString, _ItemsTableName),
            0, _ItemsTableName);
        _ItemsDataRecordsetIndex = visioDocument.DataRecordsets.Count;
        itemsReadSuccessful = true;

        // Link the Connections to the Visio document by adding a data
        // recordset, using the connection and command information.
        visioDocument.DataRecordsets.Add(connectionString,
            string.Format(_ADOSelectString, _ConnectionsTableName),
            0, _ConnectionsTableName);
        _ConnectionsDataRecordsetIndex =      visioDocument.DataRecordsets.Count;
        connectionsReadSuccessful = true;
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }

    return (itemsReadSuccessful && connectionsReadSuccessful);
}

The columnsExist method validates that the required columns exist in the data. It returns True if the required columns exist; otherwise it returns False.

private const string _IDColumn = "ID";
private const string _FromColumn = "ItemFrom";
private const string _ToColumn = "ItemTo";
private const string _NameColumn = "Name";

private static bool columnsExist(
    Visio.Document newDocument)
{
    // Ensure that each required column exists.
    bool foundMissingColumn = false;

    try
    {
        // Check the Items table.
        Visio.DataRecordset itemsData =
            newDocument.DataRecordsets[_ItemsDataRecordsetIndex];
        if (itemsData.DataColumns[_IDColumn] == null)
        {
            foundMissingColumn = true;
        }

        // Check the Connections table.
        Visio.DataRecordset connectionsData =
            newDocument.DataRecordsets[_ConnectionsDataRecordsetIndex];
        if ((connectionsData.DataColumns[_NameColumn] == null) ||
            (connectionsData.DataColumns[_FromColumn] == null) ||
            (connectionsData.DataColumns[_ToColumn] == null))
        {
            foundMissingColumn = true;
        }
    }
    catch
    {
        foundMissingColumn = true;
    }

    return ! foundMissingColumn;
}

After you read all of the data from the Access tables, use the buildVisioDrawing method to create the Visio drawing. The details of adding the shapes to the drawing and setting up the connections are in the addItemsToDrawing method and the addConnectionsToDrawing method.

private static void buildVisioDrawing(
    Visio.Document newDocument)
{
    try
    {
        // Create the drawing on the first page.
        Visio.Page drawingPage = newDocument.Pages[1];

        // Add the shapes to the page.
        addItemsToDrawing(drawingPage);
        addConnectionsToDrawing(drawingPage);

        // Lay out the shapes.
        drawingPage.Layout();

        // Show the Data Explorer window.
  drawingPage.Application.DoCmd(
   (short)Visio.VisUICmds.visCmdDataExplorerWindow);

        // Determine the bounding box of the shapes.
        double left, bottom, right, top, width, height;
        drawingPage.BoundingBox(
            (short)Visio.VisBoundingBoxArgs.visBBoxUprightWH,
            out left, out bottom, out right, out top);

        // Resize the page.
        width = right - left + 1;
        height = top - bottom + 1;
        drawingPage.PageSheet.get_CellsSRC(
            (short)Visio.VisSectionIndices.visSectionObject, 
            (short)Visio.VisRowIndices.visRowPage, 
            (short)Visio.VisCellIndices.visPageWidth).
            FormulaU = width.ToString() + " in";
        drawingPage.PageSheet.get_CellsSRC(
            (short)Visio.VisSectionIndices.visSectionObject,
            (short)Visio.VisRowIndices.visRowPage,
            (short)Visio.VisCellIndices.visPageHeight).
            FormulaU = height.ToString() + " in";
        drawingPage.CenterDrawing();

        // Force printing to a single page.
        drawingPage.PageSheet.get_CellsSRC(
            (short)Visio.VisSectionIndices.visSectionObject,
            (short)Visio.VisRowIndices.visRowPage,
            (short)Visio.VisCellIndices.visPageDrawSizeType).
            FormulaU = "1";
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

The addItemsToDrawing method adds all of the master Item instances to the drawing. The addShapesToDrawing method drops the shape at a random location. Later, you use the page Layout method to position the shapes. The data fields from the Access table, Name, Status, Amount, and Document are set into the shape's shape data.

// Drawing-building constants.
// These include the master and cell names.
// The property cells use the same names as the column names.
private const string _VisioItemMasterName = "Item";
private const string _VisioConnectionMasterName = "Connection";
private const string _VisioPropCellPrefix = "Prop.";

// Drawing-building fields.
// This is a map from each Item's ID in the database table to the
// corresponding Item shape that is added to the drawing. This is
// needed to determine which shapes to connect based on the connection
// information in the database.
private static Hashtable _ItemShapeIndexMap = new Hashtable();


private static bool addItemsToDrawing(
    Visio.Page drawingPage)
{
    bool itemsAdded = false;

    try
    {
        // Add the shapes to the drawing page. The returned list contains
        // the shape IDs of the added shapes.
        Array shapeIDs;
        addShapesToDrawing(drawingPage, out shapeIDs);

        if (shapeIDs != null)
        {
            // Loop through each new shape, adding the shape to the map.
            for (int index = shapeIDs.GetLowerBound(0);
                index <= shapeIDs.GetUpperBound(0);
                index++)
            {
                // Get the shape.
                short shapeID = (short)(int)(shapeIDs.GetValue(index));
                Visio.Shape newShape = drawingPage.Shapes.get_ItemFromID(shapeID);

                // Add the shape to the map.
                // This is needed to connect the shapes.

                // The primary key in the database is used in the database
                // connection table, so we need to save the mapping from 
                // this database key to the corresponding shape. The 
                // database key value was placed into each shape's Prop.ID 
                // value when the database-linked shapes were added to the
                // drawing.
                int databaseKeyValue =
                    newShape.get_Cells(_VisioPropCellPrefix + _IDColumn).
                    get_ResultInt((short)Visio.VisUnitCodes.visNumber, 0);

                _ItemShapeIndexMap.Add(databaseKeyValue, newShape.Index);
            }

            itemsAdded = true;
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }

    return itemsAdded;
}

The addShapesToDrawing method adds the shapes that represent the items in the database to the drawing page. When you pass the page to add the shapes to as a parameter, the method returns the array of shapes you created as an out parameter.

private static void addShapesToDrawing(
    Visio.Page drawingPage,
    out Array shapeIDs)
{
    shapeIDs = null;

    try
    {
        // Use the Items master.
  Visio.Master itemMaster = drawingPage.Document.Masters[_VisioItemMasterName];

        // Create one shape for each row in the database's Items table.
        Visio.DataRecordset itemsRecordset =
            drawingPage.Document.DataRecordsets[_ItemsDataRecordsetIndex];
        Array dataRowIDs = itemsRecordset.GetDataRowIDs(string.Empty);
        int numberOfShapes = dataRowIDs.GetLength(0);

        // Create the arrays. Arrays are needed for each new shape's
        // master and location.
        Array mastersToDrop = Array.CreateInstance(typeof(object), numberOfShapes);
        Array xyArray = Array.CreateInstance(typeof(double), (2 * numberOfShapes));

        // Calculate a random location for the shape. Randomizing
        // the placement of shapes helps lay out shapes more
        // efficiently when the Layout method for this page is called.
        Random randomLocation = new Random();

        // Prefill the array items required for the DropManyLinkedU
        // method.
        for (short index = 0; index < numberOfShapes; index++)
        {
            // The master to drop.
            mastersToDrop.SetValue(itemMaster, index);

            // The position of the drop.
            xyArray.SetValue(
                (int)(randomLocation.NextDouble() * 10), (index * 2));
            xyArray.SetValue(
                (int)(randomLocation.NextDouble() * 8), (index * 2 + 1));
        }

        // Do the drop.
        // Because the shapes are linked to the Items table in the database,
        // shape data values are added to each new shape:
        // ID: The Items master already contains a row labeled "ID", so the 
        // value is set in that row. Because the row is invisible, it does not 
        // appear in the Shape Data window.
        // Status, Amount, Name, and Document: The Items master already
        // contains rows with these labels, so the values are set in those 
        // rows. Because these rows are visible, they appear in the 
        // Shape Data window.
        // Owner: The Items master does not contain a row labeled "Owner", 
        // so a new row is added to each shape. The name of the row is 
        // "_VisDM_Owner", the label is "Owner", and the row is visible in 
        // the Shape Data window.
        drawingPage.DropManyLinkedU(
            ref mastersToDrop, ref xyArray,
            itemsRecordset.ID, ref dataRowIDs, false,
            out shapeIDs);
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

The addConnectionsToDrawing method adds one Connector shape to the drawing for each row in the Connections table, dropping the connectors at random locations. Later, gluing them to the Item shapes causes the connectors to snap to their proper locations. The Name field of the table is added to the shape data of the connector shape. This value is displayed as text on the shape. The display of the text is defined as a text field within the shape. To view the text displayed on the connector, see Visio Template.

private static bool addConnectionsToDrawing(
    Visio.Page drawingPage)
{
    bool connectionsAdded = false;

    try
    {
        // Use the Connections master.
        Visio.Master connectionMaster = 
            drawingPage.Document.Masters[_VisioConnectionMasterName];
        if (connectionMaster != null)
        {
            // Get the information about the connection data from the
            // linked data recordset.
            Visio.DataRecordset connectionsDataRecordset =
 drawingPage.Document.DataRecordsets[
     _ConnectionsDataRecordsetIndex];

            int nameColumnIndex;
            int fromColumnIndex;
            int toColumnIndex;

            getConnectionColumnIndicies(connectionsDataRecordset,
                out nameColumnIndex, out fromColumnIndex, 
 out toColumnIndex);

            // Loop through each Connection in the Connection table,
            // dropping new shapes in random locations.
            Random randomLocation = new Random();
            Array rowIDs = connectionsDataRecordset.GetDataRowIDs(string.Empty);

            foreach (int thisRowID in rowIDs)
            {
                // Populate the shape with information from the data row.
                Array rowData = connectionsDataRecordset.GetRowData(thisRowID);

                // Create the shape.
                Visio.Shape newShape = drawingPage.DropLinked(connectionMaster,
                    (randomLocation.NextDouble() * 10),
                    (randomLocation.NextDouble() * 10),
                    connectionsDataRecordset.ID,
                    thisRowID,
                    false);

                // Set the Name value.
                newShape.get_Cells(_VisioPropCellPrefix + _NameColumn).Formula =
                    "\"" + rowData.GetValue(nameColumnIndex).ToString() + "\"";

                // Glue the connection to the Item shapes.
                int shapeIndexFrom =
        (int)_ItemShapeIndexMap[
(int)rowData.GetValue(fromColumnIndex)];
                int shapeIndexTo =
                 (int)_ItemShapeIndexMap[
   (int)rowData.GetValue(toColumnIndex)];
                glueConnectionToItems(
                    drawingPage, newShape, shapeIndexFrom, shapeIndexTo);
            }

            connectionsAdded = true;
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }

    return connectionsAdded;
}

The getConnectionsColumnIndicies method examines the Connections data recordset to determine the column indices needed to read the connection data from each row. This step is needed because you must use the index instead of the column name to access the data recordset row information. The parameter fromColumnIndex is the index of the From column. The parameter toColumnIndex is the parameter of the To column.

private static void getConnectionColumnIndicies(
    Visio.DataRecordset connectionsDataRecordset,
    out int nameColumnIndex,
    out int fromColumnIndex,
    out int toColumnIndex)
{
    nameColumnIndex = 0;
    fromColumnIndex = 0;
    toColumnIndex = 0;

    // Loop through the columns in the Connections data recordset, 
    // checking each for a name that is needed.
    Visio.DataColumns connectionColumns = connectionsDataRecordset.DataColumns;
    for (int index = 1; index <= connectionColumns.Count; index++)
    {
        Visio.DataColumn connectionColumn = connectionColumns[index];
        if (connectionColumn.Name == _NameColumn)
            nameColumnIndex = index - 1;
        else if (connectionColumn.Name == _FromColumn)
            fromColumnIndex = index - 1;
        else if (connectionColumn.Name == _ToColumn)
            toColumnIndex = index - 1;
    }
}

Use the glueConnectionToItems method to glue the ends of the connection to the desired shape. The drawingPage object is the page that contains the connection shape and the shapes being glued to. The connectionShape object is the connector that is being glued. The fromShapeIndex object is the shape where the beginpoint of the connector is glued. The toShapeIndex object is the shape to which the endpoint of the connector is glued.

private static void glueConnectionToItems(
    Visio.Page drawingPage,
    Visio.Shape connectionShape,
    int fromShapeIndex,
    int toShapeIndex)
{
    try
    {
        // Glue the beginning of the connection to the "From" shape.
        Visio.Cell shapeCell =
            drawingPage.Shapes[fromShapeIndex].get_CellsSRC(
            (short)Visio.VisSectionIndices.visSectionObject,
            (short)Visio.VisRowIndices.visRowXFormOut,
            (short)Visio.VisCellIndices.visXFormPinX);
        Visio.Cell connectorCell =
            connectionShape.get_CellsSRC(
            (short)Visio.VisSectionIndices.visSectionObject,
            (short)Visio.VisRowIndices.visRowXForm1D,
            (short)Visio.VisCellIndices.vis1DBeginX);
        connectorCell.GlueTo(shapeCell);

        // Glue the end of the connection to the "To" shape.
        shapeCell =
            drawingPage.Shapes[toShapeIndex].get_CellsSRC(
            (short)Visio.VisSectionIndices.visSectionObject,
            (short)Visio.VisRowIndices.visRowXFormOut,
            (short)Visio.VisCellIndices.visXFormPinX);
        connectorCell =
            connectionShape.get_CellsSRC(
            (short)Visio.VisSectionIndices.visSectionObject,
            (short)Visio.VisRowIndices.visRowXForm1D,
            (short)Visio.VisCellIndices.vis1DEndX);
        connectorCell.GlueTo(shapeCell);
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

Conclusion

Integrating Visio 2007 drawings and data from Access can be accomplished in multiple ways. Visio 2007 offers some very nice new functionality for selecting, viewing, linking, and graphically displaying data from external sources such as Microsoft Office Access. Using the information in this article, you can gain even more control of your data by integrating Visio with Access data programmatically.

About the Author

Since 1997, Visimation has helped companies improve their productivity by providing visual software tools to ease business and technical tasks, and by offering a broad range of consulting services focusing on Microsoft Visio as a platform for rapid development of efficient Automation programs.

Additional Resources

For additional help about automating Visio and reading data from Access data sources, see the following resources:

For more information about Visio 2007 integration, see the following articles: