Access Cookbook - Ken Getz [284]
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 you're working.
TIP
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:
source=
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 menu.
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.
13.6. 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:
Close the database and open Windows Explorer. Change the name of the sample database, 13-06.MDB, to 13-06-test.MDB.
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 after renaming the sample database
Close the browser window.
Open 13-06-test.MDB. Open the basResetConnectionString module.
If the Immediate window is not displayed, press Ctrl-G to open it. Type ?ChangeConnectString( ), as shown in Figure 13-18. Press Enter.
Figure 13-18. Running the ChangeConnectString