• Choose a category
• All
• Classic-Fiction

As the code runs, you'll see two alerts that look like Figure 13-19 (one for each DAP in the database). There's no apparent way to get around these alerts; even the SetWarnings method has no effect on them. Close each dialog to move on.

Figure 13-19. Alerts like this will appear as the code runs

Return to Windows Explorer and double-click Customers.htm to open it in the browser. The page will be displayed with no error messages, as shown in Figure 13-20. The code "fixed" the connection string so that it points to the database in which the data access page object is located.

Figure 13-20. The browser window after running ChangeConnectString

Discussion

The complete ChangeConnectString function looks like this:

Public Function ChangeConnectString( ) As Boolean

' Code sets the connection string for all pages so that the data source

' is the database in which the data access page object is stored.

' Run this function whenever there is a chance that the database name

' has changed.

On Error GoTo HandleErr

Dim objDAP As AccessObject

Dim dapPage As DataAccessPage

Dim strConnectionDB As String

' This code assumes that the connection string should point to the

' current database. You could make the solution more generic by

' making strConnectionDB an input parameter, perhaps set with a

' custom form that includes a Browse button.

' It would be great if you could simply supply the

' relative path to the database, but that doesn't work.

' You must supply the full name, including the path.

strConnectionDB = CurrentProject.FullName

' Turn off warnings and screen painting.

DoCmd.Hourglass True

Application.Echo False, "Updating pages"

DoCmd.SetWarnings False

' AllDataAccessPages contains AccessObjects, not DataAccessPage objects.

' You must open the data access page in design view to change the

' connection string. Note that you will get a message notifying you

' that the connection is broken. SetWarnings False should probably

' suppress this, but it doesn't.

For Each objDAP In CurrentProject.AllDataAccessPages

DoCmd.OpenDataAccessPage objDAP.Name, acDataAccessPageDesign

Set dapPage = DataAccessPages(objDAP.Name)

dapPage.MSODSC.ConnectionString = _

"Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=" & strConnectionDB

DoCmd.Close acDataAccessPage, dapPage.Name, acSaveYes

Next objDAP

ChangeConnectString = True

ExitHere:

' Turn on warnings and screen painting.

DoCmd.Hourglass False

DoCmd.SetWarnings True

Application.Echo True

Exit Function

HandleErr:

MsgBox Err.Number & ": " & Err.Description, "ChangeConnectString"

Resume ExitHere

End Function

The code begins by setting up three variables:

Dim objDAP As AccessObject

Dim dapPage As DataAccessPage

Dim strConnectionDB As String

We need both objDAP and dapPage because the collection of all pages in a project returns a collection of AccessObject objects, but only DataAccessPage objects support a property to get at the DataSource control object, which in turn supports the ConnectionString property.

The code sets the value of the string variable to the name of the current project:

strConnectionDB = CurrentProject.FullName

It then turns on the hourglass and turns off warnings and screen updates:

DoCmd.Hourglass True

Application.Echo False, "Updating pages"

DoCmd.SetWarnings False

If you ran the test we described, you have seen that SetWarnings has no effect on the message box that notifies you that the data link is broken.

The code uses the AllDataAccessPages collection of the CurrentProject object to iterate through the pages:

For Each objDAP In CurrentProject.AllDataAccessPages

.

.

.

Next objDAP

The ConnectionString property can't be changed unless the page is in design view, so the code opens each page in turn and sets a DataAccessPage object variable to the open page:

DoCmd.OpenDataAccessPage objDAP.Name, acDataAccessPageDesign

Set dapPage = DataAccessPages(objDAP.Name)

It's the OpenDataAccessPage method that triggers the message box regarding