Using Data Source Objects To Share Office Databases On The Web

You can download this article's sample files from our Web site as part of the file sept98.zip. Visit www.zdjournals.com/adj, then click the Source Code hyperlink.

Many Office users and developers want to be able to share Access databases over the Web. In a previous article about the Publish To The Web wizard, we described server-side techniques for performing this task (see "Dynamic Publishing Over the Web" in the February 1998 issue of Microsoft Office Developer's Journal). This month, we'll focus on a newer technique: optimizing the data source object (DSO) to work on the browser side with Microsoft Internet Explorer (IE) 4. DSOs work intimately and uniquely with this browser. In exchange for this restriction, Microsoft gives the user power, flexibility, and ease of use. Sharing Access tables and queries via DSOs is particularly appropriate for intranets, where it's relatively easy to dictate the type of browser to use. This technique also has merit for Internet sites that detect visitors' browser types and route them to pages optimized for two or more kinds of browsers.

In this article, we'll explain what DSOs are and how you can use them to share Access- and other Office-based databases. We'll describe special extensions for displaying your downloaded database on a Web page. And, we'll present three samples that demonstrate using DSOs to populate tables on Web pages.

An overview of data source objects A DSO sits on a Web page and accepts a copy of a data set from a Web server. You can control where and how data appear on a page by using Dynamic HTML (DHTML) and ActiveX Data Objects (ADO). These tools let surfers navigate and manipulate the local data cache in the DSO. The main purpose of a DSO is to expose a local copy of data to scripts through an object model, ADO. You'll want to learn the object model for reasons other than manipulating a DSO--ADO is Microsoft's new data access technology, and it will eventually make DAO (including ODBCDirect) obsolete.

Tip: More about DSOs

The Internet Client SDK found at http://msdn.microsoft.com/developer/sdk/inetsdk/help/dhtml/dhtml.htm is an excellent resource for learning about DSOs and DHTML. Microsoft supplies several DSOs with the IE4 browser, and you'll find others at the gallery Web site: www.microsoft.com/gallery/samples/default.asp. Organizations with a commitment to exploiting data access through IE4 can even develop their own custom DSOs through the OLE DB Simple Provider or OLE DB APIs.

DSOs offer varying kinds of functionality. The Tabular Data Control (TDC) lets you sort and filter a local data set. The JDBC Java applet lets you update a local copy. The Remote Data Service (RDS) even lets you update a remote database from the local copy of the data in its data cache.

The TDC is an exceptionally easy-to-use DSO that provides access to a delimited text file residing on a Web server. Microsoft implements TDC as an ActiveX control. Access 97 can create such a file with its Text Export wizard, and Excel 97 and Word 97 also support saving tabular data in this format--a format widely available in prior versions of most Office packages. Since users link to a copy of a database extract, you can precisely control what data is available them. In addition, there's no chance to inadvertently update the original database.

The JDBC applet doesn't ship with IE4, but you can obtain it from the DSO gallery. (We've also uploaded it to our FTP site with the other sample files.) This sample DSO interfaces directly with an ODBC-compliant data source. Because it goes directly to the data source, users always get a current copy of the data when they link to it--you don't have to write an intermediate copy of the data source. Since JDBC is a Java applet, it will run on machines that don't accept ActiveX controls.

The RDS permits two-way access to remote data sources via the Web. Users can browse a local copy of a remote data source and update a remote data source from a local copy. Like the TDC, the RDS is an ActiveX control. However, the RDS requires both browser-side and server-side components for proper operation.

Other DSOs either ship with IE4 or are available free of charge from the DSO gallery. The MSHTML DSO can read data into one Web page from another one. You can use the File | Save As HTML command from any of several Office packages to write out a file that, with some editing, could serve as input for this DSO. Other DSOs read XML data, display a calendar, compute an amortization table, manipulate a tabular array, and so on. HTML extensions and elements Four HTML extensions permit you to bind the fields in a DSO to HTML elements. These elements are HTML tags, such as the table tag or the select box. The binding process is like hanging data from the data source on an HTML element. Whatever data field hangs on the element shows from that tag on a Web page. Each tag typically exposes a unique data set field from the DSO. You can use data binding for a single value or for a whole table at a time. A text box, for example, displays the value of a single field in a single record. If a DSO implements the functionality, you can use ADO methods to navigate forward and backward through a data set to expose different records. DHTML supports single-valued data binding for many other HTML tags, including anchors, buttons, images, marquees, and the input collection of controls, such as text box, radio button, checkbox, label, password, and hidden.

Tabular HTML elements can display a whole data set or segments of rows in a single view. This style of binding uses a single HTML row as a template for any subset of rows in a table.

The two core HTML extension attributes are datasrc and datafld. The datasrc attribute points at a specific DSO, while the datafld attribute references a particular field or column within the local data cache in a DSO. When you're using single-value binding, individual tags must contain both attributes. If you're working with an HTML table, child elements of the table tag inherit the datasrc attribute for the table. These child elements will often be span and div tags within td tags for individual cells. The datafld attribute applies to individual span or div blocks within an HTML table.

The datapagesize attribute sets the number of records available for viewing at one time. If you don't specify this attribute, the whole data set is available. When you set the attribute, a table will display something less than the whole table. Use the DSO's nextPage and previousPage methods to let surfers scroll through the underlying table.

You'll use the dataformatas attribute to specify how data will display in an element. This attribute, which lets you embed HTML in a database that determines how text will appear on a page, pertains to the div, span, button, marquee, label, body, and button input types. A simple repeated table The rich diversity of data binding will become apparent as we contrast three basic ways to present an Access table over a web. These examples are simple, but they illustrate important basics from which you can move on to more advanced topics. Figure A shows a table that reveals some data from the Northwind database.

Figure A: We published this extract from the Access Northwind Employees table with a TDC data source object.

Because this sample uses the TDC, it doesn't require a DSN (a DSN is required with asp and idc/htx files). Recall that the TDC works from a delimited text file copy of a datasheet. You can create this file using either the Export Text wizard in Access or the File | Save As command in Excel with a CSV file type.

To demonstrate the flexibility of the technique, we copied the Employees table from the Northwind database to Excel. Then, we saved it with a CSV extension. This text file sits on the server, and each link to the page downloads a separate copy to a surfer's browser.

Figure B shows an excerpt from the file that sits on the server.

Figure B: This excerpt comes from the comma-delimited text file used to the support the report from the TDC.

Notice that the file's contents are comma delimited without quotes. The TDC can accommodate text files with various string-field delimiters, including no quotes, single quotes, double quotes, or just about any other special character.

We edited the row of column headings in two ways. First, we removed internal spaces in column names, since these spaces can cause the TDC to fail to interpret a field. Second, we assigned data types to the column header row. The default data type is string, but you can also specify integer, float, date, and Boolean. The excerpt in Figure B designates the EmployeeID field as integer.

Listing A shows the DHTML code that supports the display in Figure A. This code contains three main sections. In the first, the object tag instantiates the TDC. You'll always need classid, DataURL, and id settings to use a TDC data source object. IE4 ships with the TDC, but you need to invoke a copy for the current web page. While the classid setting is long, you can copy it from any convenient source, such as the Internet Client SDK. The DataURL designates the name and path to the delimited text file containing the data for your DSO. You need the id setting to bind HTML page elements to the data in the DSO.

Listing A: DHTML code for creating an extract from the Northwind Employees tables using TDC

<html><body>

<!-- Instantiate TDC -->

<object id=tdcEmployees 
classid="clsid:333C7BC4-460F-11D0-BC04-0080C7055A83">
<param name="DataURL" value="employees.csv">  
<param name="UseHeader" value="True">
</object>

<!--
* See datasrc reference to TDC 
* Also see style block text alignment
-->

<table datasrc=#tdcEmployees border cellspacing=2
cellpadding=7 width="50%">
<caption><b><i><font size=6>
	Employee Phone Directory
