Visio's Database Links
Part I

Ralph Grabowski

In Part I you learned that shapes in a Visio drawing can be connected to an external database file. You learned some of the jargon that Visio uses for database connections. In this part, you learn how Visio makes that connection .

How Visio Connects with the Database
When you connect Visio with a database file, you connect the cells in the ShapeSheet to fields in the database table. Visio does the hard work of keeping track of which cells are linked to which fields. ODBC does the hard work of transferring data and commands back and forth.

If you are unfamiliar with databases -- even perhaps scared of the thought of dealing with database -- relax, because Visio has the Database Wizard, which steps you through the process of linking shapes in a drawing with database records.

Recall that every shape is backed up by a ShapeSheet. Every ShapeSheet consists of many sections, with names such as Shape Transform, Fill Format, and Custom Properties. Every section contains one or more cells. The important thing to remember is that cells are linked to database fields.

Visio uses the User-defined Cells section to store information about the database link:

Cell Name Meanings

ODBCTable Contains the name of the database table to which the shape is linked, such as "Network - Computers".

ODBCDataSource Contains the name of the ODBC data source to which the shape is linked, such as "Visio Database Samples".

ODBCLink1...n Specifies which cell in the shape is associated with a particular field in the database table, such as "PinX==XLocation==1" or "Prop.MachineTypecode==Machine Type code==32" or "Prop.Cost==Cost==111". There is one row in ODBCLinkn per field. Also specifies whether Visio evaluates data retrieved from a field as a string or a number and the units of measurement. The end of this chapter contains the complete list of codes.

ODBCKeyCell1...5 Contains the name of the Custom Properties row that stores the value for the key field named by the ODBCKeyFieldsn cells, such as "Prop.MachineSN". There is a maximum of five ODBCKeyCelln cells.

ODBCKeyField1...5 Contains the name of the cell that contains the value for a database key field, such as "MachineSN". The primary key is a field that uniquely identify each record. There is a maximum of five ODBCKeyFieldn cells.

ODBCKeyMirror1...5 Contains the most recently retrieved value for a linked database field, such as "1343 in.". Visio uses these cells to determine the record that the shape was linked to when it key value(s) was changed. There is a maximum of five ODBCKeyMirrorn cells.

ODBCMirror1...n Contain the last valid values retrieved from database fields. There is one row in ODBCMirrorn per field. Visio uses these cells to determine whether the values have changed since the last synchronization.

ODBCQualifier Contains the name of the database with the table. Note: This cell is used only when a single data source can support multiple databases.

Visio Data Types
As we saw earlier in this chapter, a database file consists of rigorous fields of data stored in rows of records. I noted that each field contained a specific type of data, such as text, number, or toggle. It turns out that Visio supports only a very few specific data types. Specifically, there are three:

Number When a cell contains a number, it is always a double-precision real number. Formatting makes the number appear to be an integer, such as 100, or a number with units, such as 12.3 ft.

Text A cell can contain up to 252 characters. (It would be 254 but two characters are always the surrounding " marks).

Data1...3 These fields can contain up to 64KB of each. There are three Datan fields per shape. In Visio, you can only access the Datan fields via the Format | Special command or through VBA programming; this data is not accessible via the ShapeSheet.

Normally, the database file expects to receive its information with data formatted into the correct data types. Most database programs require you to specify whether a field consists of an integer number, a real number, a single character, a text sting, a Boolean (true or false value), a date, etc. Fortunately for us, however, Visio attempts to interpret information and makes a best guess at its data type. For example, Visio is able to interpret the string "12.1 in." as the number 12.1 with the units of inches. Still, this might lead to the occasional unexplained error when a data type is converted incorrectly.

To define the units of a number, Visio uses the following convention. The units are stored in the ODBCLinkn cells of the User-defined Cells section, using a format that looks like this:

="Prop.Cost==Cost==111"

This means that the Custom Property row called Prop.Cost stores the Cost data of the shape. The cost data is formatted in currency units (unit code 111). The complete list of unit codes is as follows: