Access Cookbook

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

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.

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