Recipe 13.5 Create One File to Store Connection Information for All DAPs in an Application
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.
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
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
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
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
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:
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
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.