wsh

Introduction

In the first tutorial on Data Access Pages, the evolution of Data Access Pages was described and a simple method of creating the page using the Wizard and hosting it on the intranet site was explained. The second tutorial dealt mainly with creating a Data Access Page from scratch starting from the design view of a page. This tutorial expands on the two previous ones and describes how parametric queries can be used as a source for the DAP. This is a popular FAQ on MS Access forums related Data Access Pages. This tutorial describes in detail how to create a DAP based on a parametric query, and host it on the intranet website.

Parametric queries, as the name suggests, are queries that return a row of data from a table based on a supplied parameter. Let's assume that you are dealing with customer information such as that shown in the picture below. For each customer there are several pieces of information such as the Company Name, Contact Name, Address, etc. By using a parametric query and supplying a previously chosen parameter such as CustomerID, one can retrieve the rest of the information about that customer.

Parametric queries need not be restricted to one parameter; they could also depend on a combination of more than one parameter that retrieves the desired row or rows (since there could be more than one row satisfying the parameters). For example, the parameters could include finding all rows in a table satisfying a beginning date and an ending date.

Creating a parametric query

 What the parametric query accomplishes

Let's begin by creating a parametric query. Let's also use the table shown above. What we want to accomplish is this: when the query is run, the program asks for the CustomerID we want to look up. When we supply the CustomerID as an input, the query returns all of the columns (or some chosen columns) for that particular row. For example, if you supply the CustomerID as 'ALFKI', you should retrieve all the information for this particular customer, such as Customer Name, Contact Name, Contact Title, etc.

There are many ways of designing the queries, as you can learn from reading other articles on this site. Click on the tab New when you have highlighted the Queries object. This opens the interface for creating the query, and offers a number of options for creating the new query.

Choose Design View and click OK. This opens up the next window. If you choose Customers from the list of tables, the table related list is added to the design pane of the query designer as shown. After choosing the table (or tables, or queries and tables as the case may be), close the Show Table window.

Now create a Select query by simply choosing the columns you want included in your selection. If you click on the drop-down arrow next to Field : it brings up a drop-down showing all the columns in the table.  You can choose the columns you want, in the order you want as shown. You can achieve the same result by just double clicking the column in the list on the design pane and the column is added to the list from left to right.  This query and its modifications are saved as Qparam.

Turn a Select query into a Parametric query

Once you have the Select query it is very simple to turn it into a parametric query. All you need to do is shown in the next picture. Since we want to search the records for the Customer of our choice, the CustomerID becomes the parameter, and it is the one that is used in the criteria for the search. Enter [CustomerID: ] exactly as shown. After each change to a query you will be asked to save the modification, and you should not forget this to incrementally modify your query.

Row returned by the parametric query

When you double click the Query, Qparam in this case, the program pops up a little form asking for your input as shown. The form also shows that it is asking for a particular parameter, CustomerID:

For example, if you type in 'ALFKI' and click OK, the query returns all the selected column values in your select query as shown here.

The SQL behind this Query

Behind the scenes, the query runs an SQL command against the table. This can be reviewed by right clicking an empty area in the design pane and choosing the SQL View. This brings up the following window, which shows the SQL statement. You could make changes to this statement, but once you save this after modifying, you cannot access the design view.

Turning this into a Data Access Page

In the Access interface, highlight the Pages and click on New to create a New Data Access Page, which brings up the following screen. Here you choose to use the Page Wizard as shown. Also click on the drop down that lists all the tables and queries in your application from which you choose the query Qparam as shown. This picture is a montage of several screens.

When you click OK to this you will get the next screen of PageWizard as shown.  You follow it up to the end, where you will be asked to save the page to a particular location. If you have reviewed the previous tutorials you would know that they will be saved by default to the C:\My Documents folder with an .htm extension. It will also be saved in the Pages folder in your application, herein called Pdap.htm.

Page display in the browser

Open the page in IE6.0 (here IE6.0 is available and therefore used) from its location in C:\Documents and Settings\computer user\Desktop\Nov7\DAP3\PDAP.htm. The display of this page in the browser is as shown in this picture. An Enter Parameters window pops up with just one parameter, CustomerID. There is a text box where you can enter the value of the parameter.

Now enter a CustomerID, say, 'DUMON' (I remember this  because I have seen the table) for the parameter value column which is showing a text box and click OK. The page will display as shown below (I have expanded the Company Name node for this picture).

Hosting the page on the intranet

Upload the PDAP.htm file to the intranet's root folder, or copy this page and paste it in the root folder. It will be available for the intranet users. The next display shows the same page as seen at the URL, http://localhost/PDAP.htm. However you get two warnings, as shown in the next two pictures. 

If you click OK to accept to display, you get the final page displayed, which pops up the same Enter Parameter value dialogue. If you enter the CustomerID value, you get the page displayed.

Summary

If the parametric query is working correctly, turning it into a Data Access Page is quite simple using the PageWizard.  You may experience some problems with hosting it on the intranet, mostly due to permission- and data provider-related  problems. The values for the parameters you enter are not case sensitive.  The web pages created in this tutorial are functional, but you may have to work on them to make them aesthetically pleasing. However, Data Access Pages are RAD at its best.