Connecting to an ODBC Data Source

When you use the Export To Database or Link To Database command for the first time, you must set up and select an ODBC data source. Although you can use the Control Panel to set up an ODBC data source before exporting or linking, you don’t have to—Visio provides all the options you need. A data source is simply the particular database file you want to connect to. You need to tell Visio where to find the database program, which ODBC drivers can communicate with it, and which database file (or object) to use. Through this process, you create a file called a data source name (DSN), which saves the settings used to create the connection.

You can create different types of DSNs depending on whether you want to share the connection with other users. The following procedure creates a file-based DSN, which means the DSN is portable—you can copy it to any other computer, such as a laptop or another user’s computer. For added security, you can also specify a user or system DSN, both of which limit access to the DSN file to the user of your computer.

Note

The following procedure assumes that a data source already exists. If you are creating a new DSN and a new database or spreadsheet, you must first create a new database or spreadsheet file and save it before proceeding.

Follow these steps to create a new ODBC data source:

1. Choose Tools, Export To Database or Tools, Add-Ons, Visio Extras, Link To Database.

2. Click Create.

   

3. Select the File Data Source (Machine Independent) option, and then click Next.

   

4. In the list of ODBC drivers under Name, select the driver for the database you want to connect to, and then click Next.

   Tip

   If you know structured query language (SQL), you can click Advanced, and then type the DSN parameters you want to use.

5. Type a name for or browse to the DSN file. It’s a good idea to specify the program and database name. For example, if your data source is an Excel workbook with employee information, type a name such as Excel-Employee. DSN files are saved to a default system location, so you don’t have to click Browse to specify a folder location unless you want to store the DSN file in another place.

6. Click Next to display a summary of your choices. Click Finish. Based on the ODBC driver selection you made in step 4, Visio displays a dialog box that contains driver- specific options for setting up the database program.

   

7. If you’re connecting to Excel, specify the version, and then click Select Workbook to locate and select the particular file (.xls) you want to connect to. Click Options to expand the dialog box, clear the Read Only check box, and then click OK to return to the dialog box from step 1.

   

8. If you’re connecting to Access, click Select to locate the specific database file (.mdb) that you want to use, and then click OK to return to the dialog box from step 1. The options for other databases are similar.

   Tip

   In the Export To Database or Link To Database dialog box, the DSN you created is listed in the Name box, and the tables contained in your data source are listed in the Table list box.