cess shines as a rapid prototyping tool for building frequently visited Internet sites.
This approach requires three items. First, Web-site visitors must have Web browsers. Second, the IIS/IDC must be running on the server. The IDC is a DLL named HTTPODBC.DLL. Third, you must install the Access 7 for Windows 95 Open Database Connectivity (ODBC) driver on the server. The IDC requires such a "thread-safe" ODBC driver so that multiple clients can launch concurrent queries against the same data source.
Dynamic Access Parts
The Web browser handles any user events and typed input, and it displays the information returned by the database. An .mdb file stores the tables, forms, queries, and modules that comprise your Access database. Two key developer-designed files on the IIS manage the interactive process, as shown in the figure
"How Ac
cess Generates Web Pages."
The .idc file defines specific valid data fields and operations; the .htx file formats the returned data for display by the client browser.
The key field in an .idc file is
SQLStatement
. It lets you perform all the functions permitted by Access's SQL. Two other required fields are
Datasource
and
Template
.
Datasource
ultimately points the query embedded in
SQLStatement
to the appropriate .mdb file. Finally,
Template
names the .htx file that's responsible for formatting the result.
You can include several optional fields that refine your application's behavior.
MaxRecords
designates the maximum number of records a query returns. With
DefaultParameters
, you can supply default criteria for a query when the client fails to do so. Use
RequiredParameters
instead to generate an error when the client browser does not provide a parameter.
Username
and
Password
act as a gateway to sec
urity features. These fields, in conjunction with Access's users, groups, and permissions features, can restrict admittance to tables with sensitive data, such as salaries.
The .htx file is a template for the HTML file passed back to the client Web browser from the IIS. The .htx file includes special tags for processing the query results returned by the ODBC driver. These vendor-specific tags, in conjunction with traditional HTML tags, let developers control what a client browser views.
A Sample Program
I constructed a simple database application based on the Northwind database sample that ships with Access. My test database includes two tables. The first,
tblCustomers
, contains the
CustomerID
,
CompanyName
,
ContactName
, and
Phone
fields. The second,
tblSalesByCustomer
, houses the
CustomerID
and
Sum
fields.
Sum
contains the total sales for each customer.
My initial .idc file (shown in the listing
"Query Input and Control"
) creates a listing of the top 10 customers ranked by sales in descending order. This file contains four fields. The first one,
Datasource
, points to the target .mdb file, as routed through an ODBC data source. The ODBC-driver dialog box has a New button that allows the developer to create the data source that points to the .mdb file. However,
Datasource
eliminates this bit of manual intervention for the user, which is necessary to support a self-contained Web site.
The second field,
Template
, names an .htx file that serves as a template for formatting the HTML page to appear on the client's browser. The third field,
MaxRecords
, restricts the lists of returned records to 10. Finally,
SQLStatement
includes the SQL string that controls the query. Each line, including the first one, must begin with a plus sign.
An .htx file can contain all the graphic files and formatting touches that regularly adorn the HTML pages
at your favorite Web sites. The .htx file displays the query results within a pair of
<%BeginDetail%>
and
<%EndDetail%>
vendor-specific tags. To display a particular query, bracket its name inside these vendor-specific tags (e.g.,
<%Sum%>
). The IIS repeatedly merges all .idc fields between
<%BeginDetail%>
and
<%EndDetail%>
for each record in the return data set.
You create drill-down hyperlinks to other pages by use of HTML anchor tags. You do this by placing another .idc filename between the anchor tags, which in turn creates a drill-down page. A question mark must follow the .idc filename. This is demonstrated in the first listing's .htx file. When you need to pass a parameter to the second .idc file, insert a phrase such as
CustomerID=<%CustomerID%>
after the .idc filename. The left term,
CustomerID
, designates a field in
SQLStatement
for the second .idc file. The term that's on the right of the
equal sign references the value in the current hyperlink.
The listing
"Data Close Up"
shows the .idc and .htx files that generate the drill-down page from the top 10
CustomerID
displays. This .idc file extracts the company and contact names along with the phone number for the CustomerID that matches the one in the hyperlink jump. It accepts a passed argument with an expression such as
CustomerID=%CustomerID%
in the
Where
clause of its
SQLStatement
. The term on the left of the equal sign corresponds to a field in the
Select
list of the query. The term on the right of the equal sign represents the passed parameter. Its value is set by the calling hyperlink jump.
The .htx file in the listing
"Data Close Up"
demonstrates some basic HTML tag techniques for controlling the display of records returned by an .idc file. Its corresponding .idc file returns a single record with four fields. The
BR
tag positions eac
h field on a different line. The other tags adjust the font and the position of the results.
Closing Issues
The IIS/IDC plus Access provides a quick, easy way to start publishing dynamic HTML pages from a database. Many more options beyond those in the code listings are feasible. For example, you can populate combo boxes on HTML forms, or you can accept values directly from HTML forms for selecting or updating an Access database.
I found the new functionality easy to use, but experienced Access developers will appreciate enhancements. First, Access developers are used to more flexibility in processing return sets than the .htx file permits. Among the features missing are event programming and expressions. Second, no error-trapping techniques are available with this release. While errors do not cause the system to abruptly halt without user recovery, you can still be confronted with error messages that may not be meaningful. Third, wizards and more graphical development aids would
help promote the acceptance of this Web development strategy among Access developers.
.idc file that shows top 10 customers
Datasource: WebSales
Template:
SalesByCustomer.htx
MaxRecords: 10
SQlstatement:
+SELECT CustomerID, Sum
+FROM tblSaesByCustomer
+ORDER BY Sum DESC
.htx file to display customers and sales
<HTML>
<HEAD>
<TITLE>Drill-Down List</TITLE>
</HEAD>
<BODY>
<FONT FACE=ARIAL SIZE="2">
<UL>
<%BeginDetail%>
<LI><A HREF="/Byte/CustomerDetail.idc?CustomerID=<%CustomerID%>">
Customer ID:
<%CustomerID%></A>,
<%Sum%><BR>
<%EndDetail%>
</UL>
</BODY>
</FONT>
</HTML>
.idc file that drills down to detail a particular customer
Datasource: WebSales
Template: CustomerDe
tail.htx
SQLstatement:
+SELECT CustomerID, CompanyName,
+ContactName, Phone
+FROM tblCustomers
+Where CustomerID=%CustomerID%
.htx file that displays a particular customer
<HTML>
<HEAD>
<TITLE>Customer Contact Detail</TITLE>
</HEAD>
<BODY>
<%BeginDetail%>
<FONT SIZE="4">
Here is the contact information for Customer ID:
<%CustomerID%><BR>
<P>
<FONT SIZE="3">
CompanyName:
<%CompanyName%><BR>
ContactName:
<%ContactName%><BR>
Phone:
<%Phone%><BR>
<%EndDetail%>
</BODY>
</FONT>
</HTML>
illustration_link (18 Kbytes)
The developer provides several files to create an interactive database connection on the Web.
Rick Dobson is president of CAB, Inc., a database and Internet consultancy. You can reach him at
Rick_Dobson@msn.com
.
Make Access and the Web Work Together
