10.3 Create a Transaction Log

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

In 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:

1. Open Windows Explorer and browse to the folder where you'd like to create the file.

2. Right-click in the file list and select New from the context menu.

3. Select Microsoft Data Link, if that option exists. You'll see the dialog shown in Figure 13-10. Move on to Step 6. If you don't see a Microsoft Data Link choice, select Text Document.

4. Give the text document whatever name you'd like, but be sure to use the .udl extension instead of .txt.

5. Right-click the .udl file and choose Open. You'll see the dialog shown in Figure 13-10.

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

   

6. 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.

7. Select the Provider page and choose Microsoft Jet 4.0 OLE DB Provider.

8. Select the Connection page and enter the database name in the first text box. Click the Test Connection button to verify your settings.

9. Click OK to save the .udl file.

    

   

   If you need to change a .udl file to point to a different location, just right-click on it in Windows Explorer and choose Open. You'll see the same dialog, and you can use it to change any aspect of the connection.

    

The .odc format is 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:

1. Open any DAP in Access.

2. Display the properties sheet for the page and find the ConnectionFile property (on the Data page).

3. 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

   

4. 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

   

5. 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

   

6. Select Microsoft Jet 4.0 OLE DB Provider and click the Next button.

7. 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

   

8. 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

   

9. 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.

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:

1. 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

   

2. Select Edit Find from the Notepad menu and type:

   source=

   into the text box. Click the Find Next button.

3. 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.

4. 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.