Access Cookbook - Ken Getz [283]
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.
TIP
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