Linking Visio drawings with an external database is useful for automatically generating Visio drawings or to share data stored in a Visio drawing.
What is a Database?
A database stores information and lets you retrieve the information. As you read that sentence, you might be thinking to yourself, "My word processor does that; my spreadsheet does that." And you would be right. Word processors and spreadsheets have simple database features, such as the ability to sort a table by its columns.
Database software, however, is capable of advanced sorting and data output. For example, you could ask the database software to produce a list of all employees with a birthday in August, sort by the list by dates, then print the list of names by date. The data can be output to the screen, the printer, to a file on disk, or to another program -- such as Visio.
When it comes right down to it, a database simply consists of rows of data (called records). Every record contains exactly the same types of data (called fields) in precisely the same order. For example, here are three records, each containing four fields:
Stefan Vancouver $1,234.45 No
Heidi Vancouver $2,345.67 No
Katrina Vancouver $3,456.78 Yes
By looking at these records, you can guess that field #1 contains a first name. Field #2 contains a location, such as a city name. Field #3 contains an amount, perhaps the monthly salary. Field #4 contains a toggle -- yes or no -- perhaps indicating whether the person has access to the executive bathroom.
I present to you the table again, this time adding in the database structure:
Field #1 Field #2 Field #3 Field #4
Name Location Amount Executive Bathroom Privileges
Record #1 Stefan Vancouver $1,234.45 No
Record #2 Heidi Vancouver $2,345.67 No
Record #3 Katrina Vancouver $3,456.78 Yes
If you are familiar with spreadsheets, then you may notice that this database table looks very much like a spreadsheet, with rows and columns.
Spreadsheet representation of data arranging into rows and columns
Indeed, recall that Visio uses a spreadsheet-like interface to display its shape data in ShapeSheets. Keep the similarity in mind: only the terminology is different. The ShapeSheet rows are called records in the database; the ShapeSheet cells are called fields in the database; the ShapeSheet itself is called the table in the database.
One other important term you need to know is key. The primary key is a field that uniquely identifies each record.
If you are familiar with database software, then you should know about Visio's limitations. Visio only sees a flat table of rows and columns of data; it cannot make relational database queries. Visio has only three data types, as discussed later. Visio cannot execute SQL commands directly; it is possible to do this indirectly. Visio is limited to connecting one record to one shape; you cannot connect two or more records to a shape. A future version of Visio may eliminate these restrictions.
Reasons for Making the Connection
Why would you want to connect Visio with a database? Readers of my other Visio books have emailed me their needs. Here are some of them:
* "I am documenting an old mainframe system, so I load information into a database and show the flowchart of jobs/programs/files in Visio Standard v5."
* "I am using a database to create a network diagram in Visio."
* "I connected Visio to my database because I needed a more efficient way to document database development projects."
Other examples include facilities management (keep track of furniture and other corporate assets) and centralized master generation. You can probably think of your own examples. Perhaps the most important thinking you need to do is whether to store the data in the Visio drawing or in the external database. Here is the difference: you might want to store the data in the external database when the data needs to be shared among other users and other software applications.
The Visio-database connection is more powerful than simply showing data graphically. You can control the database from Visio, such as adding and deleting records. You can even create new database files from Visio. This is pretty exciting stuff!
Visio and ODBC
With Visio shape connected to database records, you can pass information back and forth between Visio and the database and keep the two versions of the data synchronized. The mechanism for doing this is called ODBC, short for open data base connectivity. This is a standard, written by Microsoft (who else?), for sharing database information between applications under Windows. It lets a software program access, view, and modify data from a databases. Get used to the abbreviation "ODBC," because you'll be seeing it a lot.
Before the advent of ODBC, a software program, such as Visio, needed an interface driver for every database program. A driver is a piece of software that lets a program communicate with another program or hardware. (You are probably familiar with hardware drivers that let Windows communicate with your computer's graphics board, CD-ROM, or network card.) Pre-ODBC you would have one database driver for dBase, another database driver for Sybase, another database driver for Paradox, and on and on. It was a pain for software developers to write a driver for every database program, then have to update every driver each time the software was upgraded.
For this reason, Microsoft invented ODBC. Pretty much all Windows-based database programs convert their proprietary data format to ODBC format. Examples include Paradox, Oracle, Access, dBase, Alpha Four, SQL Server, and Sybase. Excel contains a limited form of ODBC; for example, Excel cannot be made to delete a record via ODBC. Visio says they heard of one customer who managed to get ODBC working between Visio running under Windows and dBase II running on a mainframe computer!
Similarly, a number of non-database Windows applications have ODBC, such as AutoCAD, MicroStation, Lotus Notes, and Visio. These programs can read ODBC data and can control ODBC-compatible databases.
Caution: ODBC might not be installed on your computer. The appropriate ODBC components and database drivers are only installed if you choose the Complete option when installing Visio. All versions of Visio, including Visio Standard, support ODBC.
Which Database Program?
With all this talk about database connectivity, you may be wondering, "Which database program is best for me?" The list of database programs include Paradox, Fox Pro, Oracle, Access, dBase, Alpha Four, SQL Server, Sybase, Excel, etc, etc. The short answer is, "None!"
You heard right: to connect a Visio drawing with a database file, you don't need a database program. That's because Visio includes everything you need to create the database file from within Visio. Visio has an option to create an Access-compatible database file. It is only when you want to access (pardon the pun) the database file from outside of Visio that you might need the database program.
However, if you want to dabble with an actual database program, you have several low-cost choices. If you have Microsoft Office, you can begin with the Excel spreadsheet program. Microsoft Office Professional includes the Access database program. Otherwise, there are a number of low-cost database programs that are dBase II-compatible, such as Alpha Four. Some of these are available in free demo version, which are only limited by the size of database you are allowed to create. 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:
ShapeSheet view of User-defined cells
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:
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:
0 String value
1 String formula
32 Non-dimensional number
48 Number with no explicit units
50 Points (72 points per inch).
51 Picas (6 picas per inch).
53 Didots (67 didots per inch).
54 Ciceros (12 ciceros per didot).
63 Use default units, as specified by the page
64 Use drawing's default units
65 Decimal inches
67 Feet and inches
68 Decimal miles
73 Fractional inches
74 Fractional miles
80 Angle with no explicit units
81 Angle in decimal degrees
82 Angle in degrees, minutes, and seconds
83 Angle in radians
84 Angle in minutes and seconds
85 Angle in seconds
252 Leave number in current units
This article provided you with an introduction to linking Visio with an external database file. You learned some database terminology, the importance of ODBC, and how cells are linked to fields. You saw the importance of understanding the ShapeSheet before tackling advanced Visio topics.