</b></i></font></caption>
<thead><tr STYLE="font-weight:bold">
<td width="40%" valign="TOP">
<b><i><font SIZE=5><P>First 
	Name</b></i></font></td>
<td width="40%" valign="TOP">
<b><i><font SIZE=5><P>Last
	 Name</b></i></font></td>
<td width="20%" valign="TOP">
<b><i><font SIZE=5><P 
	style="text-align:right">
Phone Ext.</b></i></font></td>
</tr></thead>

<!--
* See datafld references to fields 
* Also see align setting for last div block
-->

<tbody>
<tr>
<td width="40%" valign="TOP">
<b><font size=4><div datafld=
	"FirstName">
</div></b></font></td>
<td width="40%" valign="TOP">
<b><font size=4><div datafld=
	"LastName">
</div></b></font></td>
<td width="20%" valign="TOP">
<b><font size=4><div 
	align="RIGHT" datafld="Extension">
</div></b></font></td>
</tr>
</tbody></table>

</body></html>

The third code section is the template row that specifies a standard format for all rows in the table. You need to designate a specific field for each column in the table, and the datafld attribute enables this specification. However, since the td cell tag doesn't have this attribute, you embed a div tag in each cell. This tag takes a datafld attribute. The code also uses the div tag's align attribute to right-justify the telephone extensions in the third column. Paging through a table DSOs let you view a datasheet interactively over the Web. Because data binding is browser-based, you can deliver this functionality without requiring users to wait for a round-trip visit to a Web server. Figure C shows a variation on the datasheet from Figure A.

Figure C: Surfers can scroll to view pages of records from the Northwind Employees table.

This example lets users browse the underlying data three records at a time. In essence, the three records serve as a page size for viewing data. Two buttons below the table allow a user to scroll forward and backward one page at a time. Data binding lets you easily set the number of records that define a page as well as implement scrolling. This feature is particularly desirable when you're working with tables that have more records than can fit on a screen.

Just four changes to the code in Listing A transform it from a static table to a table that surfers can browse a page at a time. One of these lines merely displays the text Navigate Pages above the two buttons. The table tag is distinct, but it simply includes a datapagesize attribute setting. The new version of the table tag is as follows:

<table id="tblPhones" datasrc=#tdcEmployees 
datapagesize=3 border cellspacing=2
cellpadding=7 width="50%">

<button onclick=
"tblPhones.previousPage()">&lt;
</button>
<button style="position:absolute; left:105" 
onclick="tblPhones.nextPage()">&gt;
</button>

Note: Get to know JScript

The onclick setting is written in JScript syntax, the default scripting language for the IE4 browser. If you do any DHTML coding, you'll probably find at least a minimal knowledge of JScript convenient, since so many samples at the Internet Client SDK site are in JScript.

In order to set off the Navigate Pages text and the two buttons from the table, we embedded them in a paragraph tag. This is a standard HTML coding practice. Using the JDBC Java applet The JDBC Java applet offers a distinct alternative to the TDC for representing Office databases on a Web page. Figure D shows what at first glance may look like an ordinary collection of output from the TDC.

Figure D: We used the JDBC applet to publish this extract from the Access Northwind Employees table.

However, this set is from the JDBC Java applet. Notice that Figure D shows one more employee than does the output from the TDC data set in Figure A. If you add a new record to a data source exposed with the TDC, the DSO doesn't show the record until the next time you create a new delimited text file. The TDC exposes a copy of a data source--but the JDBC applet interfaces directly with the data source.

Since the JDBC applet connects directly to a database, such as an Access MDB file, any changes to the database appear on a Web page the next time you link to it or refresh the page. Unlike the TDC, the JDBC applet requires a DSN that points at an ODBC data source. This applet offers you the flexibility of writing a SQL statement to extract any information from a data source. The applet also runs on machines that won't accept ActiveX controls, such as Mac and UNIX computers.

Since data binding is a browser-side technology, the DSN must reside on the browser workstation rather than the Web server. You can reference any file accessible to the browser workstation via an ODBC connection--this includes database files on other machines, such as a Web or database server. The requirement for a browser-based DSN is in marked contrast to ASP technology, which requires a DSN on the Web server.

