Data Management

Data access pages connect Web pages to Data Source control

This Daily Drill Down concludes Rick Dobson's five-part series on Office XP Web Components. Rick drills down on data access pages, which are the user interface for the Data Source control.

Microsoft offers excellent Help materials for data access pages and the Data Source control, but there’s considerably less information on administering data access pages, despite the fact that their administration can be tricky. This concluding article in my series on Office XP Web Components focuses on data access pages and the Data Source control. Data access pages are stand-alone files with a database connection that you can view from any IE 5+ browser. You can think of the Data Source control as a connector and local data store for data access pages. A data access page offers a manual interface to the Data Source control associated with it, or you can programmatically access the Data Source control with VBA or VBScript. Users can now open their IE 5+ browsers to interactive reports while you control what they access.

Check out the whole series
The first installment in this series offers an overview of topics relating to the authoring of Web pages with Office XP Web Components. This initial installment explains that Office XP Components obsolete the Office 2000 Web Components, but gives you techniques for using the two versions together. The second, third, and fourth installments focus on the Spreadsheet, Chartspace, and PivotTable List components, respectively. These intermediate installments introduce progressively sophisticated programming techniques, including the Microsoft Script Editor and VBScript programming techniques for Web pages with Office XP Web Components.

Data access pages and the Data Source control
Basically, a data access page is a stand-alone Web page. The file for the data access page has an .htm extension. Therefore, the Web file for the data access page is not a part of an Access database file (.mdb) for a Jet database or an Access project file (.adp) for a SQL Server database. Nevertheless, the only way to create or edit a data access page is through Access. The Database window has a collection of elements associated with the Pages item in its Objects bar. These elements are links that point to the stand-alone files for data access pages.

When you create a new data access page, Access automatically creates a link for the page that points to the file. You can also create a new link in the Database window for a Web page created by another file. The Web file and the link are separate entities. Thus, links in several different Access database or Access project files can point to the same Web file. You can remove the link from a Database window without affecting the file. While you can remove the Web file for a data access page through Windows Explorer, I do not recommended this approach since it can leave a link pointing at a nonexistent file.

Using built-in capabilities for a data access page, you specify its database connection to either an Access database file or a SQL Server database. In either of these situations, your data access page must have a LAN connection to the database. The Data Source control manages this connection for a Web page. Use the Design view for a data access page to define manually the connection string for a Data Source control. When your code manages the creation of a Web page, set the Connectionstring property of a Data Source control.

When you create a data access page manually from an Access database file, the user interface creates, by default, a connection string based on the Jet database for the Access database file. This connection string uses a local file designation, such as c:\mydatabasefile.mdb. When you attempt to open the file locally from the workstation that creates the Web page, your data access page will make a connection to mydatabasefile.mdb. However, if another workstation attempts to open the workstation, the attempt may fail because mydatabasefile.mdb doesn’t exist on the C: drive of the other workstation. The solution is to specify the link to the file with a UNC designation. I’ll elaborate on this issue later in the article.

The local drive issue is not a concern when you connect a data access page to a SQL Server database because you specify the connection to a SQL Server database by the server's name, the database name, and a user id and password. In any event, you must have a LAN connection to the database server.

Manually creating a data access page with a form
To create a data access page, select Pages in the Objects bar of a Database window and double-click Create Data Access Page in Design view. You may see a prompt that reminds you about the compatibility between versions of the Office XP Web Components before Access opens the blank layout for a data access page in Design view. Once in Design view, click the Field List control on the toolbar (if the Field List dialog box is not already open). In an Access database file, this will expose folders for the tables and queries in the database. Similar folders are available when creating a new data access page within an Access project file.

Figure A
The Field List dialog box for the creation of a page connected to the trOXPWC5.mdb file

Figure A  shows the Field List dialog box for the creation of a new data access page from the trOXPWC5.mdb file. This file has links to the tables in the Northwind.mdb database file in its default path, c:\Program Files\Microsoft Office\Office10\Samples. However, the default database connection setting is for trOXPWC5.mdb, not the database to which it links. Before showing you how to override this default setting, I will show you how to create a data access page with a form and save it.

Select a record source for your form by expanding its fields. You can do this by clicking the plus sign next to a table or query. Then, double-click each of the fields that you want to add to the form. In Figure A, I’ve already added the ShipperID and CompanyName fields. The figure shows the Field List dialog just before I double-clicked the Phone field.

Figure B
Three fields from the Shippers table added to the layout for a data access page

Figure B  displays the completed layout for the data access page example after I double-clicked the Phone field in the Field List dialog box. Notice that the layout includes a couple of sections for the form. The Header section contains the form fields. The Navigation section includes a navigation bar for the form. Access automatically adds this bar after you add the first field. You can optionally drag up the lower bound of the Header section to reduce the empty space between the Phone field and the navigation bar.

