Scripting a Data Access Page: a Simple Example

Introduction

In the previous tutorial on data access page scripting, an overview of the Microsoft Script Editor in the MS Access environment was described. This second article shows how you may use the script editor to carry out modifications to a Data Access Page using the JavaScript language (ECMA).

This tutorial shows you step-by-step how you may script the page so as to make context sensitive changes to the heading of the page, and apply some simple filtering to the data. Some experience with JavaScript/VbScript is assumed, although Data Access Pages use ECMA (same as Javascript, but with syntax according to ECMA standards) for its scripting. Again, this article will not be using Microsoft Office Web components.

This tutorial is self-contained in order that you may concentrate on the tutorial without it being necessary for you to follow hyperlinks. However, it will be very helpful if you would review the other articles on Data Access Pages on the ASPFree.com site.

For a data access page, everything about the page is stored in an HTML file, instead of an mdb file. Let's begin by creating a new data access page. Open the Access program and click on  File-->New. In the New File navigation window on the right, click on Blank Data Access Page. Click on New Source...  This opens the Select Data Source dialog as shown below. There are a number of sources already present, created for other programs on this machine. Click on the New Source... button.

This opens the Data Connection Wizard with the Welcome screen. Choose Other/advanced by highlighting and clicking on the Next> button.

 This opens up the Data Link Properties window with Microsoft Jet 4.0 OLE DB Provider as shown.

Click on the Next> button.  In the connection tab, use the ellipsis button and browse to the Northwind database. On this machine it is at: C:Program FilesMicrosoft OfficeOFFICE11 SAMPLES Northwind.mdb  You may test the connection and this should say that you succeeded. Accept the default username Admin and you may also place a check mark for the Blank password checkbox as shown. Click OK.

In the screen that pops up as shown in the next picture, you need to select a database and table from the dialog Select Database and Table. However for the mdb file, all that you will be choosing will be the whole database.

Click on the button Next> in the above screen. At this point you will be required to save the data connection and finish the wizard. You need to make suitable entries: give a filename, and add some description and keyword(s) as shown in the next picture. Keywords help if someone wants to search the pages.

Now click on the button Finish. You will finally save this file as an ScriptExple.odc file.

Click on the button labeled Open in the above window now. This opens up the data access page in design view as shown. You are given a hint to add fields by dragging fields from field list as shown.

Go to View in the main menu, and from the drop-down click on FieldList to reveal the tables/fields as shown here.

Highlight Company Name, Contact Name, City and Address holding down the Control key as shown. Drag and drop the group onto the design pane as shown in the next picture. You will be asked to choose a layout style by the Layout Wizard. Accept the default and click OK

Work on your controls by adjusting widths and alignments so that they show clearly, as shown in this picture.

Add a label to the area above the dragged elements as shown. Right click the label, go to the other tab and change its innerText from Label0 to Test as shown.

This changes the label caption on the design page to "Test" as shown in the next picture.

Now click on View-->Page View from the menu item to see how it appears when it is displayed on the web browser.

We will add script to change the heading 'Test' to something different. We will change this heading to something that will depend on one of the columns in the database.  Review the next picture; it shows the result of a query which returns the values used in the DAP (this page).  There are various cities, including 'London.'  We will change the heading's text depending on the 'City' that is in view.

What will the script accomplish?

We will be creating a script to change the Label to 'Welcome to Heathrow' if the city is 'London,' or 'Welcome to World' for any other city at the click of a button. Instead of the click event of the button you may as well use any other multitude of events the DHTML allows.

Now in the design view add a CommandButton as shown here. Change its Element Properties, innerText from Command1 (default) to 'London?' as shown.

Now open up the Microsoft Script editor to see the document outline as shown. You can see the Command1 object in the Client Objects and Events folder.

On an empty area of the page, right click to bring up the Document Property Pages as shown and change the default scripting language so that the client's default language is JavaScript (ECMAScript) as shown here. The default is VbScript.

In an empty area of the page right click, and from the drop-down menu choose Insert Script Block -->Client as shown.  Please refresh your memory about the various windows in the script editor.

This will allow you to write JavaScript/ECMA for the client. Now double click the Command1 button's click event in the Document window shown earlier. This adds the script shown in this picture.

Now to the function of the command1 click add the following code.  

function Command1_onclick() {
alert("Will be formatting");
alert(document.getElementById("HeadingText").outerHTML);
var hd=document.getElementById("HeadingText");
hd.style.color="magenta";
hd.innerHTML="<h2>Welcome</h2>"
//variable hd refers to the document's heading var 

cty=document.getElementById("City_Label");
//similarly cty refers to the City_Label[different from City] 

cty.style.backgroundColor="lime";
var ctytxt=document.getElementById("City");
alert(ctytxt.innerHTML);
//
if(ctytxt.innerHTML=="London")
{ 
hd.innerHTML="Welcome to Heathrow";
}
else{
hd.innerHTML="Hello world!";
}
//
var cn=document.getElementById("CompanyName_Label");
var con=document.getElementById("ContactName_Label");
cn.style.backgroundColor="lime";
con.style.backgroundColor="lime";
} 

Saving and Displaying the Data Access Page

Saving the file

You need to save the page before displaying on a browser. Now from the file menu you can choose to save, and it will normally get saved to the default directory, C:My Documents.  If you want it saved to the web server, you may place it on the web server by saving it to the C:Inetpub/wwwroot, or to a virtual directory of your choice.

You may however get the following message. Presently  the data source is referenced by its absolute path. If you want to connect to  it through the network, you may have to give the UNC path (ServerSharefile) name. Just click OK and proceed. It gets saved to the folder you chose. In the present tutorial it is saved to the virtual root of the IIS Server.

Displaying the file and testing the script

Since the data access page is on the website, it can be displayed by browsing the file with the IE. At first you will get this warning message shown in this window.

When you click OK to the above you will get another warning about access as shown in the next picture.

When you click OK to the above also, then you will see your data access page as shown here.

It opens with the first record as shown. You can move to the other records by using the navigation buttons provided. We added the script to Command1 button with the caption 'London?'. Since we are on a record whose city field shows 'London' we can click the button. You may see a couple of message boxes if you have not commented out the MsgBox statements in the code, but you will finally see the page shown in the next picture.

Now you may move out of Customers 1 and move to some other record where the city is not 'London,' such as the one shown in the next picture, and click on the command button. You will see the following window, which shows that the script is indeed working.

Summary

There are two requirements for successful scripting of a data access page, understanding the Microsoft Script Editor and the JavaScript object model. ECMA syntax is used in the code, which facilitates a standards-based approach to scripting. The script on this page is very simple, but one look at the Client Objects available for scripting shows an enormous potential for intricate scripting.