Recipe 13.5 Create One File to Store Connection Information for All DAPs in an Application

13.5.1 Problem

You have many DAPs that all use the same data source. The data source's name and location are subject to change; for example, the pages point to a sample data source when you're working on their design and to a production data source when you deploy them. But if you move the data source to a different folder or change the name of the data source, the links your DAPs use will probably break. You'd like to be able to change the data source in one place, rather than making the change on every page.

13.5.2 Solution

Starting with Access 2002, you can use a connection file, rather than a hardcoded string, to define the source of the data for each page. Microsoft provides two types of files for storing data connection information: Microsoft Data Link (also called Universal Data Link, or .udl) files, and Office Data Connection (.odc) files. You can create a data connection file that points to the test data, create and test your pages, and then switch to live data by editing the data connection file.

Essentially, both .udl and .odc files store an ADO connection string. We'll walk you through the steps to create and edit each type of file, and then talk about how to reference a connection file in your DAPs.

The .udl format has been around longer, so we'll discuss it first. A .udl file is a text file that stores the same provider and data source information you would use to set up an ADO connection. To create a .udl file, follow these steps:

Open Windows Explorer and browse to the folder where you'd like to create the file.
Right-click in the file list and select New from the context menu.
If you don't see the Microsoft Data Link option, skip to Step 4. You'll see the dialog shown in Figure 13-10. Move on to Step 6.
Select New Text Document. Give the text document any name you like, but be sure to use the .udl extension instead of .txt. (Disregard the warning from Windows Explorer about changing the file extension.)
Double-click the .udl file. You'll see the dialog box shown in Figure 13-10.

Figure 13-10. The .udl file format uses a simple wizard to create a connection string

Use the dialog to set up the connection you want. The instructions given here describe how to set up a connection to an Access database.
Select the Provider page and choose Microsoft Jet 4.0 OLE DB Provider.
Select the Connection page and enter the database name in the first text box. Click the Test Connection button to verify your settings.
Click OK to save the .udl file.

If you need to change a .udl file and modify its connection settings, just right-click on it in Windows Explorer and choose Open from the context menu. You'll see the same dialog as before, and you can use it to change any aspect of the connection.

The .odc format was new with Office XP. It stores information about the connection in an HTML format and uses the Office Web Components to display information about the database when you view the .odc file in Internet Explorer. To create an .odc file, follow these steps:

Open any DAP in Access.
Display the properties sheet for the page and find the ConnectionFile property (on the Data page).
Click the Browse button next to the property. You'll see the dialog shown in Figure 13-11.

Figure 13-11. By default, .odc files are stored in My Data Sources, a subfolder of My Documents

Click the New Source button next to the "File name" drop-down list. You'll see the dialog shown in Figure 13-12.

Figure 13-12. The Jet provider falls into the Other/Advanced category

To create an .odc file that points to an Access database, select Other/Advanced from the list box and click the Next button. You'll see a dialog like the one used for Microsoft Data Links, as shown in Figure 13-13.

Figure 13-13. The .odc wizard looks just like the .udl wizard at this point

Select Microsoft Jet 4.0 OLE DB Provider and click the Next button.
Type in the name of the database you want to point to and click the OK button. You'll see the dialog shown in Figure 13-14. The dialog shows you the tables in the database, but you can't actually make a selection here. You're defining a file to point to the database, not a particular table or query.

Figure 13-14. You can't select a table at this point

Click the Next button. You'll see the final wizard page shown in Figure 13-15.

Figure 13-15. Provide a meaningful name and description

Type a name and description for the .odc file and click the Finish button. At this point, you can choose your new connection file as the connection file for the DAP with which you're working.

Although Access' user interface is extremely confusing on this point, if you modify the ConnectionFile property and select a connection file that isn't in the My Data Connections folder, you must supply a full path. The dialog box you'll use in order to select the file doesn't allow you to supply the full path, however. Therefore, if you want to specify a .udl or .odc file that isn't in the My Data Connections folder, you must type the full path and file name by hand, in the ConnectionFile property.

If you want to change an .odc file to point to a different location, you'll need to open it in a text editor. Follow these steps to make the change quickly:

The easiest way to open the file in Notepad is to right-click on the .odc file in Windows Explorer and choose Edit in Notepad from the context menu. The text file will look something like Figure 13-16.

Figure 13-16. A portion of the text file in Notepad

Select Edit Find from the Notepad menu and type:
```
source=
```
into the text box. Click the Find Next button.
The cursor should be on the "Source=" string that precedes the path and filename of the .mdb file. You can modify the path directly, then select File Save from the menu.
Close Notepad.

To use either type of data connection file to supply the data source for your page, simply set the ConnectionFile property of each page to the name of the connection file. When you set the ConnectionFile property, Access automatically adds the ConnectionString property as well, but the ConnectionFile takes precedence. That is, if the ConnectionFile's information changes, the ConnectionString property will be updated automatically the next time the page is opened.

You can use the Pages page of the Tools Options dialog to set a default ConnectionFile property. Then, all new pages will use your connection file as the ConnectionFile property value.