The layout in Figure B is not a data access page. However, you can create a data access page from it. Choose File | Save As to open the Save As dialog box, from which you can designate a name for the link in the Database window to the stand-alone data access page file. This example uses dap1 as the link's name. Click OK to open the Save As Data Access Page dialog box, which is a standard dialog box for saving a file. Designate a folder in which to save the stand-alone file for the data access page in the Save In drop-down control. By default, Access appends .htm to the link name to form a filename. You can override this choice in the default name for the stand-alone file by entering an alternative in the File Name box. Click Save to save the layout as a new data access page. Unless you change the default path for the database file (this example does not), a pop-up message will remind you that the page has an absolute instead of a UNC path for its database connection string. Click OK for now. At this point, you can open your data access page with an IE 5+ browser from the workstation that created it.

Figure C
A data access page in a browser based on the layout in Figure B

Figure C  shows the dap1.htm file opened from the workstation used to create the page. Notice that the page title reflects the name for the data access page. The form in the browser has the look and feel of an Access form. You can use the form to add new records, delete existing ones, or modify fields within records. In addition, the navigation bar offers users the ability to sort and filter the records that a Data Source control manages for a form. You can programmatically restrict access to any subset of the functionality provided by the navigation bar.

Figure D

Attempting to open the dap1.htm file from a browser on another workstation can result in a message like the one in Figure D. This graphic shows the outcome from trying to open a data access page with an absolute file specification for its database connection from a different workstation than the one that created the page file. One of the more robust solutions to this issue is to create a database file with just links to data access pages that connect to another database file with tables and queries. In the database file with the links, you must use UNC notation to specify the link to the file with the tables and queries. You can change the default connection specification for a data access page by clicking the Page Connections And Properties control near the top of the Field List dialog box. Next, select the All tab in the Data Link Properties dialog box and click Edit Value with the Data Source item selected. Enter a UNC path to the file with the tables or queries for the page. For example, if the path needs to point at the sample Northwind database in its default location on a workstation named cab2000 with c as the share name for the C: drive, the new path specification should be:
\\cab2000\c\Program Files\Microsoft Office\Office10\Samples\Northwind.mdb.

Programmatically creating a data access page with VBA
Manual techniques are useful because they can be intuitive and less error-prone than programmatic techniques for end users and IT professionals. However, sometimes a little programming can simplify a task that is awkward or inconvenient to resolve with manual techniques. For example, the manual method for creating a new data access page automatically specifies an absolute address for the page's database connection. As we have just seen, this is inappropriate for use of the page by another workstation besides the one creating the file.

Those of you that worked with the Office 2000 Web Components may remember the CreateDataAccessPage method. In the Office 2000 version of the Web components, this method can create a new blank data access page and link. Requesting Help for this method with Access 2002 opens a message saying the method is unavailable for programmatic access. This message is not strictly true. Actually, you can invoke the method to create a new page, but it creates an error before completing the task. However, you can trap the run-time error and complete the task yourself. There are two advantages to pursuing this approach. First, it can automatically save a data access page with a UNC path to a database. Second, it is relatively easy to do.

Listing A  includes a pair of VBA procedures that demonstrate the use of the CreateDataAccessPage method with the Office XP Web components. The listing also shows an explicit reference to the Data Source control (see the reference to the MSODSC object pointer). The first procedure assigns the name of the data access page file and link names as well as the path for saving the file. The page and link names are the same (dapName). Optionally, you can designate a file name for the Data Source control of the data access page (or you can accept a default designation of the Northwind sample database).

The second procedure invokes the CreateDataAccessPage method. This method opens the blank layout for a data access page. After trapping the error for the page (Err.Number = 13), the code extracts the current connection string for the Data Source control (MSODSC) behind the page. For simplicity, the code divides the connection string into three parts: the part before the data source specification, the data source specification, and the part after the data source specification. Next, the code reconstructs the connection string for the MSODSC pointer that represents the Data Source control. The reconstructed version swaps in a new data source specification that is either the default specification (see the argument list) or a passed parameter from the first procedure. By reconstructing the connection string, your code can replace the default absolute path to the data source with a UNC path. After writing the new Connectionstring property to the MSODSC object, the second procedure saves the blank data access page and closes it. You can optionally leave the page open by omitting the DoCmd.Close statement and adding a DoCmd.Restore statement. This Restore method is necessary since the CreateDataAccessPage method creates a blank layout in a minimized window.

Managing data access pages from an Access form
The Database window in an Access database or project file has a built-in graphical interface for working with data access pages. However, in some situations you may need to replace or supplement the built-in user interface for administering data access pages. For example, if you build an application with the Access run-time for the royalty-free deployment of a solution, your solution will not have the Database window available. In addition, you may want to enable users to create data access pages with a UNC specification without having to go back and manually revise the settings that the built-in user interface creates for you. Furthermore, you may want to abort some attempts to delete data access pages instead of letting users corrupt your solutions that depend on these pages.

