Microsoft KB Archive/295315

= How to Programmatically Update Data Access Pages Links =

Article ID: 295315

Article Last Modified on 10/11/2006

-

APPLIES TO


 * Microsoft Office Access 2003
 * Microsoft Access 2002 Standard Edition

-



This article was previously published under Q295315



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
If you are familiar with ODBC tables and Microsoft Access databases, you know that an ODBC table is not literally stored in an Access database. Instead, the Access database contains a link that points to the actual location of the back-end ODBC table.

Data access pages in an Access database are also links. They are not objects, such as forms, reports, queries, and so on, that reside in the database. Data access pages are HTML files and they are stored in external files outside of the database. The data access page object that you see in an Access database is simply a link to the external HTML file.

If either the database or the HTML files are moved to a new location, the data access page links in the Database window will no longer be valid. A developer may encounter this situation when deploying a database to another computer. The developer or end-user would then have to relink the data access pages to the appropriate HTML file after the database has been deployed in order for the data access pages to be functional.

This article provides sample Visual Basic for Applications (VBA) code that illustrates how to programmatically update the data access pages links in a Microsoft Access database.



MORE INFORMATION
 Create a new folder named Test in the root directory of drive C. Copy all the files in the Samples folder to the C:\Test folder.

NOTE: The Samples folder can be located in the following location:

For Access 2002: \Office10\

For Access 2003: \Office11\ Use Access to open C:\Test\Northwind.mdb. On the Insert menu, click Module.  Type or paste the following VBA code into the module: Option Compare Database Option Explicit

Sub subUpdatePageLinks '   'Update the links for each data access page. '   Dim aoDAP As AccessObject Dim strLocation As String Dim intPosition As Integer 'Retrieve the new path/location of the database. intPosition = InStrRev(CurrentDb.Name, &quot;\&quot;) strLocation = Left$(CurrentDb.Name, intPosition) 'For each page... For Each aoDAP In Application.CurrentProject.AllDataAccessPages 'Select the page. DoCmd.SelectObject acDataAccessPage, aoDAP.Name, True 'Assign the new path/location to the page. aoDAP.FullName = strLocation & aoDAP.Name & &quot;.htm&quot; Next aoDAP MsgBox &quot;Links Updated.&quot; End Sub </li> On the Debug menu, click Compile Northwind.</li> On the View menu, click Immediate Window.</li> Type subUpdatePageLinks 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, right-click any existing page, and on the shortcut menu, click Properties. Note that the Path box displays the new location to the HTML file.

This example is for illustration purposes only. If your database and HTML files do not reside in the same folder, you must use a different approach for providing the path in the VBA code.NOTE: To use this code within a .adp file, you must change the following two command lines: intPosition = ... strLocation = ... These commands lines should reference the CurrentProject object instead of the CurrentDB object. For example: intPosition = InStrRev(CurrentProject.FullName, &quot;\&quot;) strLocation = Left$(CurrentProject.FullName, intPosition) </li></ol>

<div class="references_section">