Access Cookbook

Programmatically Change the Connection String for All Pages in a Database

Problem

You don’t want to rely on yet another extra file, such as the data connection file, to determine how your application is supposed to work. But you also don’t want to manually change the ConnectionString property of each page every time you need to point to a different data source.

Solution

As long as you can count on having Access available every time you need to point to a different data source, it’s easy to change the ConnectionString property of every page programmatically. You’ll need to iterate through the collection of pages and change the ConnectionString property. Note that ConnectionString is a property of the DataSource control for the page; you can refer to the DataSource object as MSOSDC.

We’ve supplied sample code in the database for this item. Take a look at the ChangeConnectString function in basResetConnectionString.

To see how the code works, follow these steps:

1. Close the database and open Windows Explorer. Change the name of the sample database, 13-06.MDB, to 13-06-test.MDB.

2. Open Customers.htm in Internet Explorer. You’ll receive two messages: one informs you that the data provider could not be initialized, and the other tells you that the database could not be found. After you close the message boxes, the browser window will look like Figure 13-17. The #Name? syntax will be familiar to most Access developers; it means the data source couldn’t be found.

   Figure 13-17. The browser window ...