DonnaM » Blog Archive » Connecting Visio to a database

Connecting Visio to a database

This post outlines how to connect your Visio diagram to a database to reduce manual entry of data and improve consistency. I’m sure there are plenty of ways to improve on this, but this is a low-tech method that works reasonably well.

Note: I am using my personal site for the examples, Visio Professional 2003 and Microsoft Access 2002. I haven’t tested on anything else – hopefully the instructions will help you to trouble-shoot other combinations.

Background

I recently worked on a small project that was a little different to my previous IA projects. It involved a larger set of deliverables than normal – a site map showing every page and a wireframe for each page. I decided to create all deliverables using Microsoft Visio, knowing that I would be able to use its ability to layer backgrounds to reduce duplication of elements.

I knew from the start that I risked inconsistency in the large deliverable set – for example, it would be easy to have slightly different page titles in the site map and relevant wireframe. I initially thought I just needed to be careful, but after re-working the site map a few of times (manually renaming and renumbering shapes each time) I decided to look for a better way to manage data in the site map and wireframes.

Visio and databases

Visio allows you to connect shapes to data in a database and display database fields automatically in the shape.

You can connect individual shapes, or masters on a stencil – the process is the same for each. You can connect to a range of databases – I use Microsoft Access as I’m already familiar with it and I can easily send the database file to my client.

Create and populate the database

You need to first create a database and set a primary key – you cannot display data from the primary key in Visio, but will use it to select data, so make sure it is something recognisable. For this example, I used the same unique identifier for the page and the primary key. My database also has fields for page title and template/layout (I use different colour schemes and navigation for different parts of the site).

Screenshot of database table, showing ID, page number, page title columns

Populate the database with the data you will need for the site. This becomes the main source of data for the deliverables, and could be used for other parts of the project.

Connect a shape to the database

You connect a Visio shape and the database using the ‘Database Wizard’ (Tools>Add-ons>Visio Extras>Database Wizard). The wizard leads you through fairly well – below are some tips and instructions for the tricky spots only:

  • If you are trying to connect to a master on a stencil, make sure the stencil is editable first
  • If you are trying to connect to a shape and the list of shapes is very long, cancel out of the wizard, select your shape and start again. Your shape will be selected already.
  • The dialog box below sets up the connection between the shape and the database field. If you would like the shape to show the text from the database, select Shape.Text and the relevant database field and click add.

A screen shot of the dialog box used to select the shape element and database field

How I use this

For this project, my site map shapes contained a title, id number and layout/template. I connected each part of the shape to the relevant database record. When I duplicated the shape (copy>paste), instead of retyping the text, I connected it to the correct database record by right-clicking and selecting ‘select database record’ from the contextual menu.
image of some of the shapes on my site map, showing title, template and identifier
My wireframes use database connected shapes in a number of places:

  • background pages contain shapes for navigation items
  • the title bar contains the page number and title
  • the content area has a page title in the content
  • the notes area has the template or layout name
  • the notes area may also have a list of in-line links in the content

example of a wireframe showing where database fields are included, linked to a larger version

Other comments

This won’t be appropriate for all IA projects, but may be helpful for any where you are trying to manage large numbers of pages and repeating information in a number of places.

Visio is far from perfect, and one of the most annoying things is that you can’t ask it to automatically update shapes from the database. If you have changed records in the database, you have to right-click each shape and select ‘refresh shape properties’. So consider carefully before you do connect to a database, because this is almost as tedious as retyping everything (but not quite).

I’m sure I have left out plenty, so please leave a comment if you have questions.

3 Responses to “Connecting Visio to a database”

  1. Austin Govella Says:

    This is really awesome. Thanks for writing this up. I’ve poked at the connectivity a couple of times, but Visio can be daunting.

  2. Jos Says:

    Hello, but ther is a way to tell VISIO to autoupdate Data from the Database, It’s in the Database Wizard ->Create a linked drawing or mdigy an existing one ->Add database actions and events to a drawing page->

    And of course you can use the right click on teh page and update all.

  3. Bill Morein Says:

    Just thought I’d mention that this is an area that we have really simplified for the next version of Visio. You can check out some information on this in Eric Rockey’s (the Visio Lead Program Manager) weblog:

    http://blogs.msdn.com/eric_rockey/archive/2005/12/06/500395.aspx