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

MORE INFORMATION

1. Create a new folder named Test on the root directory of drive C.
2. Copy the files in the C:\Program Files\Microsoft Office\Office10\Samples folder to the C:\Test folder.
3. In Access, open C:\Test\Northwind.mdb.
4. In the Northwind.mdb database, open any one of the existing data access pages. Note that the path in the field list still points to the Samples folder.
5. Close the data access page, and then click Module on the Insert menu.
6. Type or paste the following VBA code into the module:

   Option Compare Database
   Option Explicit
   
   Sub subUpdateConnStr()
       '
       'This subroutine will update the ConnectionString property
       'in each data access page within the current database.
       '
       Dim aoDAP As AccessObject
       Dim dapObject As DataAccessPage
       
       'Go through each data access page.
       For Each aoDAP In Application.CurrentProject.AllDataAccessPages
       
           'Open the current page.
           DoCmd.OpenDataAccessPage aoDAP.Name, acDataAccessPageDesign
           
           'Assign the current page to the data access page object.
           Set dapObject = DataAccessPages(aoDAP.Name)
           
           'Update the ConnectionString property of the current Page.
           'CurrentDB.Name returns the path and file name to the database,
           'that is, C:\Test\Northwind.mdb
           dapObject.MSODSC.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                               "Data Source=" & CurrentDb.Name
   
           'Close the current page, saving the changes.
           DoCmd.Close acDataAccessPage, dapObject.Name, acSaveYes
       Next aoDAP
       
       'Inform the user when the routine is complete.
       MsgBox "Connection Strings Updated."
   End Sub
   					

7. On the Debug menu, click Compile Northwind.
8. On the View menu, click Immediate Window.
9. Type subUpdateConnStr in the Immediate window, and then press ENTER. When the code is finished, click OK in the message box.
10. On the File menu, click Close and Return to Microsoft Access.
11. Click Pages in the Database window, and then open any one of the existing pages in Design view. Note that the field list displays the new connection path of C:\Test\Northwind.mdb.NOTE: If you use a .adp file instead of a .mdb, you must change the command line:

    dapObject.MSODSC.ConnectionString =
    					

    to one of the following.
    
    If you use a .adp based on a SQL Server database, and database security is maintained by Windows Integrated Security, use:

    dapObject.MSODSC.ConnectionString = CurrentProject.BaseConnectionString
    					

    If you use a .adp based on a SQL Server database, and database security is maintained by SQL Server Security, use:

    dapObject.MSODSC.ConnectionString = CurrentProject.BaseConnectionString & _
                                        ";User ID=<name>"
    					

    where <name> is a valid SQL Server account name, such as the built-in account name of sa.