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.
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:
Close the database and open Windows Explorer. Change the name of the
sample database, 13-06.MDB
, to
13-06-test.MDB
.
Open Customers.htm
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.
Close the browser window.
Open 13-06-test.MDB
. Open the
basResetConnectionString module.
If the Immediate window is not displayed, press Ctrl-G to open it.
Type ?ChangeConnectString( )
, as shown in Figure 13-18. Press Enter.
As the code runs, you’ll see two dialogs that look like Figure 13-19 (one for each DAP in the database). There’s no apparent way to get around these dialogs; even the SetWarnings method has no effect on them. Close each dialog to move on.
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.
The complete ChangeConnectionString 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. ' The Name property of the CurrentProject returns only the MDB name, ' not the path. This way, the data path doesn't need to be changed ' if the folder name or structure changes. strConnectionDB = CurrentProject.Name ' 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.Name
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.
Our code is written to iterate through all the DAPs in the database
and change the ConnectionString property of each to the name of the
current database. Note that we’re using the filename only, not
the path; this technique makes it easier to distribute our databases
and .htm
files to you, because you might use a
different folder structure than we do. If you want to use our sample
code, consider whether it makes sense for you to store the full path
to the data with each page. If so, you can edit the code to use the
FullName property of the CurrentProject object, or provide an input
parameter that names the source database.