Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

Recipe 13.6 Programmatically Change the Connection String for All Pages in a Database

13.6.1 Problem

You
don't want to rely on yet another extra file, such
as the data connection file, to determine how your application is
supposed to work. But you also don't want to
manually change the ConnectionString property of each page every time
you need to point to a different data source.

13.6.2 Solution

As long as you can count on having
Access available every time you need to point to a different data
source, it's easy to change the ConnectionString
property of every page programmatically. You'll need
to iterate through the collection of pages and change the
ConnectionString property. Note that ConnectionString is a property
of the DataSource control for the page; you can refer to the
DataSource object as MSOSDC.

We've supplied sample code in the database for this
item. Take a look at the

ChangeConnectString
function in basResetConnectionString.

To see how the code works, follow these steps:

1. Close the database and open Windows Explorer. Change the name of the
   sample database,

   13-06.MDB , to
   

   13-06-test.MDB .

2. Open

   Customers in Internet Explorer.
   You'll receive two messages: one informs you that
   the data provider could not be initialized, and the other tells you
   that the database could not be found. After you close the message
   boxes, the browser window will look like Figure 13-17. The #Name? syntax will be familiar to most
   Access developers; it means the data source couldn't
   be found.

Figure 13-17. The browser window after renaming the sample database

3. Close the browser window.

4. Open

   13-06-test.MDB . Open the
   basResetConnectionString module.

5. If the Immediate window is not displayed, press Ctrl-G to open it.
   Type ?ChangeConnectString( ), as shown in Figure 13-18. Press Enter.

Figure 13-18. Running the ChangeConnectString function from the Immediate window

6. 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

7. Return to Windows Explorer and double-click
   

   Customers 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

13.6.3 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 the
broken link.

The next line of code does the work:

dapPage.MSODSC.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strConnectionDB

ConnectionString is
a property of the DataSource control that is automatically included
on every bound DAP. In code, the DataSource control is called MSODSC.

Once the string has been changed, the code saves and closes the DAP
and moves on to the next page object:

DoCmd.Close acDataAccessPage, dapPage.Name, acSaveYes

Finally, after the code has iterated through all the pages, the
cleanup work is done. The code turns the hourglass off, sets warnings
on, and turns screen painting on:

' Turn on warnings and screen painting.
DoCmd.Hourglass False
DoCmd.SetWarnings True
Application.Echo True

If any part of the code fails, the function returns a
False value.

It would seem that you could simply use the name of the database (without its path) if it was in the same folder as the page file (the file). Unfortunately, this doesn't work. Therefore, you'll need to update the connection string information any time you move your database and page files to a new location.