Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

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:

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

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

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

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

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.

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:

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.