The JDBC applet is a sample Java data source that demonstrates the technology. However, it may not be appropriate for finished applications--the applet works fine the first time you use it after booting, but fails when you try to restart the applet after its initial launch in a computer session. You must reboot the computer to make it work again.

Organizations with a commitment to Java technology can use this applet as a starting point with either OLE DB Simple Provider or OLE DB APIs to create custom Java DSO applets. These DSOs can uniquely fit the needs of a business and work on more workstation types than ActiveX controls, such as the TDC.

Listing B shows the DHTML code that generated the extract from the Northwind database displayed in Figure D. You still need an ID setting to refer to the data cache contents. The value for the cabbase parameter specifies the name and path to the Java applet. (This location can be on the Web server.) The dbURL parameter designates a DSN that, in turns, points to an ODBC data source. Remember that the DSN must reside on the browser workstation.

Listing B: DHTML code for creating an extract from the Northwind Employees tables using JDBC

<html><body>

<!-- Instantiate Java applet -->

<applet code=JDC.class ID="jaEmployees"
	 width=0 height=0>
<param name=cabbase value="jdc.cab">
<param name=dbURL value="jdbc:odbc:
	Northwind2200">
<param name=showUI value=false>
<param name=sqlStatement value=
"select FirstName, LastName, Extension from 
	Employees;">
<param name=allowInsert value="true">
<param name=allowDelete value="true">
<param name=allowUpdate value="false">
</applet>

<!--
* See datasrc reference to Java applet 
* Also see style block text alignment
-->

<table ID=mytbl datasrc="#jaEmployees" border 
cellspacing=2 cellpadding=7 width="50%">
<caption><b><i><font size=6>
	Employee Phone Directory
</b></i></font></caption>
<thead><tr STYLE="font-weight:bold">
   <td width="40%" valign="TOP">
<b><i><font SIZE=5><P>
	First Name</b></i></font></td>
   <td width="40%" valign="TOP">
<b><i><font SIZE=5><P>
	Last Name</b></i></font></td>
   <td width="20%" valign="TOP">
<b><i><font SIZE=5><P 
	style="text-align:right">
Phone Ext.</b></i></font><
	/td></tr></thead>

<!--
* See datafld references to fields 
* Also see align setting for last div block
-->

<tbody>
<tr>
<td width="40%" valign="TOP"><b>
	<font SIZE=4>
<div DATAFLD="FirstName"></div><
	/b></font></TD>
<td width="40%" valign="TOP"><b>
	<font SIZE=4>
<div DATAFLD="LastName"></div><
	/b></font></TD>
<td width="20%" valign="TOP"><b><
	font SIZE=4>
<div align="right" DATAFLD="Extension"><
	/div></b></font></TD>
</tr>
</tbody></table>

</body></html>

The remaining two sections of the DHTML code lay out the table heading and contents. Despite the fact that the JDBC applet uses an entirely different technology than the TDC to implement its capabilities, you'll notice that the code in these sections of Listing B exactly matches that in Listing A. This similarity confirms the capability of data binding for easy implementation across multiple platforms.

Conclusion

In this article, we've only skimmed the surface of data binding with Office databases and the IE4 browser. We've focused on how to display tables from an Office database over the Web. We also demonstrated how to use two types of DSOs and how to implement page scrolling through the records in a data source. DSO technology is important for Office developers because it represents an easy, fast, and flexible way to share Office databases across a web. This technology is particularly appropriate when you can restrict browser access to IE4, as on an intranet. However, DSOs will work at Internet sites having the resources to perform browser detection and then route surfers to pages optimized for their browser.

Copyright © 1998, ZD Inc. All rights reserved. ZD Journals and the ZD Journals logo are trademarks of ZD Inc. Reproduction in whole or in part in any form or medium without express written permission of ZD Inc. is prohibited. All other product names and logos are trademarks or registered trademarks of their respective owners.