How to programmatically update the ConnectionFile property of all data access pages in Access 2002 (295282)

SUMMARY

When you want to bind a data access page to another data source or to the same data source that has been moved to a new location, one way to do this is to assign an Office Database Connection file (.odc) to the ConnectionFile property of a data access page.

This article shows you how you can programmatically update the ConnectionFile property of all data access pages within a database, which in turn, rebinds each data access page to a data source.

This article has two parts. The first part shows you how to create an Office Database Connection file. The second part shows you how to create the code that will use the .odc file.

back to the top

Creating the ODC file

1. Copy all files in the C:\Program Files\Microsoft Office\Office10\Samples folder to a new folder, C:\Test.
2. In Access, open C:\Test\Northwind.mdb.
3. Click Pages in the Database window, and then open any one of the existing pages in Design view.
4. On the Edit menu, click Select Page. If this does not open the property sheet for the page, you can open it by clicking Properties on the View menu.
5. In the property sheet, click the Data tab. The top-most property is the ConnectionFile property.
6. Click the Build button to the right of the ConnectionFile property.
7. In the Select Data Source dialog box, click +Connect to New Data Source.odc, and then click OK. This starts the Data Connection Wizard.
8. On the first page of the wizard, which is titled Welcome to the Data Connection Wizard, click Other/Advanced, and then click Next.
9. In the Data Link Properties dialog box, click Microsoft Jet 4.0 OLE DB Provider, and then click Next.
10. On the Connection tab, enter C:\Test\Northwind.mdb, and then click OK.
11. On the Select Database and Table page of the wizard, click Next.
12. On the Save Data Connection File and Finish page of the wizard, type C:\Test\Northwind.odc in the File Name box, and then click Finish.
    
    NOTE: You must select the ODC file that you just created.
13. If the field list is not displayed, click Field List on the View menu. Note that although you are currently working in the C:\Test\Northwind.mdb file, the path in the field list still points to the Samples folder.
14. Close the data access page without saving changes.

back to the top

Creating the VBA code

1. In the C:\Test\Northwind.mdb database, click Module on the Insert menu.
2. Type or paste the following VBA code in the module:

   Option Compare Database
   Option Explicit
   
   Sub subSetConnFileProp()
       '
       'Update all data access page connections.
       '
       Dim aoDAP As AccessObject
       Dim dapObject As DataAccessPage
       
       'Loop through the collection of data access pages in the database.
       For Each aoDAP In Application.CurrentProject.AllDataAccessPages
       
           'Open each data access page in Design view.
           DoCmd.OpenDataAccessPage aoDAP.Name, acDataAccessPageDesign
           
           'Assign the current page to a data access page object.
           Set dapObject = DataAccessPages(aoDAP.Name)
           
           'Update the ConnectionFile property of the current page.
           dapObject.MSODSC.ConnectionFile = "C:\Test\Northwind.odc"
           
           'Close the page, saving the changes.
           DoCmd.Close acDataAccessPage, dapObject.Name, acSaveYes
       Next aoDAP
   End Sub
   					

3. On the Debug menu, click Compile Northwind.
4. On the View menu, click Immediate Window.
5. Type subSetConnFileProp in the Immediate window, and then press ENTER. Note that each data access page is opened programmatically. When the code is finished, focus is returned to the Immediate window.
6. On the File menu, click Close and Return to Microsoft Access.
7. Click Pages in the Database window, and then open any one of the existing pages in Design view. Note that the field list now lists a new connection path for C:\Test\Northwind.mdb.

back to the top