Data Access Pages and Access XP | Beginning Access 2002 VBA (Programmer to Programmer)

Although not specifically a VBA topic, publishing data to the web is one of the most common database/Internet- related activities, and so deserves a mention. If you want to publish data to the Internet then you need to consider one important point how dynamic is your data, that is, does it change frequently or is it static? For example, a staff contact listing which may not change a great deal over time as opposed to daily sales figures which will obviously change frequently. The publishing approach taken will depend on how dynamic your data is.

If your data is static, that is, does not change, then you could use plain HTML files and then update these on a regular basis, say once a month. If you need the very latest data, then there are several options available to you from within Microsoft Access: you can use ASP files to show the latest data every time the web page is viewed , or you can use Data Access Pages (DAPs), which show a live view of the data. Both HTML and ASP files give the advantage that any browser can view the page, whereas Data Pages only work in Internet Explorer 5 or above. Data Pages are restrictive to say the least they are best used in an intranet, and only then if you can guarantee the browser being used will be Internet Explorer. However, when working in an intranet environment where you know that your users will be using Internet Explorer 5 and above, DAPs provide you with a useful tool to distribute data to the users. As you will see later, DAPs also provide you with a virtual codeless solution, that is, they are created using the Access interface. However, in order to create greater functionality you will need to learn VBScript at some point.

Important

An intranet can be viewed as an internal Internet with access provided to those within a company or other organization. Increasingly we are seeing inter-company intranets between a large organizations and, for example, their suppliers where common information can be shared privately.

In our view, unless you are tied into a completely Microsoft solution, DAPs are of little benefit. Sometimes you will know in advance which browser the user will view your site with. It is also possible to use browser detection software such as BrowserHawk (available from http://www.cyscape.com/products/) to detect the browser the user is entering a site with, so you can then redirect them to another set of pages more appropriate for that browser. This, of course, is an option, but you need to be prepared to maintain and manage two or three different sets of web pages.

Important

Do not confuse Data Access Pages (DAPs) with Access Data Pages (ADPs). Access Data Pages are a specific file type ( .adp ) used to interface with SQL Server databases as opposed to Data Access Pages which are used to interface with a web browser.

DAP Connection Files

A Data Access page is peculiar to Microsoft Access and is not available for use with any other database. As we have said, it is also a total Microsoft Solution requiring the full range of Microsoft software from the server to the client. DAPs can also be used when working with SQL Server and SQL Server Desktop via Microsoft Access Data Project files. At a high level, DAPs contain HTML and ActiveX controls. The ActiveX controls allow you to bind to Microsoft Jet, SQL Server, and SQL Server Desktop data stores. One of the more important of the controls is the Data Access Control, which interacts with Internet Explorer, permitting you to actually display the data. This is why it is impossible to get a DAP to work with, for example, Netscape Navigator. The interaction with the browser is just not there.

The following diagram provides an overview of how a DAP works:

Before creating our Data Access Pages it is useful to know how the actual connection information is stored. This is the ADO connection string that will be used on the web to get at the actual data. (ADO Connection strings are discussed in Chapter 21). Microsoft Access provides you with a database option which makes it very simple to edit or create the string. This option will create a particular file type known as an Office Data Connection or .odc file. The ODC file can be opened and edited in a text editor, which makes it very easy to change the connection string if required. You can also view the contents of the ODC file by opening it using Microsoft Internet Explorer. To create a database connection file which points to an Access Database, open a database file and using the main menu select Tools Options . In the Options dialog, click on the Pages tab. From here it is simply a matter of following the wizard:

Check the Use Default Connection File box.
Click on the Browse button.
Click on the New Source button.
Select Other/Advanced, and click Next >> .
Select Microsoft Jet 4 OLEDB Provider.
Click Next >> .
Use the button beside the Select or enter a database name to navigate to the required database.
The User name will be defaulted to Admin with a blank password. If you are using Access security you may change this by removing the check marks in the appropriate box.
Click the Test Connection to ensure all is well.
Click OK.
In the Choose Data dialog simply click Next > . This option is available when working with SQL Server, and you have the opportunity of selecting from a number of databases on the server. It is disabled when working with Jet.
The final dialog allows you to enter a File Name for the odc file and a textual Description about the connection. Click Finish to close the dialog and save the connection file.

If at some point you need to change the connection information, it is simply a matter of opening the ODC file in Notepad and manually changing the database location section. A section of which is highlighted in the following figure. Note that much of the connection information is not visible in this example.

What's New in Access 2002 DAPs

In Access 2000, DAPs were viewed as a version one technology, and, in some cases, by Access Developers as marketing fluff. With Access 2002, Microsoft brings several new features to DAPs in an effort to increase their uptake, including:

An Office 2000 or 2002 license is now no longer required. Users, however, still require the Office web controls to view and interact with a DAP. This, though, does not present great problems. When installing Office XP, the web controls are installed as part of the setup. A user without the controls will be prompted to download the files from the Microsoft web site. See http://office.microsoft.com/downloads/2002/owc10.aspx .
Multiple levels of undo.
Ability to save existing forms and reports as Data Access Pages.
Improved DAP designer.
Ability to select multiple fields from the table selector.
New Autosum feature when working with aggregate values.

What are the Office Web Components ?

The web components are assets of COM objects used to place spreadsheets and pivot tables on the Internet. Using the controls, a user can use this functionality without having Microsoft Office XP installed. Make sure you have at least version 2.5 of the Microsoft Data Access Components on your machine before the install.

New DAP Events

In additional to the above there are also several new events available to you in Access 2002.

AfterDeleteFires after a record is deleted or the deletion is cancelled. This event is commonly used to show a message to the user that the deletion was successful.
AfterInsertFires after a new record is added. In this case, you may want to "grab" the username of the individual who entered the record or simply update an audit table with the date and time the insert took place.
BeforeDeleteFires before a record is deleted or the deletion is cancelled. You may want the user to confirm they actually want to delete a record before continuing. This event would permit you to send a message to the user requesting that they confirm the deletion before actually deleting the record.
BeforeInsertFires when you type the first character into a control, but before the record is actually inserted into the database. Commonly used as a validation trigger.
DirtyFires when the record changes, but before the BeforeUpdate event. It can be used to trigger code when data or text items in a combo box change. For example, enabling or disabling controls on a page based on data entered by the user.
FocusFires when a DAP section receives the focus. For example, when moving from one record to another the focus event could be used to change the background color .
RecordExitFires when all AfterUpdate events fire, but before the record becomes non-current. Can be used to ensure that any necessary validation has been carried out before you close a form. Commonly used when working with Master Detail pages, when you need to work with an embedded sub form in the DAP. For example, a customer record can be displayed on the top half of the form, and the multiple orders can be displayed on the bottom half of the form. A sub form is normally used to display the many records.

A Master Detail page is commonly used to display one record and its associated many records.

The best way to understand Data Access Pages is to actually create one.

Try It OutCreating a Simple Data Access Page

Remember, to view Data Access Pages you require at least Internet Explorer 5.

In an open database, select the Pages in the Objects bar.
Select Create data access page by using wizard you can double-click to start it.
Select tblIceCream as the table, selecting all fields. You don't need to add any grouping or sorting, so just keep clicking Next and finally Finish . After the wizard has finished you'll see your Data Access Page in design view:

Notice how much this looks like the standard form and report designer, but in fact it's an HTML page, with embedded ActiveX objects. The DAP designer contains some features that would be very useful in standard Access forms design. For example, selecting View Field List from the main menu opens a drag and drop interface to your database object, tables, queries, and views. One additional trick it has is that it can "read" existing relationships between tables and allow you to create relationships on the fly. Adding a field is a simple matter of dragging and dropping the field from the list to the DAP.

Finally save this page as tblIceCream and view it by right-clicking on the page and selecting Web Page Preview :

Important

When you save the DAP it is not saved within your MDB file. The first time you create a DAP you will be prompted for a folder in which to save this and any further pages you create. DAPs are not actually part of your MDB file and are therefore stored outside the MDB file in their own folder. Remember a DAP is actually a HTML page containing ActiveX objects, not an Access object. There are no particular conventions when saving DAPs but it is handy to save all the files in a sub folder of your main project folder. That way you know where they are when you need to find them.

This looks very much like a normal Access form. It shows a single record at a time, and the navigation bar works just like the Access form equivalent. As you can see, creating a basic DAP is a fairly simple process. It is also possible to export a report as a Data Access Page.

Try it OutSaving a Report as a Data Access Page

This time we will take an existing report which shows the ice creams and the ingredients required for production. The report is available in the Chapter 16 example database. In the Database window, select Reports .

Select the rptIceCreamIng report
Either right-click and select Save As or chose the same from the File menu
Enter a name for the new object
Select Data Access Page from the dialog drop-down list
Click OK to proceed
Using the New Data Access Page dialog accept the default folder or navigate to the required folder and click OK

Microsoft Access will generate the DAP based on the selected report.

Notice within the DAP the format of the report has been changed. Rather than a static Access Master Detail report when all the data is presented to you at once, the new DAP file is actually interactive. In order to see the ingredients for each ice cream, click on the plus ( + ) symbol to expand the data tree. This process gives you a dynamic version of a standard Access Master Detail form which is viewable via the Web. The DAP will always contain the latest version of your data, and all your users need do is refresh the page within the browser to view the latest information.

Important

In order to complete the DAP, you will have to go into design view and amend some items, for example, you can change the default title from tblIceCream to something more meaningful. Textboxes also need to be resized, in particular, you may need to expand some of them in order to display the full ingredients and remove the default line wrap put in by the wizards.

When your data is not dynamic, but there is a requirement to display it via the Web, then one simple approach is to save it as a static HTML page. For multiple page sets, Microsoft Access will add the required page navigation links for you.

Try It OutCreating HTML Files

In the database window, select tblIceCream .
From the File menu, select Export .
From the Export dialog, change the Save as type to HTML Documents (*.html; *.htm) .
Press the Export button to create the HTML file.
The new HTML file isn't automatically displayed, so switch to Windows Explorer and find the file, then double-click it to launch Internet Explorer.

You now have a static HTML file, which will not change if the data changes. In addition the file, unlike a Data Access Page is viewable by any browser. However, once the data changes, you must then republish the HTML page in order to let users see the latest data. However, for data that does not change often, saving as HTML offers you an easy route to the Internet. Unlike ASP, standard HTML requires no execution on the web server as it is simply text and no translation process is required. Another important feature of static HTML is that there are no database connections to maintain. For completeness, we will show a fragment of the HTML produced from the above example.

 <HTML DIR=LTR> <HEAD> <META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=Windows-1252"> <TITLE>tblIceCream</TITLE> </HEAD> <BODY> <TABLE DIR=LTR BORDER> <CAPTION>tblIceCream</CAPTION> <TR> <TD DIR=LTR ALIGN=RIGHT>2</TD> <TD DIR=LTR ALIGN=LEFT>Walnut Wonder</TD> <TD DIR=LTR ALIGN=LEFT>Wondering how many walnut pieces can be packed into an ice cream.  In which case you've got to try this - it must be a record.</TD> <TD DIR=LTR ALIGN=RIGHT>0.00</TD> <TD></TD> </TR> <TR> <TD DIR=LTR ALIGN=RIGHT>3</TD> <TD DIR=LTR ALIGN=LEFT>Strawberry Surprise</TD> <TD DIR=LTR ALIGN=LEFT>Lazy summer days by the river.  Stawberries and cream, captured into a rich ice cream, heaped with real strawberries.</TD> <TD DIR=LTR ALIGN=RIGHT>0.00</TD> <TD></TD> </TR> <TR> <TD DIR=LTR ALIGN=RIGHT>4</TD> <TD DIR=LTR ALIGN=LEFT>Admirable Apricot</TD> <TD DIR=LTR ALIGN=LEFT>Sharp and refreshing.  One for those hot summer days.</TD> <TD></TD> <TD></TD> </TR> <TR> <TD DIR=LTR ALIGN=RIGHT>5</TD> <TD DIR=LTR ALIGN=LEFT>Blushing Blueberry Berg</TD> <TD DIR=LTR ALIGN=LEFT>A tangy taste of fresh Blueberries, buried deep beneath rich vanilla ice cream.</TD> <TD></TD> <TD></TD> </TR> <TR>

The HTML tags used here are standard tags used to create a HTML table. The only additional tag which may appear odd is the DIR=LIT . This tag simply states that the language direction is Left to Right. The following HTML tags are used to create a table:

TABLE the beginning of a HTML table structure
< TR > the beginning of a Row
< TD > the beginning of a HTML table cell
< /TR > the end of a table row
< /TD > the end of an individual cell
< /TABLE > the closing tag ending the table construct