Chapter 10: Creating Data Access Pages
Chapter 10: Creating Data Access Pages
This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.
Ken Getz, Paul Litwin, Mike Gilbert
Reproduced from Access 2000 Developer's Handbook, Volume 2, by Ken Getz, Paul Litwin, and Mike Gilbert, by permission of SYBEX Inc. ISBN 0-7821-2372-4. Copyright 2000, SYBEX Inc. All rights reserved. For further information, please contact email@example.com, or call 1-800-227-2346, or visit their Web site at http://www.sybex.com/.
Download this article in Microsoft Word format (ACSybex10.exe, 985 KB).
`What Are Data Access Pages?
What Can You Do with Data Access Pages?
Entering or Editing Data
Creating Data Access Pages
The Simplest Technique
Using the Database Window Shortcuts
Managing Data with Data Access Pages
Creating a Moderately Complex Page on Your Own
What Is the Designer Doing?
Working with Controls
The Record Navigation Control
More Data Management Issues
Managing Data Access Pages
Placing Data Access Pages
Importing Data Access Pages
Deleting Data Access Pages
Deploying Data Access Pages
Linking Files and Data
Fixing Both Issues in One Pass `Summary
In creating Office 2000, Microsoft took on a huge challenge: it wanted each of the Office products to be able to present its native data in some format within a Web browser. This "browserability" needed to be transparent and simple to accomplish for the end user. In addition, Microsoft wanted each of the products to allow data editing from within the browser, if possible.
For products like Word and Excel, the products' data and its formatting are inextricably intertwined. That is, the only real way the teams could meet their goals was to provide round-trip data transformations from the native data store (.DOC or .XLS files) to the Web (in XML format) and back. For those products, this technique works well.
In Access, however, data and its presentation are not so tightly bound. You store data in one place and use a number of different possible techniques for displaying and editing that data. Therefore, the issues facing the Access team weren't as "cut and dred" as those facing the other teams. Although there may have been several possible alternatives, in the end the Access team produced a new technology, data access pages, which allows you to easily create Web-based pages that allow display and editing capabilities. In this chapter, we'll discuss the issues involved in creating and deploying data access pages (DAPs), taking a look at their various uses, the controls that Access provides for use on the pages, and several issues involved with deploying these pages for end-user applications.
This chapter focuses on design-time issues only. That is, you'll find information here on creating and designing DAPs, binding the DAPs to Jet or SQL Server™ data, and handling coding issues involving the location of DAPs. In Chapter 11, you'll find information about working with DAPs at runtime. Chapter 11 discusses scripting in general, and programming DAPs in specific. Once you've created a DAP and need to know how to make it do real work, you'll want to investigate the material in Chapter 11 as well.
Because data access pages store hard-coded links to their data source internally, and because Access stores hard-coded paths to the files containing the HTML for the data access page, the examples contained in this chapter will not operate correctly until you've reset these paths. To do that, open the basResetDAP module from CH10.MDB, place your cursor inside the first procedure (adhValidateDAPLinks), and press the F5 key. (For more information on this procedure, see the "Deploying Data Access Pages" section at the end of this chapter.) Although you might want to run this procedure automatically in your applications, or provide a user interface for running this procedure, we opted not to do that, as every application needs to handle this situation differently.
The data access page control toolbox contains three controls provided by the Office Web Components: Office PivotTable®, Office Chart, and Office Spreadsheet. We've put off coverage of these three useful components until Chapter 12, so that it follows the chapter on scripting. If you're interested in those particular components, check out Chapter 12.
What Are Data Access Pages?
Data access pages allow you to display and (sometimes) edit data provided either by Jet or by SQL Server, in the context of a Web page. You use the data access page designer from within Access to create the pages (although you could just as easily use any other ActiveX®-enabled designer, the Access designer makes this much easier), and you can display the pages either within Access—in Page view—or from Internet Explorer 5.0 or higher.
**Tip **When you're working in Access, displaying a data access page in Page view is basically the same thing as displaying it in the browser. Access simply hosts a full instance of Internet Explorer in an Access window, so that you're seeing what users will see in a browser. The big difference is the location of the HTM files. Normally, if you want users to use a browser to view your pages, you'll want to place the pages (and the database, if you're reading data from an MDB file) on a Web server. If your application displays data access pages in Page view, the HTM files normally "live" in a local folder.
Basically, you can think of data access pages as a cross between forms and reports, available in a browser environment. In limited circumstances, users can edit data on a data access page (if you're showing data from only one data source, you've not grouped the data, and you have the appropriate rights). The most exciting part about data access pages, however, is the ability to interactively drill down into grouped data. This provides a report-like "feel," without needing to view all the data. Users can select just the subsets they want, based on the groupings you've created at design time. This chapter includes demonstrations you can walk through, creating various types of data access pages.
When you create a data access page within Access, you're actually creating two independent "pieces":
- The DataAccessPage object, within Access, which contains little more than the location of the associated HTM file
- The HTM file, which contains all the HTML and XML source code for your page
This separation causes its own share of deployment issues, some of which you'll find discussed at the end of this chapter. (In this chapter, we deal mostly with issues involved with deploying data access pages used within Access applications. For information on deploying data access pages in general, see Chapter 16.)
To link the HTM file with a data source, data access pages use a component from the Office Web Components (see Chapter 12 for more information on the Office Web Components) called the DataSourceControl control. This invisible control provides data linking for specific HTML and ActiveX controls that you place onto your data access pages, and makes it possible to group and sort data as well. Although you'll never see the DataSourceControl object, Access automatically creates an instance of this control (with a Name property value of "MSODSC") and adds it to your data access page when you first bind data to the page.
Data access pages take advantage of ActiveX controls (the Office Web Components) that require an Office license. That is, anyone using Office Web Components must, legally, have an Office license (either an installed copy of an Office 2000 product, or a license purchased by a company as part of some licensing package). If for no other reason, this is reason enough not to use data access pages for public Web pages—unless Microsoft changes its licensing stance, you don't have the legal right to use these particular ActiveX controls in an environment where users don't necessarily have an Office 2000 license.
To sum up data access pages, think of an HTML file containing ActiveX controls that can be bound to Jet or SQL Server data. Microsoft® Office provides ActiveX controls that manage the linking, sorting, and grouping, and the data access page designer makes it simple for you to create these bound Web pages.
At this time, because Access can only retrieve data using Jet or SQL Server, the limitation flows over to data access pages too. Data displayed on a data access page will either come from Jet (as data stored in an MDB file, or linked tables) or from SQL Server 6.5 or higher (as native SQL tables used in an Access Data Project [ADP file], or linked server tables).
Data access pages provide a simple way to create Web pages for intranet reporting and data entry. If you're using them on a Web server, however, you may want to limit their use to SQL Server. With MDB files, all data processing must occur on the client. This means that Jet must be installed locally, and all data manipulation happens locally. Perhaps this isn't a problem, but it all works better using SQL Server data, where the processing occurs on some other machine.
What Can You Do with Data Access Pages?
If you use data access pages within an application, you're likely to end up performing one of these three types of tasks:
- Analyzing data
- Reviewing data
- Entering or editing data
The following paragraphs describe how you can accomplish each of these goals, and each goal references a sample you'll find in the Northwind.MDB sample database that comes with Access.
Users may want to be able to view and summarize data at will, collapse and expand various groups, and rearrange relationships to meet their needs. In order to allow users to analyze data, you'll most likely want to use the PivotTable control, available as part of the Office Web Components. You can bind this control to any standard data source, and users can work (read-only) with the control and its data interactively. You can also use one or more Office Chart controls to display the results of the analysis performed in the PivotTable control. See the Sales page in Northwind (see Figure 10.1) for an example of this type of page.
Figure 10.1: The Sales page in the Northwind sample database allows you to analyze data using the PivotTable and Office Chart controls.
Most commonly, you'll use data access pages to give users the ability to interact with groups of data, drilling down into the data as necessary. To create these types of pages (and this chapter includes a detailed walk-through to help you get started), you'll place bound controls on the page, grouping as necessary. Users can expand and contract levels until they find the data they need. To see an example showing this use of data access pages, see the Review Products page in the Northwind sample database (Figure 10.2).
Figure 10.2: The Review Products page in the Northwind sample database allows you to drill down into the various categories.
Entering or Editing Data
In limited circumstances, you can allow users to edit data on a data access page. In order for this option to be available, you must follow these rules:
- Your page can contain data from only a single source (one table or query, for example).
- You must use simple controls, like textboxes, combo boxes, and so on. You cannot use the bound PivotTable control.
- You can only display a single row at a time.
- You should customize the record navigation bar (setting properties of this ActiveX control) to manage your users' access to data. See the "The Record Navigation Control" section later in the chapter for more information. See the View Products page in Northwind for an example of a page that allows you to edit data (Figure 10.3).
Figure 10.3: The View Products page in the Northwind sample database allows you to edit data.
A Few Qualifying Words about Data Access Pages
There's no doubt about it: data access pages are a 1.0 technology, if that. The designer, to put as positive a spin on it as possible, needs a lot of work. There's no Undo capability, as amazing as that sounds! The whole concept requires Microsoft's Internet Explorer 5.0 (which every Office 2000 user has, of course, but what about the rest of the world?). We can't see any way that you'll want to use data access pages for anything but internal use, where you can control and verify browser support from all your users. You're limited to Jet and SQL Server data (not a terrible limitation, and Access suffers from the same exact limitation—all data must either go through Jet, as linked tables, or through SQL Server). Data access pages provide a simple way to get data into a browser, that's for sure. And Microsoft plans, as far as we know, to continue to improve the tools and the functionality in future versions. Right now? You'll have to decide for yourself. Work through the examples in this chapter, use the information here as a resource, and check out data access pages in limited tests. You may find that they solve a problem you need solved, and can do it right now.
Creating Data Access Pages
Access provides several ways to get a data access page into your projects. This section introduces the techniques you can use for adding pages.
You'll note one conspicuous absence, in terms of creating data access pages—Microsoft has provided no way to convert an existing form or report into a data access page. Although this is one common request, it's just not available as part of the package. Trigeminal Software has created a wizard to perform this task for you, however, and you'll find this tool on the book's accompanying CD.
The Simplest Technique
Although you'll see a number of more flexible techniques in this chapter, the simplest way to add a data access page to your project is to follow these steps:
- Select a table or query.
- Choose Insert | Page, or click the New Object toolbar icon and select Page from the menu.
- From the New Data Access Page dialog box, select AutoPage: Columnar, then click OK.
- Stand back, as Access creates a simple data access page for you.
Because this technique creates a page using simple controls, based on a single data source with no grouping, the data on the page should be editable (as long as the data source was editable).
Using the Database Window Shortcuts
Unless you've turned off the display of the new object shortcuts (use the Tools | Options | General dialog box to turn this option on and off), you should see three built-in objects at the top of the list of available Page objects (see Figure 10.4). The following sections describe how to take advantage of each of these shortcuts.
Figure 10.4: Unless you've hidden them, the built-in new object shortcuts make it easy to get started with data access pages.
Create Data Access Page in Design View
If you select this shortcut, Access creates a new empty data access page and opens it in Design view. This is similar to opening a new form or report in Design view—what you do next is completely up to you. Most of this chapter focuses on creating new pages this way.
Create Data Access Page by Using Wizard
Selecting this option brings up the Page Wizard, which walks you through the steps of creating a page. You can select fields, groupings, sorting, and a theme for your page. We won't insult your intelligence by walking you through the wizards here—it's definitely worth your time to stop and work through creating a few pages this way, however, if you've not already done so.
Using the wizard to create a page for you is a great way to see how to apply groupings and sorting to your pages.
Although we won't devote much time to styles and how you use them with data access pages, they do add an extra detail to worry about when deploying your pages. See the "Deploying Data Access Pages" section later in the chapter for more information.
Edit Web Page That Already Exists
Selecting this option provides a useful solution to two problems you might encounter:
- How do you move a data access page from one database or project to another without changing the data source for the page to be the database/project you're in? (If you simply import the page, Access changes the data source for the page so that it attempts to retrieve its data from the current database or project.)
- How do you take an existing Web page and add data access page functionality to it?
When you add an existing Web page, Access opens the HTML document in Design view. If you decide to add it to your project, make sure you save it—at that point, Access actually creates the data access page shortcut in the Database window. If you decide not to save the page, Access won't add it at all.
The way Access behaves when you add an existing page to your project can be confusing. Imagine that you've added an existing page, you make changes, and then close it. You decide, at this point, that you don't want to save your changes—you simply want to use it as it was originally, but you do want it available as a data access page in your project. If you select No when Access asks if you want to save your changes, the project ends up as if you'd never asked to add the existing page. Access doesn't add a shortcut to the existing page; to make that happen, you'll need to add the existing page and save it.
Managing Data with Data Access Pages
Although you can create data access pages that aren't bound to any data source, one has to wonder why you'd bother. That is, there are much better HTML page designers out there. If you're working in Access, however, we're assuming you want to create bound pages, so that users can display (and in the rare instance, edit) data coming from your Access or SQL Server database.
Creating a Moderately Complex Page on Your Own
Once you've run the wizards to see what data access pages can do and how much the wizards can do for you, you might like to create a reasonably complex page from start to finish. The steps presented in this section walk you through creating a complete (if somewhat ugly) data access page. If you want to apply a style to the pages, feel free, although the steps won't include that option.
Throughout these steps, the text will instruct you to take actions and create objects that we've not yet discussed. We suggest that you follow along, see what the page does, and fill in the details later as we work through various data access page topics in this chapter.
Create an Editable Page
To get started, you'll create a data access page that allows you to edit data, pulled from qryCustomersWithFullName. This query takes all the fields from tblCustomer and adds a FullName calculated field. Follow these steps:
From the Database window (using the sample database for this chapter), select Pages, click the New button, and select Design View from the New Data Access Page dialog box (or use whatever technique you like that ends up with an empty data access page).
Click the "Click here and type title text" label on the page and enter Customer Information.
Make sure the Field List window is visible, either using the appropriate toolbar icon or the View | Field List menu item.
In the Field List window, find the Queries node of the tree view and open it to select qryCustomersWithFullName. Open the query's field list by clicking the "+" sign next to its name.
Drag each of the following fields to the grid on the page, arranging them so they look somewhat like Figure 10.5: CustomerID, FullName, and Phone.
Keep in mind that when you first create a page, the grid section is named Section: Unbound. After you place a bound control in that grid, the name changes to match the data source for the bound control.
Although it wasn't strictly necessary to place CustomerID on the page, the data wouldn't be editable if you didn't. This is, apparently, one of the limitations of using the data access page with a query as its data source: the data won't be read/write unless the primary key field is available on the page. Of course, CustomerID and FullName are read only, no matter what other restrictions apply, but with CustomerID on the page, Phone should be editable.
Figure 10.5: After placing fields on the page, arrange them like this.
There's nothing magical about the grid on a data access page. It allows you to place controls in a two-dimensional manner—that is, controlling the placement and size of the controls. If you place controls on the body of the page, they're still bound to the same data source, but you cannot manage their locations. This is the way most HTML documents work—controls appear in a linear (one-dimensional layout on the page).
Choose File | Save to save the page. Assign it the name "Demo1" and save the HTM file in the same folder as the sample database. (If you closed the page, re-open it in Design view now.)
Choose View | Page View and work with your simple, editable page. You should be able to edit the Phone field for any row of data. (You can use the navigation buttons on the Record Navigation control to move from one row to another.) Figure 10.6 shows how the page should look.
Figure 10.6: In Page view, you should be able to edit fields.
Try out the sorting and filtering buttons on the record navigation bar. These tools work just like the corresponding Access form tools. Select the FullName or Phone field and then select the A to Z sorting. You'll see that the data has been sorted at your request. When you're done, close the page.
Launch a copy of Internet Explorer 5.0 or higher and choose File | Open to browse to the HTM file (named Demo1.HTM) you saved. Open the page, and it should look like Figure 10.7. Note that you can still edit phone numbers from the browser. Close the browser when you're done.
Figure 10.7: In the browser, your page should look like this.
If you have access to a Web server, you can set up a Web folder from any Office File Open or File Save dialog box and then save your page to the Web. Then use Internet Explorer to browse to your saved page, using an HTTP address (such as http://www.yourserver.com/Demo1.htm). If you have direct access to the Web server, you can simply copy the HTM file to the appropriate folder on the server (along with any subsidiary files).
Open the Demo1 page in Design view again, and open the Sorting and Grouping window. (You can either use the appropriate toolbar icon, or choose View | Sorting and Grouping to display the window.) In this window, find the Data Page Size property, and change it from 1 to All. (See Figure 10.8.) Doing this will cause the page to display all the rows at once (like a continuous form in Access) when in Page view or in the browser, but will also make the data read-only. (Data access pages can only edit data shown one row at a time.)
Figure 10.8: Set the Data Page Size value to All, in order to see all rows without scrolling. This makes the data read-only, however.
Test the behavior in Page view to convince yourself that it works. You'll see all the rows at once, but you won't be able to modify the Phone field any more. Switch back to Design view once you're done.
To finish off this section, spend some time working with the Record Navigation control. For example, modify the Show… properties (on the Other page of the Properties window) to hide the buttons that aren't active with the page laid out as it is currently. Change the RecordsetLabel property to something like this (for more information on the RecordsetLabel property, see Table 10.5 later in the chapter) and then view the page in Page view again:
Customers |0 of |2;Customers |0-|1 of |2
When you're done, close and save the page.
We've already saved a version of this page as Step1 in the chapter's database. You can use this as the starting point in the next exercise, if you don't wish to use your own page.
Create a Group
In this section, you'll add a group to the page, showing information from both qryCustomersWithFullName and tblOrder. As with any page, once you add a grouping to the page, you can no longer edit the data, so this one will be for display purposes only.
In this, and all the rest of the pages you create, you can test the page either from within Access, or from Internet Explorer. The behavior should be the same either way. The step-by-step instructions won't prompt you to test both ways, but it can't hurt.
Follow these steps to create the sample grouped page:
Make sure your data access page is open in Design view. Delete all the existing controls from the page, one at a time.
With the page selected (you can click on the page's title bar to select it), find the DefaultControlType property on the Data page of the Properties window, and set it to be "Bound HTML" (as opposed to "Text Box"). These controls are more efficient than textboxes when displaying data.
Bound HTML controls display faster than Text Box controls, but they don't have attached labels. If you're creating a data access page that's to be used for displaying data, you should consider setting the DefaultControlType property to Bound HTML for the page.
From the Fields List window, select qryCustomersWithFullName and add the FullName and Phone fields to the page. Lay out the two controls horizontally across the top of the section, leaving some room at the left for the Expand control that Access will soon add to the page for you. After you've set this up, the page might look something like Figure 10.9.
Figure 10.9: The first step in creating a grouped page.
From the Fields List window, select tblOrder, and drag the OrderDate field over to the Header: qryCustomersWithFullName section. Don't drop the field yet, however! You want to create a new grouping section, and the easiest way to do so is to drop the field below the existing section. To do that, watch for the divider line between the sections, as shown in Figure 10.10. Once you see that divider, drop the field onto the divider itself.
Figure 10.10: Watch for the divider line with the arrows before dropping the grouped field(s).
At this point, you've asked Access to join a query (qryCustomersWithFullName) and a table (tblOrder). Because the data access page designer doesn't know how these two data sources are related, Access must ask you to supply the linking fields. Figure 10.11 shows the New Relationship dialog box, where you can select the field name(s). In this case, the dialog box "guesses" that CustomerID and Notes are related (they have the same names and data types). Remove Notes from each source by selecting the top-most, empty value in the combo box, and then click OK. Once you dismiss the dialog box, you'll see the grouped data access page, as shown in Figure 10.12. (Note that Access has added a new section for the tblOrder data and an Expand control to the original section.)
Figure 10.11: Choose fields from the lists to create the ad hoc join.
Figure 10.12: After you drop the grouped field, you'll have a new section for the group.
Choose View | Page View to display the page as it would appear in a browser. Click on the "+" (Expand) control, to see that each customers' order dates appear as a grouping. Choose View | Design View to revert to design view.
Save your page.
This page has been saved for you in the sample database as Step2. You can use this as the starting point in the next exercise, if you don't wish to use your own page.
If you can't open Step2, or any other data access page in this sample database, make sure you've run the adhValidateDAPLinks, as discussed at the beginning of this chapter.
Add Another Grouping Level
In this section, you'll add a third grouping, use the Sorting and Grouping dialog box to turn on a caption section, and change the number of rows displayed at once. Follow these steps to continue the demonstration:
With your data access page in Design view, select the Fields List window and find the query named qryOrderDetails. Open the query's list of fields, and drag the MenuDescription field to the area immediately above the NavigationSection: tblOrder section, watching for the double-arrowed divider (discussed in Step 4 of the previous section). Drop the field and dismiss the New Relationship dialog box, accepting the default suggestions.
Add the Quantity and Price fields to the Header: qryOrderDetails section. You should now see three Header sections, and three NavigationSection sections on your page.
Because you might like to see the Description, Quantity, and Price fields displayed in a tabular format, it would be nice to have column headings for those fields. To do that, you'll need to add a caption section for your data, as shown in Step 3.
Choose View | Sorting and Grouping (or its equivalent toolbar icon) to display the Sorting and Grouping window. In the top pane of the Sorting and Grouping window, click on qryOrderDetails to select that particular group, and then find the Caption Section property in the lower pane. Set the value of this property to Yes.
Rather than showing one row at a time, you might like to see all the rows at once. To do that, set the Data Page Size property to All.
If you're showing all the detail rows at once, there's no need to display the record navigation section, so set the Record Navigation Section property to No. Close the Sorting and Grouping window.
Add three Label controls to the Caption: qryOrderDetails section, and set their positions and text as shown in Figure 10.13. (We've set the Price and Quantity captions to be right-aligned, as the values in those fields will be right-aligned as well. We've also set the captions to be displayed bold, so they stand out on the page.)
Figure 10.13: Lay out the caption section like this.
View the page either within Access or from Internet Explorer, and try opening the various sections. Note that the caption section only appears when the section above it is expanded, and that the page displays all the rows for the lowest-level detail section without navigation. Figure 10.14 shows how the page might look at this point.
Figure 10.14: After adding a third grouping with a caption section, your page should look like this.
The chapter database includes a data access page named Step3, which includes all the steps taken up to this point.
Create Summary Data
Once you've added a tabular grouping, you might like to add summary data—that is, sums for the Quantity and Price fields, to be displayed at the end of each order's data. Follow these steps to add summary data to your page:
Open your data access page in Design view once again and make sure the Sorting and Grouping window is visible.
Because you're adding a footer for the qryOrderDetails section, it makes sense to select that group in the Sorting and Grouping window. Unfortunately, you cannot. By design, the lowest-level group cannot have a footer section. To work around this, you must add a footer section to the next higher grouping—tblOrder, in this example. Find the Group Footer property in the Sorting and Grouping window, and set it to Yes. This adds a Footer: tblOrder section to the page.
Select the Quantity textbox, in the Header: qryOrderDetails section, and press Ctrl+C to copy the control to the Windows Clipboard.
Select the Footer: tblOrder section, then press Ctrl+V to paste the copied control into this section. Access resets the ControlSource property for the control to GroupOfQuantity1: Quantity (indicating that this is a grouped field) and sets the TotalType property to Sum (which is exactly what you want). Move the new control so that it's right-aligned with, and immediately below, the original Quantity control.
Repeat the previous step for the Price control.
If you like, add a Label control to the Footer: tblOrder section, with the caption "Totals:". Once you're done, the page might look like the page shown in Figure 10.15.
Figure 10.15: After adding a group footer, your page might look like this.
Run the page in Page view, then expand and contract the various sections to view the data. Note that you see summary information for the Quantity and Price fields. Go back to Design view when you're done.
You'll see, if you look carefully, that the summary information is visible as soon as you expand the tblOrder section—that is, the Header: tblOrder and Footer: tblOrder sections become visible at the same time. This can either be seem as an advantage, or an issue. That's the way this page works, however.
You won't be able to add a footer section for the lowest grouping on your page. You'll need to work around this limitation. In the example page, you created a footer for the next-higher grouping level.
The chapter database includes a data access page named Step4, which includes all the steps taken up to this point.
Work with a Group Filter Control
As you saw when you worked with the page so far, all the customer rows appear individually on the page. You may want to allow your users to select a particular customer and show only the orders for that customer. The Access data access page designer makes this simple by allowing you to place a listbox or combo box in a section, and use that control to filter the rows. (Access calls this a group filter control). In the following steps, you'll work through adding such a control to the demonstration page you've been building. Follow these steps:
Open your page in Design view.
Delete all the existing controls from the Header: qryCustomersWithFullName section (including the Expand control that Access added for you).
Make sure the Toolbox window is visible and then click on the Dropdown List (Combo box) control.
In the Fields List window, right-click on the qryCustomersWithFullName.FullName field, and drag it over to the Header: qryCustomersWithFullName section. Release the mouse.
From the context menu, select Group Filter Control.
Unless you follow the steps carefully, Group Filter Control won't be available on the context menu. You must select the Dropdown List (or List Box) control first, then right-click/drag the correct field, and then select Group Filter Control from the context menu. Not an easy operation.
If you like, modify the caption for the new control (perhaps "Select a Customer") and make the combo box wider.
Because you won't need a RecordNavigationControl control for the customer data, open the Sorting and Grouping window again, select the qryCustomersWithFullName section in the window and set its Record Navigation Section property to No.
Display the page in Page view (or open in a browser) and select a customer from the combo box. As you do, the page displays just the orders for that particular customer.
The chapter database includes a data access page named Step5, which includes all the steps taken up to this point.
That's the end of the "guided tour" section. You've seen how to create a simple editable page, as well as a more complex, grouped page. The rest of the chapter focuses on explaining the details of what you've done and how else you might use the data access page designer.
What Is the Designer Doing?
As you're laying out controls and data fields on the data access page designer, what you're actually creating under the covers is an HTML document. Because of this, you should be aware that everything you place onto this page becomes an HTML element. For example, the header section (including the text "Click here and type title text") is an HTML element defined with <H1></H1> tags. The area immediately below this section is a paragraph defined with <P></P> tags. The data access page designer creates the following HTML source—along with a bunch of other HTML elements—when you enter the text shown in the graphic below (choose View | HTML Source to view this source):
<CENTER> <H1 id=HeadingText style="FONT-WEIGHT: normal"> Title Section </H1></CENTER> <P>This is where you might place a description of your page, or instructions to your users.</P> <P>Each line of text becomes a <P></P> section in the HTML document. Press enter to create a new paragraph or let the text wrap to fit the page size.</P>
Other objects that you place on the page become different types of HTML elements: the sections become DIV elements, and controls become either intrinsic HTML controls or OBJECT tags. You may find it instructive to investigate the HTML created by the data access page designer (choose View | HTML Source to display the Microsoft Development Environment with your page's source loaded).
Working with Controls
If you're going to create data access pages yourself, or modify pages created by the wizards, you'll need to be able to create and manipulate controls on the data access page designer. This section introduces the skills and information you'll need in order to be able to work with this designer (which is very different from other Access tools).
Although the standard controls you can place on a data access page look and "feel" like Access controls, they are a different beast altogether. The data access page Label, Text Box, Frame, Option Button, Check Box, List Box, Dropdown List, and Command Button all look deceptively like their Access counterparts. These controls, however, expose radically different properties, methods, and events. This chapter deals only with design-time properties of these controls. For more information on working with methods and events of the data access page controls, see Chapter 11.
Placing Controls on the Page
The steps required to place a control on the data access page are slightly different, depending on whether you're placing an unbound or bound control on the page.
Working with Unbound Controls
If you want to place an unbound control onto a data access page, simply select the type of control you need in the control toolbox and then click and drag to place the control on the grid. This works, effectively, the same as placing Access controls on a form or report.
Working with Bound Controls
If you need to place a bound control on a data access page, you have a number of options. First of all, note that when you first create a new data access page, the page is unbound. It's not until you add the first bound control to the page that Access sets up the necessary DataSourceControl object, linking the page to its data source.
The DataSourceControl is part of the Office Web Components set of ActiveX controls, provided by Office 2000. The data access page designer adds this invisible control to every bound data access page.
To place bound controls onto a data access page, first make sure that the Field List window is visible. If it's not, choose View | Field List or click the Field List toolbar button. Choose one of these options to place controls on the page:
Click a table name in the Field List window. Drag the table to the grid area of the page and let go. Access creates a bound PivotTable control (from the Office Web Components). The data in this control is read only.
Right-click on a table in the Field List window. Drag the table to the grid area of the page and let go. Access provides a context menu with two available choices: Individual Controls and PivotTable List. If you choose PivotTable List, you end up with the same read-only grid discussed in the previous bullet point. If you choose Individual Controls, Access lays out the fields in the table in a two-dimensional columnar layout. Figure 10.16 shows how a page might look after right-clicking, dragging the tblEmployee table onto the page, and selecting Individual Controls.
Figure 10.16: This page was created by dragging the tblEmployee table to the page and selecting Individual Controls.
Select any field in the Field List window. Click the Add To Page button at the bottom of the Field List window. Access adds the control in a columnar fashion, immediately below the previously added control.
Right-click on any field in the Field List window. Choose Add To Page from the context menu. Access adds the control, just as if you'd clicked the Add To Page button.
Double-click on any field in the Field List window. Access adds the control, just as if you'd clicked the Add To Page button.
Click on any of the following controls in the control toolbox: Bound HTML, Text Box, Scrolling Text (Marquee), Frame, Option Button, Check Box, Dropdown List, List Box, Bound Hyperlink, and Image. Next, click and drag a field from the Field List to the grid area of the page. Access creates the selected control type, bound to the selected field.
Select one of the control types listed in the previous bullet point, then select a field in the Field List window. Right-click and drag the field onto the grid area of the page. When you drop the field, you'll see the same context menu that was mentioned earlier in this section. Choose PivotTable List to create a pivot table containing the field and the primary key field(s) for the table.
As a special case of the previous bullet point, select the Dropdown List control, then right-click/drag a field onto the page. When you release the field, the context menu will have enabled the third item on the list—Group Filter Control. This usage for the Dropdown List, discussed in detail later in the chapter (in the "Using the Group Filter Control" section), allows you to filter the displayed rows based on the value selected in this list.
While you're getting started with data access pages, you may find it useful to turn on the data access page control wizards. (Click the Control Wizards button at the top of the Control toolbox to turn them on.) The wizards will help you set properties for Dropdown List, List Box, and Command Button controls.
Adding Fields from Multiple Tables
In the Field List window, you'll find a hierarchical listing of tables and queries, along with related tables. That is, if you've defined permanent relationships between a table listed in the Field List and other tables, Access can display the related tables in the hierarchical list. If you drag fields from multiple related tables to the same section on the page, Access perform a join between the tables for you and will allow you to edit data from the "most many" table. For example, if you were to drag the tblOrder:OrderDate field to the grid and then add the tblCustomer:LastName field, Access would display the joined tblCustomer/tblOrder data, and you would be able to edit the data from tblOrder. (The data from tblCustomer would be read only.)
If you drag fields from a single table onto the unbound section (the grid area), you'll see that Access creates a navigation section (including the record navigation control) at the same time. The section's title indicates the data source for the section. If you later place a field from a related source onto the same section, you may see the title of the navigation section change—it always displays the name of the "most many" data source.
If you attempt to drag fields from two tables that aren't related onto the same page, Access will request that you supply linking information. Figure 10.17 shows the dialog box you might see if you dragged fields from tblOrderDetails and tblCustomer to the same page. Because these tables aren't related, you won't be able to successfully supply linking information, but for tables that can be related (but for which there isn't a permanent relationship), this dialog box allows you to create ad hoc relationships.
Figure 10.17: Use this dialog box to create ad hoc relationships, as necessary.
Generally, if you want fields from two tables, you'll want to create multiple group levels. It's easier to demonstrate this with an example, so you may want to follow along, using the tables provided in the chapter's database. (These steps mirror steps shown earlier in the chapter. For those who skipped the long walkthrough, it's worth trying these few steps here.) Follow these steps to create a multi-section data access page:
Create a new data access page.
From the Field List, drag the tblCustomer:CustomerID field to the grid area of the data access page. Note that Access created a NavigationSection group for you.
From the Field List, drag the tblOrder:OrderDate field to the grid area, but drag it to the bottom of the Header:tblCustomer section. Don't let go yet! You should see the double-arrow divider, as shown in Figure 10.18.
Figure 10.18: Drag fields from the related table to the area immediately below the existing section.
Drop the tblOrder:OrderDate field while you see the double-arrow divider line, and Access will create a new NavigationSection for you, as shown in Figure 10.19. If you look carefully at the original section, you'll see that Access has also added an Expand control (the square control with the "+" on it).
Figure 10.19: When you drop a related field into a new section, Access creates the Header and NavigationSection for you.
Add more fields, as you wish, to both Header sections.
When you're ready, choose View | Page View to display the page. You should see something like the page displayed in Figure 10.20. Because you placed the related data in its own group, you can expand and collapse the secondary group using the Expand control.
Figure 10.20: In Page view, you can expand or contract the detail section.
Modifying the Layout
After designing your own page, or using the wizards to create a page for you, you'll inevitably need to modify the layout of the page. You'll immediately find that the data access page designer isn't nearly so friendly as the Access form designer. You can accomplish all the same sorts of tasks as you can when working with Access forms and reports, but it's all more difficult. This section works through some of the issues you'll encounter when modifying the layout of your data access pages. (We're assuming that you've mastered the basics of creating Access forms and reports and are interested here in the differences you'll find in the data access page designer.)
Save Early and Often
We'll get this out of the way first: there's no Undo in the data access page designer. No, you weren't missing anything when you weren't able to find this functionality. It's simply not there. We've found it useful to save often, and to use the File | Save As menu item, so that you assign your page a new name each time. This wastes disk space, but you can clean up all the extraneous pages once you're finished.
Using the Grid
It appears that the snap-to-grid feature in the data access page designer only works half the time. That is, controls snap to the grid when you're moving them, but not when you're resizing. For this reason, you may find it easier to set the Width and Height properties of controls using the Properties window, rather than attempting to set their width and height correctly using a mouse.
Moving Conjoined Controls
When you place a textbox onto a data access page, Access creates a textbox and an attached a label control. It's easy enough to resize either of the controls, and simple to move the label independently of its associated textbox. It's not possible, as far as we can tell, to move the textbox independently of its associated label, however. If you need to change the relative locations of the two controls, you'll need to drag the label, not the textbox. In addition, if you want to change the distance between the two controls, you'll need to move the label, not the textbox.
Working with Groups of Controls
Fooled you! You can't. You can only work with individual controls (except for the child label with its parent, as mentioned in the previous paragraph). If you need to move a group of controls from one place to another on a page, you'll need to move each one individually. There's simply no way to select more than a single control (or parent/child label control pair).
You can, however, use the Alignment and Sizing toolbar, shown in Figure 10.21, to help overcome this missing functionality. Choose View | Toolbars | Alignment and Sizing to display the toolbar.
Figure 10.21: Use the Alignment and Sizing toolbar to manipulate groups of controls.
To try out this toolbar's functionality, follow these steps:
- Create a new data access page.
- Double-click on the LastName, FirstName, Address, City, State, and ZipCode fields from tblCustomer, so that Access places six textboxes on the page.
- Stretch the LastName textbox, so that it's wider than the rest of the textboxes. Assume that you'd like the FirstName, and City textboxes to now be stretched to the same width as the LastName textbox.
- Make sure the Alignment and Sizing toolbar is visible, as discussed previously.
- Click on the LastName textbox to make sure it's selected.
- Click the Size Width button (second from the right) on the toolbar.
- Click on the control you'd like to have be the same width as the LastName textbox. Access will make it the same width. Notice that the focus moves back to the LastName textbox.
- Repeat Steps 6 and 7 for each control you'd like to resize to match the LastName textbox.
As you can see, this is a somewhat arduous process. You must first select the "master" control, then click the toolbar button and select the "copycat" control for each control you want to modify.
The data access page designer provides a somewhat undiscoverable shortcut that makes formatting a bit simpler. If you double-click on the toolbar button for the action you want, it becomes "sticky." That is, rather than having to select a control, click a button, and then apply that to a second control, you can select a control, double-click a button, and then click once on each control to which you want to apply formatting without going back to the toolbar. When you're done applying the formatting, press the Escape key. You'll be amazed how much easier it is to format groups of controls this way, although it doesn't excuse the data access page designer for not allowing you to select multiple controls.
Sets of Properties
The data access page controls share a large set of properties, and this section focuses on two of these sets: color properties and position properties. Because the properties apply to so many of the controls, it makes sense to "factor out" the discussion of these properties, and cover them in only one place.
Many properties involve colors. For example, the BackColor, BorderColor, and Color properties all require you to specify a valid HTML color value. HTML is flexible in this regard, and you have several options. You can specify any of the following (don't type the quotes into the Properties window):
"Transparent," which causes the element to be painted with no color, inheriting the color of the element that's behind it.
A hex value, in the format "#rrggbb" (where "rr" stands for the two-digit red component of the color, "gg" for green, and "bb" for blue). The DHTML help that comes with Office includes a Color Table page, a portion of which appears in Figure 10.22.
A named color, selected from the colors available in the help file (a portion of which is shown in Figure 10.22).
A named system color, selected from the system colors available in the help file (with names like "ActiveBorder," "ButtonFace," and so on).
An expression using the rgb function, specifying the three components of the color as values between 0 and 255, such as "rgb(255, 0, 0)." (This example gives the same result as entering the values "red," or "#FF0000.")
If you need to convert from existing color values stored as long integers in VBA, we've provided the ColorToHex function in basHexColors. This simple function handles the brute force conversion from VBA's long integer "bbggrr" format for colors (once you convert color values into Hex) into HTML's "#rrggbb" format.
Figure 10.22: The DHTML help includes a Color Table page. Use it to help choose colors.
Many properties require you to specify a location, such as the BackgroundX/Y, Left, Top, Width, and Height. In each case, you have a number of different ways you can supply the values. You can enter:
- A percentage of the parent size, using a value such as "50%."
- A measurement in one of several coordinate systems, using an abbreviation for the unit, such as "mm" for millimeters, "px" for pixels, "in" for inches.
- For some properties, such as BackgroundPositionX, you can provide specific location values, such as "Left," "Center," or "Right." See Table 10.5 later in this chapter for specifics.
Working with the Tools
These next few sections discuss some of the controls you're likely to use as you design your data access pages. We'll describe uses of some of the simple controls and properties you'll want to set in order to use them.
The Non-Helpful Data Page Properties Window
Unlike when you're working on an Access form or report, you cannot count on the Properties window for a data access page to help you much. Not only are many of the important properties confusing, poorly named, and inconsistent, but many properties that could supply a drop-down list of values don't. You're expected to simply know the possible values. In addition, some properties are available for controls for which they have no meaning, and often can't be changed (for example, the Type and Start properties for an option button). Focus on getting the control to do what you'd like, not on what each and every property does, or you'll waste inordinate amounts of time. (We know this, from personal experience.) We've supplied a large table, Table 10.5a, which includes many of the properties you'll encounter as you design data access pages, along with possible values and descriptions of each. Having this table has helped us a great deal, and we hope it helps you too. By the way, online help on these topics generally doesn't. (Help, that is.)
The data access page Label control works similarly to the Access Label control. It cannot be bound to data, and simply displays static text. To set the caption of the label, you can click once on the label to select it, and then click again to enter Edit mode and edit in place. If you'd rather, you can go to the Properties window and edit the InnerText property there. (The Access F2 shortcut key does not work in the data access page designer.)
Although the Label control has a DataFormatAs property (which can be set to either HTML or Text), we were unable to get a label control to display its InnerText property as anything besides text. (That is, we were unable to get it to render any HTML text correctly.)
Bound HTML and Text Box Controls
The Bound HTML control displays HTML-rendered text and can be bound to a field in your data. This control provides the preferred method of displaying text on a data access page, because it incurs less overhead than does the Text Box control. Its contents are, however, read only. If you want to allow data editing on your page, you'll need to use the Text Box control. The Text Box control allows for editing as well as display, but displays slower and requires more resources.
You should consider these issues when working with these two controls:
- Bound HTML controls will almost always display more quickly. If you want your page to display as fast as possible, use Bound HTML controls.
- You can specify the default control type for the entire page. Select the page (choose Edit | Select Page), and then change the DefaultControlType property in the Properties window for the page to be either Bound HTML or Text Box. From then on, any fields you drag onto the form will inherit the specified control type.
- Text Box controls have attached child label controls (see the ChildLabel property to find out which control is the child label). Bound HTML controls don't have a child label: if you want to display a label associated with the data displayed in the Bound HTML control, you'll need to place and maintain one yourself.
- If you want to display text including HTML tags, you must use the Bound HTML control. The Text Box control provides no mechanism for rendering HTML text. If you use the Bound HTML control, make sure you set the DataFormatAs property to HTML, if you want the control to render the HTML for you. (This means that you could place text into a table field like this—"This is <b>BOLD</b> text"—and the control would correctly interpret the tags and display the intended word in a bold font.) If you set the DataFormatAs property to be Text, then the control will display the text you send it without attempting to render it as HTML.
Scrolling Text (Marquee)
The Scrolling Text (or Marquee) control allows you to display scrolling text. By setting properties of the control, you can specify the text, direction, number of loops, and speed of the scrolling. Table 10.1 lists and describes the properties of the Marquee control that affect its text display. (See Table 10.5 for more complete details on the properties of this control.)
Table 10.1: Control Properties Pertinent to the Scrolling Text (Marquee) Control
|Behavior||Determines whether the text scrolls, slides, or alternates.|
|Direction||Allows you to specify the direction: left, right, up, or down.|
|InnerText||For an unbound Marquee control, determines the text that the control displays. For a bound control, determines the field name to be displayed.|
|Loop||Controls the number of times the text loops by (use –1 to loop indefinitely).|
|ScrollAmount||Number of pixels to scroll the text each time it moves.|
|ScrollDelay||Amount to delay between scrolls.|
|TrueSpeed||Determines whether the speed for a Marquee control's scrolling is based on the internal clock, with a minimum of 60ms, or based on the ScrollDelay and ScrollAmount properties.|
Setting ScrollAmount too large and ScrollDelay too small can make testing your page within Access difficult. If your text is scrolling very quickly, Access can't catch mouse clicks as you attempt to switch from Page view to Design view. You'll still be able to close the page, however.
To create a bound Marquee control, first select the control in the toolbox, then click and drag a field from the field list window onto the page. Access will create the Marquee control and bind it to your selected field. As you move from row to row, the text in the scrolling region will change to reflect the selected row.
Frame and Option Button
A Frame control allows you to choose a single option from a group of option buttons. Unlike an Access Option Group control, the data access page Frame control can contain only option buttons. Just like in Access, however, you should place the Frame control on the page first, and then place the option buttons within the frame.
To create a bound Frame control, first select the Frame control in the toolbox. Then drag the appropriate field from the Field List window to your page. When you "drop," Access creates a bound Frame control for you on the page. Then create as many Option Button controls as you need, inside the frame. Finally, set the Value property to match each of the possible values you'll want to display or enter using the frame. When you display the page, as you move from row to row in your data, the frame will select the appropriate option button, indicating the specific value for the selected field in the current row.
Unlike option buttons in Access, data access page option buttons use a text string as their value. This makes it possible to use a frame bound to the PaymentMethod field in tblOrder, and also to use option buttons to display the four values: "Cash," "Check," "Mastercard," and "Visa." In this environment, you won't have to write any code to convert from integer values to the appropriate text values.
There's always a good side and a bad side. Although you can use text values to identify the option buttons within a frame, their comparisons to data are case sensitive. Unless you're sure that your data is pristine, using text values for your option buttons may not be a good idea. The sample page, FrameOptions, demonstrates this behavior. (When you move to a row and no option is selected, that indicates there's a case error.)
List and Combo Boxes
In theory, list and combo boxes on data access pages work the same as bound list and combo boxes within Access. In actuality, many of the Access-specific features (value lists, list-filling callback functions, and so on) won't work here. Unless you care to delve into scripting, you'll need to bind the lists to some data source, so that they'll fill up from a column provided by a table or query.
Table 10.2 lists the properties you'll need to set if you want to display data from one data source and store it to another. (That's usually what you do with these controls: that is, you display a column of data from one data source and store the selection as a linking item into a second table—the table that's bound to the page as a whole.)
To make this clearer, we've provided a simple page, ComboAndListBoxes, shown in Figure 10.23. This page displays data from tblOrder and includes a combo box for selecting the customer and a list box for selecting the payment method. Table 10.2 includes a column indicating the fields used for each property, in this example.
Figure 10.23: Demonstration for combo and list boxes on a data access page.
Table 10.2: Properties Pertinent to Combo and List Boxes
|ControlSource||Field, selected from the fields available in the page's data source, where selected value is to be stored/retrieved.||tblOrder.CustomerID||tblOrder.PaymentMethod|
|ListRowSource||Source of data for the list. Set this property before any of the other List properties.||tblCustomer||qryPaymentMethod|
|ListDisplayField||Field from the list's row source that's displayed in the control's list.||LastName||PaymentMethod|
|ListBoundField||Field from the list's row source that's stored and retrieved from the bound data source.||CustomerID||PaymentMethod|
When you use a bound ComboBox control, beware that matches against underlying data are case-sensitive. In this example, if a row contains "VISA" as a payment method, it won't match up against any item in the list box, which contains the text "Visa". You'll simply see nothing in the control for that row.
We were unable to find any way to have a combo box perform incremental searching as you type. If there's a property that controls this behavior, it is certainly well hidden.
Table 10.3 lists other properties associated with list and combo boxes that you may find useful.
Table 10.3: Miscellaneous Combo and List Box Properties
|Length||Returns the number of items in the list. Appears in the Properties window, but you'll only use it in scripting code.|
|Multiple||If set to True, allows multiple selections. If you set this to True for a Combo Box, the control automatically becomes displayed as a List Box. Setting it back to False makes it appear again as a Combo Box.|
|SelectedIndex||Indicates the selected item in the list. For a bound control, setting this in the Properties window has no effect.|
Although you needn't create bound Combo or List Box controls, if they're unbound, you'll need to write some code to fill them with items. See Chapter 11 for more information on scripting data access pages.
The Hyperlink control allows you to insert a link that can navigate users to another page, to a bookmark within a page, or to send email. When you insert a Hyperlink control on a page, Access pops up the (rather dense) dialog box shown in Figure 10.24, allowing you to select the destination of the link.
Figure 10.24: Choose your hyperlink's destination from this dialog box.
Although you can set properties individually for Hyperlink controls, we don't recommend it. If you need to modify the behavior of the hyperlink, simply right-click on the control and choose Hyperlink from the context menu. This brings up the dialog box shown in Figure 10.24 again, allowing you to modify any of the linking properties. If you want to set the properties manually (or if you intend to use scripting, as discussed in Chapter 11, to modify the behavior of the control at runtime), you may find the properties in Table 10.4 useful in adjusting the behavior of the Hyperlink control
Table 10.4: Properties Pertinent to the Hyperlink Control
|Host||Specifies the location/port name, separated with a colon. You're better off setting the Href property instead.|
|Href||Specifies the location to which to navigate.|
|InnerText||Specifies the display text for the hyperlink.|
|Title||Specifies the tooltip text for the hyperlink.|
Like the standard Hyperlink control, the Bound Hyperlink allows you to navigate to a new page or location. The difference here is that this control allows you to supply the hyperlink Href from a data source, possibly different for each row of data. In addition to the properties mentioned in Table 10.4, you'll also need to be concerned with the ControlSource property, which indicates the field supplying the hyperlink value.
We discovered some strange behavior for bound hyperlinks: when you first place a Bound Hyperlink control on a page, Access neglects to fill in the ControlSource property for you. (Rather, it fills in the ControlSource property on the Hyperlink property page, but that doesn't get carried over to the Data page, where it makes a difference.) It does set the InnerText and Href properties for you, however. If you manually go to the Data page and set the ControlSource property, the hyperlink will display correctly.
Binding Hyperlinks to Hyperlink Fields
Although you might be tempted to bind the Hyperlink control to a Hyperlink field in an Access table, this won't work. You can only directly bind the Bound Hyperlink control to a text field. To do this, format the values in the text field as hyperlinks or email addresses.
If you want to bind a Hyperlink control to an Access field storing data in the displaytext#address#subaddress#screentip format, you'll need to bind the control to individual portions of the hyperlink field. If you want to display a portion of a hyperlink field, you can bind a data access page control to that portion using the hyperlinkpart function provided by the Data Source Control on the page (MSODSC). For example, if you had a field named Link, you could create a bound Hyperlink control on a page and set the ControlSource property on the Data page of the Properties window to be:
This sets the control's displayed value. You can also set the ControlSource property on the HyperLink page to be:
This sets the control's address for linking.
The Image control and the HotSpot Image control are, effectively, the same control: they allow you to display images on your data access page. The big difference between the two is that the HotSpot Image control provides an active hyperlink, so that clicking on the image will navigate to a new location. The Image control can be bound; the HotSpot Image control cannot.
If you want to bind an Image control to data stored in a table, you must follow these steps:
- Create a text field in a table containing paths of the images to be displayed. (The data access page will look for the images in the same folder as the page itself, unless you specify a full path for the images.)
- When designing your page, select the Image control in the toolbox.
- Drag the field containing the addresses onto the page. (This will create the Image control for you, and bind it to the field of addresses.)
You cannot bind a Jet OLE field containing actual images to an Image control—you must bind a text field (containing locations of images) instead.
To convert an Image control into a HotSpot Image control, simply add a hyperlink: right-click on the Image, select Hyperlink from the context menu, and set the linking properties of the control. (See the section on using the Hyperlink and Bound Hyperlink controls for more information.)
Once we set the Hyperlink properties for an Image control (thereby turning it into a HotSpot Image control), we were unable to remove the linking properties. That is, we never found a way to convert it back into a normal Image control. Even deleting the associated properties in the Properties window didn't remove the link. You may need to delete and re-create the control, if that's your intent.
What about the rest of the controls? Why no mention of the Office PivotTable, Chart, or Spreadsheet? Why nothing about the Movie control? Chapter 12 covers the Office Web Components. The Movie control is, well, just another ActiveX control. It's well documented, and doesn't have any direct equivalent from Access that requires "unlearning" of Access techniques. We can't cover every single ActiveX control here, and so we focused on the controls that are most likely to give you trouble.
Hyperlinks to Data Access Pages
If you want to use links on one page to display data on another data access page, you're likely to want to limit the data on the second page to just information about the selected item on the first. That is, you'll often want to use hyperlinks as a drill-down mechanism. Access makes this easy: when you set up a hyperlink using the Insert Hyperlink dialog box, you can specify a filter in the Filter Criteria for the Data Access Page textbox on the dialog box. This filter (called a server filter, as its work is done by the database engine before the page is displayed) allows you to specify a SQL Where clause, limiting the rows that will appear on the new page. For more information on using this interesting feature and on other uses of hyperlinking with data access pages, see the white paper on the accompanying CD, "Connecting Data Access Pages Together" (Connecting DAPS.doc).
As we worked on learning how to use the data access page designer, one thing became abundantly clear: this isn't the Access form designer! Not only is the design-time "feel" different, but most of the properties for familiar controls that you'll place onto data access pages are decidedly unfamiliar. As we attempted to create simple pages, we quickly became lost in the sea of new property names and values.
To make it easier for you, we've compiled a large table (Table 10.5) containing most of the properties associated with the simple elements (label, textbox, bound HTML, scrolling text, checkbox, option button, and so on) that you're likely to use on your pages. We've not documented here the controls that are clearly "foreign" (that is, controls from the Office Web Components, and the ActiveMovie® control), but have focused on the simple controls you use every day.
Online help for these properties is decidedly unhelpful, so you may want to keep this table handy as you attempt to set properties associated with the elements you place on your data access pages. Note that not all properties listed in Table 10.5 apply to all controls—for example, the ListBoundField property applies only to list box and combo box controls, and the TrueSpeed property applies only to the Scrolling Text (Marquee) control.
Table 10.5: General Control and Page Properties
|AccessKey||Specifies the accelerator key for the control or page||Any letter or number||This value allows you to press the Alt key, plus the specified letter or number, to set focus to the element. Although you can specify an AccessKey property for a page, it's not clear why you might.|
|Align||Specifies the alignment of the caption||"bottom", "center", "left", "right", "top", "textTop", "absMiddle", "baseline", "absBottom", "middle"|
|AlinkColor||Specifies the color of a hyperlink as it's clicked||See BackgroundColor for options||Unlike most other color properties, this property converts its property settings into hex values when you enter them directly into the Properties window. That is, if you enter red, Access converts the value to #0000ff.|
|Alt||Specifies alternate text for graphic images, if the image can't be displayed||Any text string|
|AlternateDataSource||ID property of an alternate data source (supposedly, if the original data source can't be found)|
|BackgroundColor||Specifies the background color for the control||"transparent", a color name, a hex color number (like #ffffff), a system color name (like "buttonface"), or a value created by calling the rgb function, and passing in three individual values (for example, "rgb(128,12, 256)")||Follow the help topics to the DHTML Color Table page for more information.|
|BackgroundImage||Specifies the background image for the control||"none", or the URL of an image to display||If you want, you can use the File:/// protocol to display a local file here.|
|BackgroundPositionX||Specifies the offset from the left edge of the control||number of pixels ("10px"), percentage ("10%"), or "left", "center", "right"||If you repeat the image (see the BackgroundRepeat property), this coordinate indicates where the repetitions start, and fill from there.|
|BackgroundPositionY||Specifies the offset from the top edge of the control for the background image||number of pixels ("10px"), percentage ("10%"), or "top", "center", "bottom"||If you repeat the image (see the BackgroundRepeat property), this coordinate indicates where the repetitions start, and fill from there.|
|BackgroundRepeat||Specifies if and how the background image is repeated||"no-repeat" (don't repeat), "repeat-x" (repeat only in the x-direction), "repeat-y" (repeat only in the y-direction), "repeat" (repeat in both x- and y-directions||Repeats start at the offset specified in the BackgroundPositionX and BackgroundPositionY properties.|
|Behavior||Specifies the behavior of the Marquee control||"alternate" (alternate between sliding right and sliding left), "scroll" (scroll in the direction specified by the Direction property), or "slide" (slide text, once, in the direction specified by the Direction property)||Applies only to the Marquee control. See the Direction property as well.|
|BGProperties||Specifies behavior for the background image for a page||"fixed", "scroll" (default)||If set to "fixed", the background image doesn't move as you scroll the page. If "scroll" (or ""), the image scrolls with the page.|
|BorderColor||Specifies the border color||(See BackgroundColor)||Only has an effect if the BorderStyle and BorderWidth properties indicate that you want a border displayed.|
|BorderStyle||Specifies the border style||"none", "solid", "double", "groove", "ridge", "inset", "outset"||Use one of the specified words to get the effect you want. If BorderWidth is 0, you won't see any border.|
|BorderWidth||Specifies the border width||"thin", "medium", "thick", or a numeric value (in pixels)||You can specify up to four different numeric values. These are interpreted as follows. One value: applies to all four sides. Two values: the first value is for top and bottom, second is for left and right sides. Three values: specifies top, right and left, and bottom widths. Four values: top, right, bottom, left, in that order.|
|BottomMargin||Specifies the bottom margin for a page and overrides the default margin||String representation of a decimal value, in pixels||If you attempt to leave this property empty, Access replaces the property with 15. You can specify 0 as a valid value, however.|
|Checked||Specifies whether a checkbox or option button has been selected||True or False||Applies only to checkbox and option button controls.|
|ChildLabel||Name of the control's child label (that is, the descriptive text associated with the control)||You can specify the same label control for several other controls, allowing for some interesting design-time behaviors. Although you can specify any control you like here, only Label controls will "attach."|
|ClassName||Specifies the class of the given element||Normally, you'll use this property to associate a particular style rule in a style sheet with a control. For label controls, for example, the class is "MSTheme-Label."|
|Color||Color to be used by the font or text rule||See BackgroundColor||Like other properties, this one is inherited from the parent. Setting the Color property of the SectionUnbound object allows you to specify color for all the controls you place on the section.|
|ConsumesRecordset||Specifies whether data is supplied by the data source control||True or False||All bound controls have either their ControlSource property (if bound to a field) or ConsumesRecordset property (if bound to an entire recordset) property value set.|
|ControlSource||Name of the field within the page's data source to bind to this control||String|
|Cookie||Unknown||Undocumented, with unknown behavior.|
|DataEntry||Specifies whether a page is used only for data entry, or if the page's recordset should be populated with data from the data source||True or False (default)||If set to True, the page only allows you to add new rows. Once you've added a row, you can click the Save button on the record navigation toolbar to save it.|
|DataFormatAs||Specifies how the data should be rendered||"html", or "text"||Although this property exists for textbox controls, neither of the specified values is allowed. For the Bound HTML control, however, the values are accepted. Using "text" forces display of the exact text in the field. Using "html" causes the control to render the HTML (so that, for example, <b> and </b> tags format the text between them as bold).|
|DefaultCharacterSet||Unknown||Undocumented, with unknown behavior. The default (in the U.S.) is "windows-1252", so it's relatively simple to guess that this specifies the character set and code page.|
|DefaultChecked||Determines whether a checkbox or option button is selected by default when the page is rendered||True or False|
|DefaultControlType||Specifies the default control type to use for displaying text||ctlTypeTextBox (0) or ctlTypeBoundHTML (1)||Bound HTML controls allow your page to load faster, but are read-only.|
|DefaultValue||Specifies the value to display in a control when you create a new row||String||In theory, the same concept as Access' DefaultValue property. Applies only to controls that have a Value property.|
|Dir||Specifies the direction of text, in language-specific environments||"ltr" (left-to-right) or "rtl" (right-to-left)||In English, the only effect of setting this property is to manage the alignment of the text: "rtl" forces right-aligned text, "ltr" forces left-aligned text.|
|Direction||Specifies the direction the text scrolls in a Marquee control||"down", "left" (the default), "right", "up"||Applies only to the Marquee control. See also the Behavior property.|
|Disabled||Specifies whether the control is disabled||True or False||Similar to Access' Enabled property, except from the opposite point of view. The default for the Disabled property is False.|
|Display||Determines whether the element is rendered||"none", "block", "inline", "list-item", "table-header-group", "table-footer-group"||Specifying "none" causes the control to be removed from the page—that is, the page doesn't reserve any space for the control (as opposed to setting the Visible property to False, which simply hides the control). Specifying any other value renders the control, but doesn't affect its display. You can use the other values if you write code that manipulates different types of items.|
|Dynsrc||Specifies the address of a video clip or VRML world to be displayed in the window||Why this property appears for the option button and checkbox controls is beyond us.|
|ElementID||Enumerated value indicating the type of selected element||Long||Normally, passed as a parameter to an event procedure as an indication of the type of element that was selected.|
|FGColor||Specifies the foreground color for text on the page||Any color value (see BackGroundColor for specifics)||Unlike most other color properties, this property converts its property settings into hex values, when you enter them directly into the Properties window. That is, if you enter red, Access converts the value to #0000ff.|
|FontFamily||Specifies the font name to be used||Specify a comma-delimited list, containing either specific font names (such as "Tahoma") or generic font families (choose from "serif", "sans-serif", "cursive", "fantasy", "monospace")||Specify a list if you care to indicate what font to use if the font you want isn't available. If you use a generic font type (such as "serif"), the browser will select a font for you. If a font name contains spaces, make sure and place quotes around the font name. You can specify as many fonts as you like, separated with commas.|
|FontSize||Specifies the font size to be used||Absolute size, from this list: "xx-small", "x-small", "small", "medium", "large", "x-large", "xx-large"; or a relative size (based on the parent's font size), either "larger" or "smaller"; an absolute value for the size, such as "10pt"; or a percentage of the parent's font size, such as "80%"||Absolute font sizes scale to match the user's font preferences in the browser.|
|FontStyle||Specifies the font style||"normal", "italic", "oblique"||IE 4 (and 5) display italic and oblique text the same.|
|FontVariant||Selects the font variant||"normal", "small-caps"||IE 4 and 5 display small caps as all capitals, perhaps in a smaller font size.|
|FontWeight||Specifies the weight of the font||"100", "200"..."900", "normal" (same as "400"), "bold" (same as "700"), "bolder", "lighter"||IE only supports normal (400) and bold (700). All other values are converted to one or the other of these values.|
|GridX, GridY||Specifies the number of dotted gridlines per inch||1 through 24||This is, obviously, a property you'll only change at design-time.|
|Height||Specifies the height of the control (for a page, the behavior is undocumented)||Specify a value ("40px", or "1.5in"), as a percentage of the parent ("40%"), or "auto"||For a page, the help link is broken, so it's unclear why this property is exposed for a page. Changing it doesn't appear to affect the behavior of the page.|
|Href||Specifies the location to which to navigate, for a Hyperlink control||Address of a page, or bookmark, or email address. See the special Insert Hyperlink dialog box for help in formatting this value.|
|Hspace||Specifies the horizontal margin||Integer value, in pixels||Use this value to indicate an offset from the left edge of the page.|
|ID||Name of the element||Just like the Access Name property for objects.|
|Indeterminate||Changes the state of a checkbox so that even if selected, the check appears greyed||True or False||Applies to both a checkbox and an option button control, but has no visual effect when set for an option button.|
|InnerText||Specifies the text to be displayed in a caption||Similar to the Caption property of a label control on an Access form. Doesn't apply to a text box. For a Marquee control, specifies the scrolling text.|
|Lang||Specifies the language to use||The specifier is an ISO standard language abbreviation. See the DHTML documentation for more information.|
|Left||Specifies the left coordinate of the control||Specify a value ("40px", or "1.5in", or "10mm"), as a percentage of the parent ("40%"), or "auto"||Added to the Hspace property (if that property is supplied for the element) to position the element.|
|LeftMargin||Specifies the left margin for the page||String representation of a decimal value, in pixels||This property sets the margin on the left side of the page, and effectively moves the 2D area of the page to the left or right, depending on the value.|
|Length||For list or combo boxes, returns the number of items in the control||Surprisingly, you can set this property in the Properties window. If you set this for a list box, and you supply a value that would require scroll bars, the control displays scroll bars. You must actually add items to the control in order to populate it, however.|
|LinkColor||Specifies the color for the unvisited hyperlinks on a page||See BackgroundColor for options||Unlike most other color properties, this property converts its property settings into hex values when you enter them directly into the Properties window. If you enter red, Access converts the value to #0000ff.|
|ListBoundField||In a combo or list box, the field that's bound (as opposed to the field that's displayed)||You must set the ListRowSource property before you set the ListBoundField property. The field name selected in this property must be available as a member of the ListRowSource recordset.|
|ListDisplayField||In a combo or list box, the field that's displayed (as opposed to the field that's bound)||You must set the ListRowSource property before you set the ListDisplayField property. The field name selected in this property must be available as a member of the ListRowSource recordset. There can only be a single field in a combo box (as opposed to native combo box control in Access).|
|ListRowSource||In a combo or list box, specifies the source for the bound field and the display field||You must set this property before setting the ListBoundField or ListDisplayField properties.|
|Loop||Specifies the number of times to repeat a marquee loop, or a sound or video loop||-1 (infinite), or a number greater than 0||Documentation states that 0 is acceptable (and will loop once). Actually, the Property window rejects this value as invalid.|
|Lowsrc||Determines the lower-resolution image to be displayed in the element|
|MaxRecords||Specifies the maximum number of records that the connection will bring to the local computer||The default value is 10,000. Making it smaller requires more trips to the server to retrieve data. Making it larger requires each data request to take more time.|
|Multiple||Determines whether you can select multiple items in a list or combo box.||True or False||If True, you can select multiple items. Unlike combo boxes in Access, you can set this property to True for combo boxes. Doing so effectively turns the control into a list box.|
|NoShade||If True, corresponding rule (line) control appears without the 3D shading||True or False|
|NoWrap||Specifies whether the browser performs word wrap||True or False (default)|
|Overflow||Specifies how to handle text that doesn't fit within the confines of the defined element area||"scroll", "hidden", "visible" (default), "auto"||"scroll" adds horizontal and vertical scrollbars, whether you need them or not. "hidden" simply hides overflow. "visible" shows overflow. "auto" decides whether you need horizontal or vertical scrollbars, and adds them as needed.|
|Position||Specifies the type of positioning for an element||"static", "relative", "absolute", "fixed"||The default value is static, implying the control gets no special positioning and simply obeys the layout rules of HTML. See the DHTML help topic on Positioning for more information on using this property.|
|ReadOnly||Specifies whether the control's data is read only||True or False||Similar to the Locked property in Access. The default is False.|
|RecordsetType||Specifies the type of recordset to use for the data source control||dscSnapshot (1), or dscUpdatableSnapshot (2)||Setting this value to dscShapshot provides you with a read-only recordset. Use the default, dscUpdatableSnapshot to get a read/write recordset.|
|RecordSource||Specifies the recordset (or grouping definition) for the section||This property is actually exposed as the RecordSource property of a GroupLevel object, provided by the DataSourceControl object|
|RightMargin||Specifies the right margin for the page||String representation of a decimal value, in pixels|
|Scroll||Indicates whether a page displays scroll bars||yes (default), no, auto||It's unclear what effect this setting has—we could find no discernable differences.|
|ScrollAmount||Number of pixels to scroll the text each time it moves||Integer value indicating the number of pixels to scroll||Use a smaller value to force the text to scroll slower. See the ScrollDelay property for the interval between movements. (Marquee control only)|
|ScrollDelay||Amount to delay between scrolls||Integer value indicating number of milliseconds to wait (default is 85)||The larger this value, the slower the scroll. See the ScrollAmount property for the number of pixels to scroll each time the text moves. (Marquee control only)|
|SelectedIndex||0-based index of the selected item within a combo box or list box||-1 (nothing is selected), 0 through the Length property - 1||Although Access supplies this property on the Properties window, setting it at design time has no effect.|
|Src||Specifies the URL to be loaded by the object||Any value URL|
|TabIndex||Specifies the order elements are visited when you use the Tab key to progress through controls||(Use any integer value)||Controls are visited in this order: All TabIndex values greater than 0, in ascending order (or source order, for duplicate TabIndex values); All TabIndex values equal to 0, in source order if there is more than one. Specify a TabIndex value of -1 to omit a control from the tab order. If you set a page's TabIndex property to anything other than -1, the page itself will get the focus at the appropriate time.|
|TextAlign||Specifies the text alignment||"left", "right", "center", "justify"||Although "justify" was added for IE4, we were unable to see any difference between "left" and "justify". Perhaps you'll have better luck.|
|Title||Specifies advisory information for the control (For a Page, supplies the title bar caption)||In IE, this property becomes the tool tip displayed as you hover your mouse over the control (similar to the ControlTipText property in Access).|
|Top||Specifies the top coordinate for the control||Specify a value ("40px", or "1.5in"), as a percentage of the parent ("40%"), or "auto"|
|TopMargin||Specifies the right margin for the page||String representation of a decimal value, in pixels|
|TotalType||The type of total to be displayed in the control||dscNone (0), dscSum (1), dscAvg (2), dscMin (3), dscMax (4), dscCount (5), dscAny (6), dscStdDev (7)|
|TrueSpeed||Determines whether the speed for a Marquee control's scrolling is based on the internal clock, with a minimum of 60ms, or based on the ScrollDelay and ScrollAmount properties||True or False||The default value of this property is False (for compatibility with IE3), which means that no matter how you set the ScrollSpeed property, text in a Marquee control will never move more often than every 60ms. If you set this property to True, then your ScrollSpeed property setting will take effect.|
|Type||Specifies a text string indicating the type of the control||Button, checkbox, file, hidden, image, password, radio, reset, submit, text, select-multiple, select-one, textarea||It's unclear whether there can be more possible options than the ones listed here.|
|UniqueTable||Name of the updateable table when a form is bound to a multi-table view or stored procedure|
|URL||Unknown||This property is used by Access, but it's not documented, and its use isn't known. Leave this value alone.|
|UseRemoteProvider||Specifies whether the data source uses a remote provider||True or False||Use the default value (False) unless you're retrieving data using IIS from an HTTP/HTTPS address. Microsoft Remote Data Services provides an HTTP or HTTPS request to IIS, which retrieves the data using an OLE DB connection to the data.|
|Value||Sets or retrieves the value of the object||Effectively allows you to specify text for an unbound control. For bound controls, the value of the field immediately replaces the Value property you've specified at design time.|
|Visibility||Specifies the visibility of the control||"visible", "hidden", "inherit"||If set to "inherit", the control gets this property from the parent element. Note that unlike the Display property, setting this property to "hidden" still reserves physical space for the control on the page.|
|VlinkColor||Specifies the color for the visited hyperlinks on a page||See BackgroundColor for options||Unlike most other color properties, this property converts its property settings into hex values, when you enter them directly into the Properties window. That is, if you enter red, Access converts the value to #0000ff.|
|Vrml||Specifies the URL of the VRML world to be displayed in the object||Any valid URL||Why does this apply to a checkbox control? Your guess is as good as ours.|
|Vspace||Specifies the vertical margin||Integer value, in pixels||Use this value to indicate an offset from the top edge of the section.|
|Width||Specifies the width of the element||Specify a value ("40px", or "1.5in"), as a percentage of the parent ("40%"), or "auto"|
|ZIndex||Specifies the top-to-bottom order of the control on the page||An integer, or "auto"||"auto" (the default) specifies that the stacking order is bottom-to-top in the order that controls appear in the HTML source. Positive ZIndex values are positioned above negative (or lesser value). Two elements with the same ZIndex values are stacked according to source order.|
The Record Navigation Control
The record navigation bar that Access places onto each bound data access page is actually an ActiveX control supplied by the Office Web Components. This control works with the DataSource control to present data on a data access page. (See Chapter 12 for more information on Office Web Components.)
As with all other controls on data access pages, the RecordNavigationControl supplies many of the standard properties. In addition, the control provides some specific properties, listed in Table 10.6. You can set any of these properties in the Properties window, in VBA code at design time, or in scripting at runtime. (See Chapter 11 for more information on scripting.)
Table 10.6: Properties of the RecordNavigationControl control
|DataSource||Specifies the ADO datasource object for the control|
|FontName||Specifies the name for the font used in the control's label.|
|RecordsetLabel||Specify the label to display in the caption of the control. You can insert the following placeholders in the text: "|0" for the current row number, or the first row number if the control is displaying a range of rows; "|1" for the final row, if displaying a range of rows; and "|2" for the total number of rows. Provide two sets of text (one for single rows, one for groups of rows) separated with a semi-colon.||For example, you might use the text: "Customer |0 of |2;Customers |0 to |1 of |2". Invalid placeholders don't generate an error, but cause no value to be displayed.|
|RecordSource||Specifies the record source (the name of a recordset definition or grouping definition) It's possible that the Data Source Control supplies multiple recordset definitions, and this property specifies which one you'd like to use.|
|ShowDelButton||Show the delete button?|
|ShowFilterBySelectionButton||Show the filter-by-selection button?|
|ShowFirstButton||Show the First button?|
|ShowHelpButton||Show the Help button?|
|ShowLabel||Show the label portion of the control?|
|ShowLastButton||Show the Last button?|
|ShowNewButton||Show the New button?|
|ShowNextButton||Show the Next button?|
|ShowPrevButton||Show the Previous button?|
|ShowSaveButton||Show the Save button?|
|ShowSortAscendingButton||Show the Sort Ascending button?|
|ShowSortDescendingButton||Show the Sort Descending button?|
|ShowToggleFilterButton||Show the Toggle Filter button?|
|ShowUndoButton||Show the Undo button?|
|IsButtonEnabled (method)||Determine whether a particular button is currently enabled.||Pass in one of the values supplied in the NavButtonEnum values (for example, navBtnUndo).|
More Data Management Issues
Although you've seen, earlier in the chapter, how to create data access pages using various techniques, you may need to modify the behavior of the pages. Sooner or later, you'll need to modify the way the pages handle sorting, grouping, and filtering. This section discusses these issues, and elaborates on the various properties available to you.
Grouping on any Field
Normally, your data access pages contain data grouped by the relationships between the data. That is, you might have a page that displays customer information, and, for each customer, information about the orders placed by the customer. Within in each order, you might display all the detail rows for that order.
What if you wanted to group all the orders for a given customer by the order date? That is, rather than seeing each individual order, you wanted to see the orders for each different date grouped together? To handle this situation, data access pages support grouping on any individual field within a group. This technique is tied to the concept of promoting and demoting controls on your page. Promoting a control creates a new section, grouping on that control's bound value. Demoting a control removes its section, and moves the control back to the normal header section for its group.
To see this technique in action, follow these steps:
- Create a new data access page in the sample database for this chapter.
- From the Field List window, drag the tblCustomer:CustomerID field to the Section: Unbound section of the new page.
- Select the newly renamed Header: tblCustomer section and make it shorter (that is, drag the bottom of the section up so that the section takes up less vertical real estate).
- From the Field List window, select the tblOrder: OrderDate field and drag it to its own section, underneath the Header: tblCustomer section (watch for the blue horizontal divider with the outward arrows on the ends).
- Choose View | Page View to view the page and expand a few customer rows to get the feel for how the page works. Go back to Design view when you're done.
- Select the OrderDate textbox, right-click, and select Promote from the context menu. Access creates a new grouping section (named Header: tblOrder-OrderDate) for you.
- From the Field List window, drag the tblOrder: PaymentMethod field to the Header: tblOrder section.
- View the page in Page view once again, expand a customer, and note that now the OrderDate field has an Expand button associated with it. Expand order dates, and you'll see that you've now grouped both on the CustomerID field and the OrderDate field.
You can use this same technique to group on the value of any bound control's field. If you promote a checkbox, for example, you'll end up with two groups (the rows for which the checkbox is checked, and the rows where it isn't). This technique works if your page contains only a single grouping, as well.
The Group On and Group Interval options on the Sorting and Grouping dialog box are only available for grouped fields. See the "Managing Sorting and Grouping" section later in this chapter for more information.
Using Automatic Joins
Data access pages allow you to create joins "on the fly." That is, if you select fields from two related tables and place them onto the page, Access will do the work of creating the join for you. In addition, when you place fields from multiple tables within the same section, you can use the Group By Table toolbar icon to automatically separate out the two tables, grouping the page by the "one" side of the relationship.
This is not the way forms and reports work—in those designers, if you want to base your form or report on the results of two joined tables, you'll need to either run a wizard to do the work for you or create a query that joins the tables for you. Data access pages allow you to do this as you're designing the page, in an ad hoc fashion.
To try out automatic joins and the Group By Table functionality, follow these steps:
Create a new data access page in Design view, in the sample database for this chapter.
From the Fields List window, drag the tblOrder.OrderDate field onto the unbound section.
From the Fields List window, drag the tblCustomer.LastName field onto the (now named) Header: tblOrder section.
By following the steps so far, you've asked the DataSourceControl control that's providing the data for the page to create a join between tblOrder and tblCustomer. If you were doing this same sort of thing in an Access form or report, you'd need to stop and create a query to perform the join for you, and then create the form or report. Once the control has created the join for you, you can separate the two sources using the Group By Table functionality.
Add a few more fields from tblOrder onto the Header: tblOrder section.
Add a few more fields from tblCustomer onto the Header: tblOrder section.
Click on any field from tblCustomer. (This step is important!)
Click the Group by Table toolbar button (it's between the two arrow buttons, one pointing left, the other pointing right). At this point, Access takes all the fields from the selected table (tblCustomer) and promotes them to having a section of their own (Header: tblCustomer).
Run the page, either in Access or in Internet Explorer, and you'll see that the page is now grouped by all the fields from the tblCustomer table.
You can use the Group by Table button when you have selected any field from the "one" side of a relationship. If you select any control bound to a field from any other table, the Group by Table option won't be available.
Using a Group Filter Control
Data access pages allow you to provide a drop-down list of values from which the users can select a value to use for filtering the data in a section. This control can be a list box or drop-down list (combo box) and creating it requires a few special steps:
- In the control toolbox, select either the ListBox or the Dropdown List control.
- In the Field List window, select the field on which you'd like to filter.
- Right-click and drag the field to the section containing data for the selected table or query. When you release the mouse, choose Group Filter Control from the context menu.
- Run the page. All the fields within the section will be empty, except the group filter control, which will contain a unique list of values from the selected field. Choose a value, and the rest of the controls will populate with the appropriate values for the first matching field. You can use the record navigation control to move to other rows that match the criteria.
It's easy to forget about using the right-click/drag technique to place the list box or drop-down list on the page: if you forget this step, your control will be a standard control and won't have any filtering effect.
For a demonstration of this technique, see DAPDemoGroupFilter in this chapter's sample database. This page uses a group filter control to draw information from qryCustomersWithFullName, filtering data from tblOrder. (This demo page also shows off some other data access page features, including using caption and footer sections.) Figure 10.25 shows the sample page in use.
Figure 10.25: Use a group filter control to filter data for just the particular row you need.
Managing Sorting and Grouping
Each time you place fields from a different data source on your data access page, Access creates a new group for you. Just as on an Access report, each group has its own set of sorting and grouping properties, and you can use the Sorting and Grouping window to manage the properties for each section. (Choose View | Sorting and Grouping, or click the Sorting and Grouping toolbar icon to display the window, shown in Figure 10.26.)
Figure 10.26: Use the Sorting and Grouping window to modify the group properties.
The Sorting and Grouping options, and how you might use each item, are described below:
Select Yes to add a caption section above the group header section. This section is useful if you want to display your data in a tabular format, and if you want to place field captions above the data. You'll only see this section when the next-higher group level is expanded, so that the detail rows are visible. You can't add bound controls to this section. If you change this value to No, and the Caption section already exists, Access will delete any existing controls within the section when it deletes the section.
Select No to delete the header section (and all its controls) for the current grouping, or Yes to display the header section. The naming for this section is somewhat misleading: unlike a header section on an Access form or report (which corresponds to the Caption section here), the header section on a data access page is the section where you'll actually display the data for the grouping. (The help is displayed by pressing F1 in the Sorting and Grouping window is also misleading.)
Select Yes to display a footer section immediately above the record navigation section (if you're displaying that section) for the current group. This is a good place to put summary information and totals pertaining to the group. For reasons we can't guess, you cannot display the group footer for the lowest grouping level. Setting this value to No causes Access to remove the section and any controls it contains.
Because you can't turn on the Group Footer section for the lowest-level grouping, you simply won't be able to display a footer for a page on which you're only displaying data in a single group. Of course, that's the only way your page will allow edits, so you can't have it both ways. If you want to display summary data about the grouping, you'll need to add multiple sections. Doing so makes the page read-only.
Record Navigation Section
Use this property to show or hide the record navigation control that manages the navigation from one row to the next. This section always goes below the group footer (if you've displayed that section), or below the group header. This section can't contain any bound controls. Setting this property to No removes the section and the record navigation control it controls; if you set it to No and you still want to display data in the group header section, you'll probably want to set the Data Page Size property to All, so that users don't require any navigation method to see all the rows. Of course, this means that your page must render all the rows in your record source.
You should be careful about turning off the Record Navigation Section property and setting the Data Page Size property to All, unless you know that you won't have many rows of data. Asking the page to render all the rows in a large recordset before displaying anything to a user isn't a good idea. It's going to be slow.
Expand by Default
If set to Yes, the page will automatically expand the next-lower group level when the page is loaded. This means that the user won't have to expand the section (by clicking the Expand button). Of course, automatically expanding the section means that for each parent row, all the children rows will need to be retrieved before the page can display its data. This option isn't available unless there is a next lower section to expand.
Use this option judiciously. For each section you set to auto-expand, the page must do a great deal more work when loading. For fastest loading, make sure the top-level section has this property set to No.
Data Page Size
This option controls how many rows the page displays when the section appears. The window provides the values 1, 5, 10, and All, but you can type in any reasonable number you like. If you've set the Record Navigation Section property to No, you'll want to set this property to be All (or use a Group Filter Control to select the specific data to be displayed), so that users can see all the rows without navigation.
If you want to edit data on your data access page, you must set the Data Page Size property to 1, and you must have only a single grouping level. If either of these requirements isn't met, the page will be read only.
Group On/Group Interval
If the current section is a grouping section (that is, you've promoted a control and you're setting properties of its section), you can specify both the Group On and Group Interval properties. Group On allows you to specify the interval on which you want to group the data (for example, for date fields you can select Year, Qtr, Month, Week, and so on). Group Interval allows you to specify the number of units specified in the Group On property to group by. For example, if Group On is set to Weeks, and Group Interval is set to 2, then the output will be grouped into two-week blocks. These properties aren't available except for grouped sections.
This property allows you to specify the sort order for the grouping. Specify field names, separated with commas. If you want to sort a field in descending order, add the text "DESC" after the field name. (You can use "ASC" after a field name to indicate that it's to be sorted ascending, but because that's the default, you needn't ever specify this text.)
For example, to sort a section on FirstName descending, and for matching first names on the LastName field, you can use a Default Sort value like this:
FirstName DESC, LastName
Group Filter Control/Group Filter Field
As mentioned in the previous section, you can use a list box or drop-down list (combo box) control to filter the rows showing within a section. These two properties specify the control to be used and the field on which to filter. You'll set these properties by creating the group filter control as defined in the previous section, and we've not found a need to alter these properties in this window.
Managing Data Access Pages
As you work with your data access pages in various databases and projects, you'll need to decide where to place the pages. Sometimes you'll also need to move these from place to place or delete them. These seemingly simple actions have complex ramifications, because of the "under-the-covers" linkage between the Access data access page shortcuts and the actual HTM files.
Placing Data Access Pages
It's tempting to place the HTM files associated with data access pages in the same folder as the database or project that's providing the page, but it's worth considering one important issue with this choice: data access pages are stored on disk as individual HTM files. That means, of course, that end users can touch them, study them, and (worst of all) modify them. Any end user, armed with Notepad, can ruin your page, and there's nothing you can do about it.
To avoid this issue, you might consider storing your HTM files on a Web server. If you have access to the Web server's file storage, you can simply place the HTM files there. If not, you'll need to first set up a Web folder within Office, and then you'll be able to store files on a Web server as easily as storing them in local file storage. By placing the files in a Web server, you make it much more difficult for the average user to find and modify the file. If you've managed the security on your Web server correctly, it should be impossible for anyone who isn't authorized to touch files to be able to modify your carefully crafted data access page.
Once you've placed the pages on a Web server, deploying your application is easy: you needn't distribute the pages—they're already deployed. If you're deploying data access pages using an MDB or ADP file as a container, you simply need to provide your end users with the appropriate file. If you're using data access pages with no associated database application, you tell your users the URL to navigate to, and you're all set.
If you do place your pages on an Web site, you must be aware that some of the code provided in the "Deploying Data Access Pages" section will not work. That code relies on file system links to the HTM files, which isn't available once you've stored your pages on a Web server.
Importing Data Access Pages
Just as with any other Access object, you can import a data access page from a different database or project file. When importing a data access page, however, you're not simply importing an Access object: you're also setting up a link to an external HTM file. After selecting the data access page to import from the external database or project, Access asks you to specify the name for the HTM file. That is, it makes a copy of the existing HTM file, and you must supply a new name for the file.
After you've supplied a name, Access checks the data source for the HTM and modifies the HTM file so that rather than retrieving its data from the original file, it now retrieves its data from the import destination file. If you don't want this behavior—that is, if you want the data access page to retrieve its data from its original source, rather than the current database or project—then you use a different technique. From the Database window, select Insert | Page. From the New Data Access Page dialog box, shown in Figure 10.27, select Existing Web Page. Then, from the Locate Web Page dialog box, select your HTM file. Access will open the page in Design view. If you decide to add it to your project, close it and save your changes. If not, close it and cancel saving—Access will simply not add it to your project.
If you see the new object shortcuts in the Database window, you can select Edit Web Page That Already Exists from the list of available Page objects. This performs the same task as the steps outlined in the previous paragraph.
Figure 10.27: Use this dialog box to insert an existing Web page.
Deleting Data Access Pages
When you attempt to delete a data access page from the Database window, you're not simply deleting an Access object: you're deleting an object that contains a shortcut to a file out in the file system, or on a Web server. Therefore, Access doesn't know, without asking, whether it should delete just the shortcut, or the shortcut and the external file.
Therefore, when you delete a data access page from within Access, you'll first see the Access dialog box confirming deletion of the shortcut (unless you've set the option within Access that allows it to delete objects without confirmation). Then you'll see the dialog box shown in Figure 10.28. This dialog box confirms that you want to delete the external file along with the Access shortcut. If you select Yes, Access will delete both the link and the HTM file. If you select No, it deletes the link, but leaves the HTM file intact.
Figure 10.28: Select whether to delete the external HTM file along with the Access shortcut.
Access does not make a special case for data access pages stored on a Web server. If you've set up a Web folder and have stored your page there, deleting the link and allowing Access to delete the HTM file will cause Access to go to the Web server and delete the file from the Web folder. Our point here is that although end users can't modify the HTM file on the Web server, you (and Access) can.
Deploying Data Access Pages
Although Chapter 16 focuses on the issues involved with deploying Access applications, data access pages present two specific issues that require careful planning and coding. We'll focus on those two issues in this section. Specifically, you must understand:
- How Access maintains links between your database (or project file) and the HTML files that make up the data access pages
- How the pages themselves link to the associated data source
What if you move the MDB (or ADP) file that contains links to the data access page files? What happens if you move the data source? You must be able to resolve both of these issues, at design-time, using code that fixes up path dependencies in your database. In this section, you'll find possible solutions to both of these important deployment issues.
Creating Data Access Pages Programmatically
The code in this section uses the Access method, CreateDataAccessPage, to create new data access pages. This method allows you to create a new Page object within Access, either associated with a new HTM file or with an existing one. It does not, however, allow you to create controls on the page. What if you want to create a complete data access page from scratch, programmatically, just as you can with forms and reports, using the CreateControl and CreateReportControl functions? Access doesn't supply a CreatePageControl function, unfortunately.
To help us out, Trigeminal Software has provided a module for us, named basCreate, that contains a CreatePageControl function. This complex function allows you to create bound controls on a data access page programmatically. If you need to create your own data access page wizards or merely want to dig into the techniques involved in building data access pages programmatically, you can study the code in the basCreate module in the chapter database.
If you want to use this module, you'll need to use the Tools | References menu to set a reference to Microsoft HTML Object Library. In addition, you'll need a reference to the Office Web Components type library. This one, however, may be tricky. Look for Microsoft Office Web Components 9.0 in the list of references. It may or may not appear, depending on your version of Office 2000. If it doesn't appear on the list of available references, you must use the Browse button on the References dialog box, find the folder that contains all the Office 2000 executables and DLLs, and locate MSOWC.DLL. This adds a reference named Microsoft Office Web Components 9.0.
Linking Files and Data
Because working with data access pages will always involve managing files external to your MDB or ADP file, you'll need to be aware of the hard-coded linkage information stored in both the shortcut stored in the database, and in the HTML file that contains the data access page itself. If you break either of these links, you'll need to run code at your application's startup to fix up the broken links.
Although we think that most data access pages used in applications will take advantage of an intranet as the deployment scenario, you may find a need to deploy an MDB or ADP-based application that includes data access pages. In this case, the shortcut provided by Access (which refers to the actual HTML file), contains the full path to the file. If you break that link by moving the HTML file, you'll need to fix up the link to contain the actual address.
In addition, each data access page itself contains the connection information for its data connection. For Access databases, this information includes a full path and database name. If you've moved or changed the name of the data source, the data access page won't be able to display any data. If you're using SQL Server as the data source, it's possible that the server name may no longer be valid, or you may simply want to change the data source to be a different server. In either case, you'll need to fix the connection information so that your data access page can find its data. (Of course, if you're using an MDB to display your data access page, you're likely linking right back to the host database to find your data. Usually, however, this won't be the case. This is the least likely of all the deployment scenarios we've considered, but fixing up the links is still an important issue.)
In this chapter, we'll look only at the contingency that you're running a data access page from within an Access application, and that you can run VBA code in order to fix up these two issues.
Fixing the Path from the Data Access Page to its Data Source
Imagine that you've created a database and added a number of data access pages to the application. You get the application working, and then you deploy it on a user's machine. Now, suddenly, all the data access pages won't work because they can no longer find the data.
What happened? If you investigate the contents of a data access page, you'll find, buried in the XML code making up the page, an ADO connection string. In order to link up to the appropriate data source, each data access page file contains a hard-coded path to its original data source. If you move the data source, you'll need to also fix that path before you can display the page and its data.
The DataSourceControl ActiveX control provides the link between the page and the data source. Listing 10.1 shows a portion of the HTML code from a sample data access page, with the data source printed in bold. In this fragment, you see properties of the DataSourceControl object; among the properties is the Data Source property, which provides the path to the data source.
<OBJECT classid=CLSID:0002E530-0000-0000-C000-000000000046 codeBase=file:\\Tpwinnt\Z\msowc.cab#version=9,0,0,2710 id=MSODSC><PARAM NAME="XMLData" VALUE="<xml xmlns:a="urn:schemas-microsoft- com:office:access"> <a:DataSourceControl> <a:OWCVersion>188.8.131.5210</a:OWCVersion> <a:ConnectionString>Provider=Microsoft.Jet.OLEDB.4.0; User ID=Admin;Data Source=C:\MasterApplication\MyApp.mdb;
If you need to modify that data source, you'll need to provide some mechanism for opening the HTM file as a text file, finding that particular item within the text file, fixing it, and saving the entire mess back to disk. To make this process simpler, we've provided the adhResetDAPDataLink procedure in basResetDAP of the CH10.MDB database.
The adhResetDAPDataLink procedure does all the work for you. You send it three items:
- A reference to the data access page object within your application
- A string containing the path to the data source (or a server name, for SQL Server)
- A string containing the path to the HTM file that contains your data access page
Given these three bits of information, the procedure can open the HTM file, fix the data source, and save it back. The procedure has a few issues that we were not able to work around, however:
- The procedure must use the standard file system in order to open and modify the file. If you've stored your data access page on a Web server, this procedure won't be able to modify that file.
- The procedure reads the entire data access page into a single String variable. This type of variable is limited to storing around two billion characters. If your HTM file is larger than that, this procedure won't be able to help. (Of course, if your HTM file is larger than two billion characters, you've probably got lots of other problems!)
The procedures you'll find in this section use the AllDataAccessPages collection, a property of the CurrentProject object in Access. This collection contains all the saved data access pages, and each element of the collection is an AccessObject object.
The procedure can only change the location of the data source, not the type. That is, it won't be able to change the source from an Access database to SQL Server. Although this is feasible, it's a lot of work—you would need to replace the entire connection string, depending on the data source. Although it might be nice to be able to do this programmatically, we did not provide that functionality. Listing 10.2 contains the entire adhResetDAPDataLink procedure. Listings 10.3 and 10.4 contain the two subsidiary procedures required by adhResetDAPDataLink. FixPath (Listing 10.3) ensures that a file path ends with a trailing "\" (or "/", if the path is an HTTP path). GetFileName (Listing 10.4) takes in a full path and returns just the filename portion of the path.
Although you can find code similar in purpose to adhResetDAPLink in the Northwind sample database that comes with Access 2000, you'll find that the code there is less "reusable", and is, perhaps, less efficient than the code we've provided. We think you'll find it easier to use our procedure than to attempt to extricate the functionality you need from the Northwind sample database.
The use of data access pages, in general, will get very sticky if you include semicolons in your paths or data source names. Windows allows this, but data access pages do not. Although this isn't documented, to our knowledge, using a semicolon in a path or data source name will make it impossible for the data access page to link to its data source.
Public Sub adhResetDAPDataLink( _ dap As AccessObject, _ strDataSource As String, _ strPathToHTM As String) ' Reset the link between a data access page ' and its data source. Dim strContent As String Dim strName As String Dim intFileNumber As Integer Dim lngPosStart As Long Dim lngPosEnd As Long Dim lngPosCnnStrStart As Long Dim lngPosCnnStrEnd As Long Dim strExistingSource As String Dim strOut As String Dim strCnnStr As String Dim strCompare As String Const adhcSearch As String = "Data Source=" Const adhcConnectionStart As String = _ "<a:ConnectionString>" Const adhcConnectionEnd As String = _ "</a:ConnectionString>" Const adhcDash = "&#45;" Const adhcAmp = "&amp;" Const adhcQuot = "&quot;" Const QUOTE = """" On Error GoTo HandleErrors strName = FixPath(strPathToHTM) & _ GetFileName(dap.FullName) If Len(strName) > 0 Then intFileNumber = FreeFile Open strName For Input As #intFileNumber ' Look through the text file looking for ' the Connection String. Once found, change ' it to reflect the new database location and ' set it to an absolute path. strContent = Input(LOF(intFileNumber), _ #intFileNumber) ' Look for the connection string part. lngPosCnnStrStart = InStr(1, _ strContent, adhcConnectionStart) If lngPosCnnStrStart > 0 Then lngPosCnnStrStart = _ lngPosCnnStrStart + _ Len(adhcConnectionStart) lngPosCnnStrEnd = InStr(lngPosCnnStrStart, _ strContent, adhcConnectionEnd) If lngPosCnnStrEnd > 0 Then strCnnStr = Mid$(strContent, _ lngPosCnnStrStart, _ lngPosCnnStrEnd - lngPosCnnStrStart) End If End If ' Get rid of the nasty "&xxx;" characters. strCnnStr = Replace(strCnnStr, adhcDash, "-") strCnnStr = Replace(strCnnStr, adhcAmp, "&") strCnnStr = Replace(strCnnStr, adhcQuot, QUOTE) ' Search for the magic words, "Data Source=". lngPosStart = InStr(1, strCnnStr, adhcSearch) If lngPosStart > 0 Then ' If found, move past the magic words, ' and search for ";". lngPosStart = lngPosStart + Len(adhcSearch) lngPosEnd = InStr(lngPosStart, strCnnStr, ";") If lngPosEnd > 0 Then ' If you found a ";", then check to see ' if the data source matches the one passed ' in. If so, you're done, else replace it. strExistingSource = _ Trim$(Mid$(strCnnStr, lngPosStart, _ lngPosEnd - lngPosStart)) ' If the DAP contains quotes around the ' data source, add quotes to the comparison ' text, as well. If Left$(strExistingSource, 1) = QUOTE Then strCompare = QUOTE & _ strDataSource & QUOTE Else strCompare = strDataSource End If If StrComp(strExistingSource, _ strCompare, vbTextCompare) <> 0 Then ' No match. Replace with the ' supplied data source. ' Put quotes around the database name. ' This can't hurt, and Access will ' remove them if they're unnecessary. strCnnStr = _ Left$(strCnnStr, lngPosStart - 1) & _ QUOTE & strDataSource & QUOTE & _ Mid$(strCnnStr, lngPosEnd) ' Can't send back a string with bad ' characters in it. Put things back ' the way they were, being careful ' to replace "&" chars first. strCnnStr = Replace(strCnnStr, _ "&", adhcAmp) strCnnStr = Replace(strCnnStr, _ QUOTE, adhcQuot) strCnnStr = Replace(strCnnStr, _ "-", adhcDash) ' Now need to build back the entire ' string, replacing the ' ConnectionString piece in there. strOut = Left$( _ strContent, lngPosCnnStrStart - 1) & _ strCnnStr & _ Mid$(strContent, lngPosCnnStrEnd) ' Close the input file. Close #intFileNumber ' Get a new file number, open the file ' for output, and write the new HTML ' stream out. intFileNumber = FreeFile Open strName For Output _ As #intFileNumber Print #intFileNumber, strOut End If End If End If End If ExitHere: On Error Resume Next Close #intFileNumber Exit Sub HandleErrors: Select Case Err.Number Case Else Err.Raise Err.Number, _ Err.Source, Err.Description End Select Resume ExitHere End Sub
Private Function FixPath(strPath As String) As String ' Given a path (with no trailing file name), make ' sure the path ends with a trailing "\" or "/" ' (HTTP: requires "/", right?) That way, you can ' take the output of this function and tack on a ' file name without having to wonder if there's a ' separator or not. Dim strCh As String * 1 On Error GoTo HandleErrors If StrComp(Left$(strPath, 4), _ "HTTP", vbTextCompare) = 0 Then strCh = "/" Else strCh = "\" End If If Len(strPath) > 0 Then If Right$(strPath, 1) = strCh Then FixPath = strPath Else FixPath = strPath & strCh End If End If ExitHere: Exit Function HandleErrors: ' Not sure what errors could occur in here, besides ' catastrophic ones (out of memory, and so on), but ' it never hurts... Select Case Err.Number Case Else Err.Raise Err.Number, _ Err.Source, Err.Description End Select ' This line doesn't do anything, ' given the current Select Case block. Resume ExitHere End Function
Private Function GetFileName(strFullPath As String) _ As String ' Given a full path, retrieve just the file name. ' (How many times have we written this code?) ' This version works with HTTP:// paths, as well, ' because you may store DAPs on a web server. Dim strFileName As String Dim lngPos As Long On Error GoTo HandleErrors ' First, convert any "/" to "\". Then, find ' the final "\" and pull off everything after that. strFileName = Replace$(strFullPath, "/", "\") lngPos = InStrRev(strFileName, "\") If lngPos > 0 Then strFileName = Mid$(strFileName, lngPos + 1) End If GetFileName = strFileName ExitHere: Exit Function HandleErrors: ' Not sure what errors could occur in here, besides ' catastrophic ones (out of memory, and so on), but ' it never hurts... Select Case Err.Number Case Else Err.Raise Err.Number, _ Err.Source, Err.Description End Select ' This line doesn't do anything, ' given the current Select Case block. Resume ExitHere End Function
The adhResetDAPDataLink procedure uses brute force to fix the broken linkage. Once the code has opened the file, it reads all the contents of the file into a local variable and searches for the text it needs to find: "Data Source=". If it finds that text, it extracts the current data source from the text and compares it to the data source you've specified in procedure's parameters. If the two items match, there's nothing more to be done. If they don't match, the procedure replaces the old data source with the new one and then writes the text back out. It's not elegant, but it works.
To be honest, there's a bit more going on than the previous paragraph mentions. Because of the way data access pages store data source names internally, we found that ampersands (&), quotes, and double hyphens caused trouble. In each case, the data access page converted the character into a string like "&amp;" for an ampersand, or "&quot;" for a quote. Because adhResetDAPDataLink is looking for semicolons to delimit the data source name, you can imagine that this might cause some trouble. To work around it, the procedure replaces each offending combination with its original value, does all the work, and then replaces them back when it's done. There may be other characters and character combinations that we missed. If so, you'll need to modify the code (it should be obvious how to do it, given the examples we've provided). If you do find other "bad" characters, please let us know!
If, for example, your intent is to reset the data link within a data access page named "DAPDemo" so that it retrieves its data from the current project, you might write code like this (assuming that the HTM file is in the same path as the current project):
Call adhResetDAPDataLink( _ CurrentProject.AllDataAccessPages("DAPDemo"), _ CurrentProject.FullName, CurrentProject.Path)
You can use the AllDataAccessPages collection of the CurrentProject object (whether you're working in an MDB or an ADP file) to reference a particular data access page. CurrentProject.Name supplies the name of the current database, and CurrentProject.Path supplies just the path portion of the current database.
If you attempt to run adhResetDAPDataLink when the data access page is in use, you'll get error 70 (Permission Denied) if the code tries to write to the HTM file.
Handling the Path from the Database to the Data Access Page
If you create a database (or project) that contains data access pages and then move any of the corresponding HTM files to a new location, Access will no longer be able to locate those HTM files. (To try this out, change the name of the folder containing the sample database for this chapter. When you attempt to open a data access page in the new folder, Access will fail: it's looking for data access pages in the original folder, which no longer exists. You could also simply move the HTM files to a different folder and get the same results.) If you're deploying an Access application that includes data access pages, you'll want to make sure you include code that relinks the HTM files to the current database or project.
Managing data access pages is really the same issue as managing linked tables, and you may want to create a user interface to manage this. In this chapter, we've provided no user interface—instead, we've supplied procedures you might call in order to relink the pages.
Given the location of the HTM file, you would think it should be easy to simply set some property of the DataAccessPage object within your database or project. And so did we—we looked in vain to find some way to simply change a property linking the shortcut stored within the database to the actual HTM file on the disk. If you hover your mouse over a data access page in a database (see Figure 10.29), the tooltip contains the path: clearly, Access knows where this thing is stored. But reading this visually from a tooltip doesn't do your code any good.
Figure 10.29: Hover your mouse over a data access page, and you'll find the path.
A little digging turned up the FullName property for all the AccessObject-type objects. That is, all of the All… collections contain AccessObject objects, and each of these has a FullName property. For most objects, this property contains an empty string. For data access pages, however, this property contains the full path to the associated HTM file. Unfortunately, this property is read-only, and so we hit another brick wall.
The only known solution to this problem is (believe it or not) to delete the existing data access page from the database (not the associated HTM file from the hard drive, however), and then re-create a new one with the path you need. Yes, it's ugly, but it works. The code in adhResetDAPLink does this work for you. Given a reference to an existing data access page, and a path, the code resets the link to the HTM file. Of course, there are some issues to be considered:
- Because the adhResetDAPLink procedure does its work by deleting and then re-creating database objects, its use will lead to "database bloat." That is, as Access deletes and then creates objects, it doesn't necessarily reuse the space it has just freed up. This will, over time, cause your database to grow. Make sure you compact your database regularly if you use this code.
- The adhResetDAPLink procedure will cause some screen "flashing." Make sure you turn off screen updating somewhere upstream to the procedure (using DoCmd.Echo), so that you don't have to endure the unsightly screen flashing as the code creates and deletes objects from the database.
- If the data access page's data source can't be found, adhResetDAPLink will fail. Make sure you've verified the data source before you call this procedure to reset the links. In the next section, you'll see code that handles resetting both the data and the path—it must reset the data before it can work with the path information.
- In an MDE or ADE file, Access won't allow you to modify design-time properties of existing objects. If the code determines that it's been run from an MDE or ADE file, it gracefully fails and returns False. (See the ISMDE function in Listing 10.6.)
The adhResetDAPLink function, shown in Listing 10.5, follows these steps:
Tests to see if the current database/project is an MDE/ADE file. If so, the code sets the return value to be False and simply exits:
If IsMDE() Then adhResetDAPLink = False GoTo ExitHere End If
Gets the filename portion of the data access page's FullName property and tacks that onto the end of the supplied path, creating the new full path for the HTM file:
strFileName = GetFileName(dap.FullName) strFullPath = FixPath(strPath) & strFileName
Compares the new path to the data access page's FullName property—if they're the same, there's nothing to do, and the code sets the return value to be True and exits:
If StrComp(dap.FullName, _ strFullPath, vbTextCompare) = 0 Then ' There's nothing to do. The path already ' matches the path you're trying to set. ' Just get out now. adhResetDAPLink = True GoTo ExitHere End If
Stores away the name of the current data access page, so that it can reset it once it has deleted and re-created the shortcut:
strName = dap.Name
Deletes the original shortcut:
DoCmd.DeleteObject acDataAccessPage, strName
Creates the new data access page, but doesn't create a new HTM file to go with it. This code simply creates a new object and sets its internal shortcut to the real HTM file:
Call CreateDataAccessPage(strFullPath, _ CreateNewFile:=False)
Closes the new data access page and saves it with the saved name:
DoCmd.Close acDataAccessPage, strName, acSaveYes
Public Function adhResetDAPLink( _ dap As AccessObject, ByVal strPath As String) As Boolean ' Given the name for a DAP shortcut, and the ' full path of the actual HTM file, reset the DAP link. ' To do this, you must create a new DAP link (using ' the CreateDataAccessPage function), delete the ' original, and then rename the new one. Dim strName As String Dim strFileName As String Dim strFullPath As String Dim lngPos As Long Dim strOldName As String ' If this is an MDE, ain't no point in ' going on. In that case, simply return False ' and quit. If IsMDE() Then adhResetDAPLink = False GoTo ExitHere End If On Error GoTo HandleErrors ' Get the new HTM file name: strFileName = GetFileName(dap.FullName) strFullPath = FixPath(strPath) & strFileName If StrComp(dap.FullName, _ strFullPath, vbTextCompare) = 0 Then ' There's nothing to do. The path already ' matches the path you're trying to set. ' Just get out now. adhResetDAPLink = True GoTo ExitHere End If ' Store away the name of the DAP shortcut ' you want to end up with -- the same as the ' current DAP shortcut, which is about to ' get deleted. strName = dap.Name DoCmd.DeleteObject acDataAccessPage, strName Call CreateDataAccessPage(strFullPath, _ CreateNewFile:=False) DoCmd.Close acDataAccessPage, strName, acSaveYes adhResetDAPLink = True ExitHere: Exit Function HandleErrors: Select Case Err.Number Case Else Err.Raise Err.Number, _ Err.Source, Err.Description End Select ' This line doesn't do anything, ' given the current Select Case block. Resume ExitHere End Function
The adhResetDAPLink procedure uses a few techniques you may not have seen before:
- To create a new DataAccessPage, the code calls the CreateDataAccessPage method of the Application object. This method can either open an existing HTML page as a data access page (as this example did), or it can create a new data access page and the associated HTM file.
- To determine if the current database/project is an MDE or ADE file, the code calls the IsMDE function, shown in Listing 10.6. This function attempts to retrieve the "MDE" property of the current database or project. If this property contains the value "T" (not the Boolean value True—the character "T"), the current file is an MDE/ADE file. In that case, the adhResetDAPLink procedure cannot relink the data access page, and so it must exit, returning False. (This code also relies on the ProjectType property of the CurrentProject option. This allows the code to determine whether the code is running in a database or in a data project.)
Private Function IsMDE() As Boolean ' Is the current database/project an MDE/ADE file? Dim strValue As String On Error Resume Next ' The "MDE" property will contain "T" if this ' is an MDE or an ADE. If CurrentProject.ProjectType = acADP Then strValue = CurrentProject.Properties("MDE") Else strValue = CurrentDb.Properties("MDE") End If IsMDE = (strValue = "T") And (Err.Number = 0) Err.Clear End Function
Determining If a Link Is Broken
You may simply want to determine if a data access page's shortcut is broken or not, without attempting to create a new one. To make that possible, we've supplied the adhCheckLinkToDAP function, shown in Listing 10.7. This procedure simply attempts to use the Dir function to locate the file in the file system. If it succeeds, the file link is valid. If it fails and triggers a runtime error, either the link is invalid, or the file exists on a Web server. In that case, in the error handler, adhCheckLinkToDAP calls the adhCanOpenDAP function (shown in Listing 10.8), which attempts to physically open the data access page within Access. (This is a slow, ugly process, so you want to do anything possible to keep it from happening. If the HTM file is stored on a Web server, however, the Dir function can't find it and will trigger the runtime error that causes the code to load the page directly.) If the page opens successfully, adhCanOpenDAP returns True; otherwise, it returns False.
Public Function adhCheckLinkToDAP( _ dap As AccessObject) As Boolean ' Validate the file link for a specific DAP shortcut. On Error GoTo HandleErrors Dim strName As String strName = Dir(dap.FullName) ' Return success, if the file exists. adhCheckLinkToDAP = (Len(strName) > 0) ExitHere: Exit Function HandleErrors: adhCheckLinkToDAP = False Select Case Err.Number Case 52 ' Bad file name or number ' Perhaps the file name is http://? ' In that case, the simplest solution ' is to quietly try to open the thing, ' and see if that succeeds or not. This ' is MUCH slower than other techniques, ' so we reserve it for this case only. adhCheckLinkToDAP = adhCanOpenDAP(dap) Case Else ' What do we do? Return False? End Select Resume ExitHere End Function
Public Function adhCanOpenDAP( _ dap As AccessObject) As Boolean On Error Resume Next DoCmd.OpenDataAccessPage dap.Name adhCanOpenDAP = (Err.Number = 0) DoCmd.Close acDataAccessPage, dap.Name Err.Clear End Function
Fixing Both Issues in One Pass
If you need to create an application that uses data access pages deployed from an Access MDB or ADP file, you'll want to incorporate code that handles both of these issues. Providing a user interface to handle these issues is beyond the scope of this chapter, but we have provided a sample procedure, adhValidateDAPLinks (shown in Listing 10.9) that works through each of the data access pages in the application. This procedure first resets the data link and then, if the code can open the data access page, resets the file link to the HTM file as well.
The adhValidateDAPLinks procedure assumes that the HTM files are in the same folder as the MDB or ADP file. If that's not the case in your application, you'll need to modify this sample procedure.
Public Sub adhValidateDAPLinks() ' An example, showing how you might ' validate all DAP links. Dim lngDAPCount As Long Dim dap As AccessObject Dim i As Long On Error GoTo HandleErrors DoCmd.Echo False With CurrentProject.AllDataAccessPages lngDAPCount = .Count ' Must work backwards through the links, ' as adhResetDAPLink deletes and recreates ' DAPs. This messes Access' looping up ' seriously. For i = lngDAPCount - 1 To 0 Step -1 Set dap = .Item(i) Call adhResetDAPDataLink(dap, _ CurrentProject.FullName, CurrentProject.Path) If Not adhCheckLinkToDAP(dap) Then Call adhResetDAPLink(dap, _ CurrentProject.Path) End If Next i End With ExitHere: Set dap = Nothing Exit Sub HandleErrors: MsgBox "Error: " & Err.Description & _ " (" & Err.Number & ")" Resume ExitHere End Sub
ValidateDAPLinks works its way backwards through the collection of available data access pages. It must do this: because the procedure may delete and re-create objects, Access assigns each new object a new ordinal position within its collection. If you used a For Each Next loop to work through the collection, you would end up skipping over items. (Believe us, we tried it!) By working backwards through the items, the newly added items, at the end of the collection, don't conflict with the items that have yet to be touched. This is the same technique you might use any time you're modifying the contents of a collection within a loop—closing all open forms, for example.
What about Themes?
If your data access page has a theme associated with it, you'll find one more issue that you'll need to grapple with. When you add a theme to a page, Access creates a subfolder of the folder containing your page, with the same name as your page plus "_files". In that folder, you'll find the theme's style sheet (CSS) files, the image files (GIF or JPG) used by the style, and an XML file describing the various parts of the theme.
When you move the HTM file that contains your data access page's layout to a new location, you must remember to move the subfolder as well, and to maintain the relative position of the folders.
In this chapter, we've introduced the new data access page and its designer. There's no doubt that the designer needs work, but the pages you can create can be as complex and full-featured as you need. Deploying data access pages is simple: all you need is the data source, the HTM file (and perhaps some theme information) and Internet Explorer 5.0. Of course, because data access pages depend on technology that's part of Office, only Office users (or Office licensees) can legally use your pages.
This chapter focused on these particular issues:
- Introducing data access pages
- Why you might want to use data access pages
- Properties of the HTML controls used on data access pages
- Using the Sorting and Grouping properties
- Managing the two links used by data access pages—the link from the page to its data source and the link from the Access database or project to the page
Where do you go from here? If you're interested in scripting your pages (and you should be, if you intend to really use them in a production environment), you'll need to investigate Chapter 11, which deals with scripting in general, and working with data access pages in specific. If you want to use the Office Web Components, check out Chapter 12.
Take the time to work with data access pages. Don't be discouraged by the designer—save temporary copies of your pages, giving your page a new name as you save it each time, so you can back out any changes you make (the lack of Undo will, otherwise, drive you crazy). It will certainly improve in a later version. We think you'll find data access pages to be the simplest way to get Jet or SQL Server data up on the Web, and as long as you can limit your user base to Office 2000 users or licensees, data access page technology just might be an answer to your "I need to get this data onto the Web" needs.