Microsoft KB Archive/295313

= How to programmatically update the ConnectionString property of all data access pages in Access 2002 =

Article ID: 295313

Article Last Modified on 10/11/2006

-

APPLIES TO


 * Microsoft Access 2002 Standard Edition

-



This article was previously published under Q295313



Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).



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 update the ConnectionString property of a data access page. This procedure is commonly required after you deploy a Microsoft Access database that contains data access pages to another computer.

This article shows you how you can programmatically update the ConnectionString property of all data access pages in a database.



MORE INFORMATION
 Create a new folder named Test on the root directory of drive C. Copy the files in the C:\Program Files\Microsoft Office\Office10\Samples folder to the C:\Test folder. In Access, open C:\Test\Northwind.mdb. 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. Close the data access page, and then click Module on the Insert menu.  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 = &quot;Provider=Microsoft.Jet.OLEDB.4.0;&quot; & _ &quot;Data Source=&quot; & 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 &quot;Connection Strings Updated.&quot; End Sub </li> On the Debug menu, click Compile Northwind.</li> On the View menu, click Immediate Window.</li> Type subUpdateConnStr in the Immediate window, and then press ENTER. When the code is finished, click OK in the message box.</li> On the File menu, click Close and Return to Microsoft Access.</li>  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 & _ &quot;;User ID= &quot; where is a valid SQL Server account name, such as the built-in account name of sa. </li></ol>

<div class="references_section">