Figure E
An Access form to customize the administration of data access pages

The Access form in Figure E enables five functions for data access pages. First, users can create a new page. The Text0 text box and a command button to its side enable this feature. Second, users can see the names of all links for data access pages in the current Access database or Access project file. Opening combo box Combo0 displays these names. By selecting the name for any link to a data access page in Combo0, users can perform any of the remaining three functions enabled by the form. Clicking the Open To Browse command button opens the data access page at which the link points in Page view. This opens the page for normal viewing so that you can test its operation from within Access. A click of the Open To Edit button opens the page in a view similar to the one shown in Figure B. This view permits you to edit the data access page’s design. If you create a blank page with a UNC specification with Text0 and the Create Page command button, you can populate the page with controls by selecting the name for the data access page link in Combo0 and clicking the Open To Edit button. A click of the Delete button erases the selection in Combo0 both from the link in the Database window and the stand-alone file for the matching data access page. To keep the presentation easy to follow, this operation occurs without any prompts, and it always erases both the link and the file. However, since you can modify the code, which appears in Listing B, you can cause the procedure to operate any way that your application requires. For example, you can block the deletion of selected files, and you can display a reminder about the importance of not deleting these files.

Listing B contains the event and sub procedures behind the form in Figure E. The procedures appear more or less in the order of their use. For example, the Form_Open event procedure appears first, and it fires when the form opens. This procedure calls a second one, PopulateCombo0, that populates the combo box with the names of all the links for data access pages in the current Access database file or Access project file. Because the PopulateCombo0 inserts the names of links in the drop-down list for the combo box with the AddItem method, the Form_Open event procedure must set the RowSourceType property to the string constant of Value List.

Several event procedures call the PopulateCombo0 procedure. To make it useful for all procedures calling it, PopulateCombo0 performs more functions than just populating the combo box drop-down list. In particular, it clears the combo box's selection by setting its Text to an empty string. It applies the same technique to clear the RowSource property for the combo box. This empties the combo box's drop-down list. The AddItem method adds a series of string values to the drop-down list. The PopulateCombo0 procedure derives these string values with a For…Each loop from the AllDataAccessPages collection object. This object contains the names of all links for data access pages in the current Access database or Access project file. As is typical of collection objects, the AllDataAccessPages object contains the link names and selected other link properties, not the actual links or pages.

The cmdCreate_Click event procedure fires when a user clicks the Create Page command button on the form shown in Figure E. The procedure begins by turning on the hourglass cursor—creating the new page can take a few moments. Next, the procedure assigns values to string variables for the new data access page and the data source for the page. The procedure derives the value for the link from the Text0 text box. Since the event procedure calls the NewDataAccessPage2 procedure, which is the same one that appears in Listing A, the link and the data access page have the same name. The event procedure's listing shows the syntax for specifying the optional path for the data source behind the data access page. The next set of comments illustrates the alternate syntax for accepting the default path specification for the data source. After adding the new link and page, the event procedure calls PopulateCombo0 to refresh the combo box's drop-down list to show the new link. The procedure closes by performing various cleanup functions, such as clearing the text box for the link name and restoring the normal cursor.

The cmdBrowse_Click and cmdEdit_Click event procedures both open the data access page corresponding to the link whose name appears in the combo box. Both event procedures invoke the OpenDataAccessPage method for the DoCmd object. This method takes two arguments: the name of the link pointing to a page and an intrinsic constant specifying how to open the page. The cmdBrowse_Click procedure uses a constant that opens the page in Page view so that you can use the page as if it were open in a browser. The cmdEdit_Click procedure applies another intrinsic constant that instructs Access to open the data access page in Design view, as in Figure B.

The final event procedure in Listing B, cmdDelete_Click, removes the link and matching data access page whose name appears in the combo box. This event fires when a user clicks the bottom button on the form shown in Figure E. The procedure invokes the DoCmd's object DeleteObject method to remove the link. Next, the procedure uses the Kill statement to delete the stand-alone file for the data access page. This statement relies on a string variable computed as a function of the path to the page, the page name, and an .htm extension. After removing both the link and the file, the procedure invokes the PopulateCombo0 procedure to eliminate the link name from the drop-down list for the combo box.

Better reporting for users with the Data Source control
With the Data Source control, you can graphically create forms and interactive reports that users can open with IE 5+ browsers. This article complements the rich Microsoft Help resources available for data access pages and the Data Source control. In particular, I’ve highlighted techniques for administering data access pages. And, from the concluding sample, you’ve learned to use VBA coding techniques that you can adapt and extend for administering the data access pages in your custom solutions.

Editor's Picks

Free Newsletters, In your Inbox