How to Programmatically Update Data Access Pages Links (295315)

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: <Office Path>\Office10\
For Access 2003: <Office Path>\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, "\")
    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 & ".htm"
    Next aoDAP
    
    MsgBox "Links Updated."
End Sub

On the Debug menu, click Compile Northwind.
On the View menu, click Immediate Window.
Type subUpdatePageLinks in the Immediate window, and then press ENTER. When the code is finished, click OK in the message box.
On the File menu, click Close and Return to Microsoft Access.
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, "\")
strLocation = Left$(CurrentProject.FullName, intPosition)
					
```

REFERENCES

For more information about resolving data access page issues, click Microsoft Access Help on the Help menu, type troubleshoot data access pages in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

For additional information about programmatically setting properties in a data access page, click the article numbers below to view the articles in the Microsoft Knowledge Base:

295282 ACC2002: How to Programmatically Update the ConnectionFile Property Of All Data Access Pages

295313 ACC2002: How to Programmatically Update the ConnectionString Property of All Data Access Pages