Job Forum: A Sample Internet Information Server Application That Stores and Retrieves Data from a Microsoft Access Database

Microsoft Corporation

January 1996

Click to open or copy the files in the Job Forum sample application for this technical article.

Note   This document is an early release of the final specification. It is meant to specify and accompany software that is still in development. Some of the information in this documentation may be inaccurate or may not be an accurate representation of the functionality of the final specification or software. Microsoft assumes no responsibility for any damages that might occur either directly or indirectly from these inaccuracies. Microsoft may have trademarks, copyrights, patents or pending patent applications, or other intellectual property rights covering subject matter in this document. The furnishing of this document does not give you a license to these trademarks, copyrights, patents, or other intellectual property rights.

Contents

Introduction

Overview

The Job Forum Sample Application Overview

The Communication Process

Internet Database Connector Files (.IDC Files)

HTML Extension Files (.HTX Files)

Referencing .IDC Files from a Web Browser

Installation and Setup

The Job Forum Sample Application

Microsoft Internet Information Server

The ODBC Data Source

Using the Job Forum Sample Application

Job Forum Example 1: Viewing Job Available Listings—Custom Search

Job Forum Example 2: Entering a New Job Wanted Listing

Job Forum Example 3: Performing Remote Database Administration

Conclusion

Introduction

The explosive growth of the Internet in recent years has exposed thousands of users and developers to a powerful resource. Although the Internet itself is not new, recently developed tools for browsing the World Wide Web (WWW) have made the Internet more accessible and more popular.

In simple terms, Web browsers such as Microsoft® Internet Explorer or Netscape® Navigator® provide a convenient client interface to data on distributed servers. Once users are accessing data on the Web, they quickly decide they want to merge the power of the Internet with the power and convenience of Microsoft Access.

This paper discusses how to provide access to Microsoft Access databases from a Web browser and describes a sample application that uses Microsoft Access for this purpose. The Job Forum sample application demonstrates how you can use the capabilities of Microsoft Internet Information Server to allow a user to execute queries against data stored in a Microsoft Access database using a Web browser, store and retrieve data from a Microsoft Access database, and return information to the Web browser formatted as a Hypertext Markup Language (HTML) document.

The Microsoft Internet Database Connector is a component of Microsoft Internet Information Server. It is a powerful tool that accepts input from a Web browser to execute SQL commands against an open database connectivity (ODBC) data source and return any resulting data to the Web browser formatted as an HTML document or form. Internet Information Server can provide access from a client Web browser to data stored on servers in many different database formats.

This paper focuses on using the Microsoft Internet Database Connector to access data stored in Microsoft Access databases. We recommend that you use Internet Information Server with data stored in a Microsoft Access database when your application will be accessed by a limited number of users. The combination of Internet Information Server and Microsoft Access is ideal for accessing data across a corporate intranet or on a World Wide Web site. If your Web site grows to the point where it gets thousands of hits per day, we recommend that you consider upsizing the database to Microsoft SQL Server. For example, the Job Forum application discussed in this paper was developed using Microsoft Access and then upsized to Microsoft SQL Server to handle the high traffic on the Microsoft Access Web site. The following components work together to provide the described functionality and must all be present for the Job Forum application to work properly:

• A Web browser (such as Microsoft Internet Explorer or Netscape Navigator)
  
  
• Microsoft Internet Information Server running on Windows NT® Server 3.51
  
  
• The Microsoft ODBC Access driver
  
  
• A Microsoft Access database

This paper assumes that you have a basic understanding of the Internet and the World Wide Web and are familiar with the structure of HTML documents and forms, and the operation of the Hypertext Transfer Protocol (HTTP). It also assumes that you have the Microsoft Internet Information Server properly configured on a machine using the Microsoft Windows NT Server 3.51 operating system and that you have Microsoft Access installed on your system and understand how to create tables and queries.

Overview

The Job Forum Sample Application Overview

The Job Forum sample application is like an interactive Classified Ads section of a newspaper. It lets Web users browse jobs-available and jobs-wanted listings stored in a Microsoft Access database. The application also lets users enter new jobs available or jobs wanted. Users can view existing job postings according to several criteria, such as job type or job location.

Figure 1. Job Forum sample home page

The data for the Job Forum application is stored in a Microsoft Access database. Users can execute queries against the Microsoft Access database. The data returned by these queries is presented to the user as an HTML document or form. To execute queries against a Microsoft Access database and return data formatted in HTML, the Job Forum application uses a component of Microsoft Internet Information Server called the Microsoft Internet Database Connector.

The Communication Process

Browsers submit requests to Internet Information Server using HTTP. Internet Information Server uses the Internet Database Connector (HTTPODBC.dll) to access data in a Microsoft Access database using ODBC. The following diagram shows the components and steps involved in the communication between a Web browser and a Microsoft Access database.

Figure 2. Steps of communication between a Web browser and a Microsoft Access database

Internet Database Connector Files (.IDC Files)

The Internet Database Connector uses ASCII files saved with an .IDC extension to get information on how to connect to an ODBC data source and how to execute an SQL statement. These files contain several required fields and may also include optional fields that contain the information necessary to execute a query.

Required fields

Optional fields

For example, the following is the text from the A_V_Cust.idc file in the Job Forum sample application:

Datasource: JobForum
Template: ViewAvail.htx
DefaultParameters: ListingDate=12/1/95, JobTitle=%%, JobDesc=%%, JobCity=%%, JobState=%%, JobAreaCode=%%, JobZip=%%, JobDesc1=%%, JobDesc2=%%
MaxRecords: 50
SQLStatement:
+SELECT *
+FROM %JobType%
+WHERE ListingDate>=#%ListingDate%#
+AND JobTitle LIKE '%%%JobTitle%%%' AND JobDesc LIKE '%%%JobDesc%%%'
+AND JobDesc LIKE '%%%JobDesc1%%%' AND JobDesc LIKE '%%%JobDesc2%%%'
+AND  JobCity LIKE '%JobCity%' AND  JobState LIKE '%JobState%'
+AND  JobAreacode LIKE '%JobAreaCode%'AND JobZip Like '%JobZip%' AND Filled=%Filled%

A reference to the A_V_Cust.idc file is passed to Internet Information Server when a user clicks the "Create a custom search" hyperlink on the ViewAvail.htm page (see Figure 3).

Figure 3. Browser view of ViewAvail.htm page

The "Create a custom search" hyperlink is formatted using the following HTML code:

<A HREF="/JobForum/A_V_Cust.htm">
<IMG SRC="/JobForum/images/but4.gif" WIDTH=25 HEIGHT=24 ALIGN="Center" Border=0></A>
Create a custom search.<BR>

HTML Extension Files (.HTX Files)

The Internet Database Connector uses ASCII files saved with an .HTX extension to determine how to format data returned from an SQL statement contained in an .IDC file. .HTX files look like HTML documents with a few exceptions. Because .HTX files are templates for formatting data returned from a query, they contain keywords that control how the data is formatted. They also contain placeholders specifying where that data will be inserted in the HTML document.

The following text comes from the ViewAvail.htx file. Note that placeholders for data contained in fields returned by the query use the syntax: <%fieldname%>.

<HTML>
<HEAD>
<title>Microsoft Access Job Listing</title>
</HEAD>
<BODY>
<BODY BGCOLOR=FFFFFF>
<IMG SRC="/JobForum/images/accjob.gif" WIDTH=442 HEIGHT=107 ALIGN="BOTTOM" BORDER=0><BR>
<IMG SRC="/JobForum/images/1pixline.gif" WIDTH=595 HEIGHT=1 ALIGN="BOTTOM" BORDER=0><BR>
<P>
<FONT FACE=ARIAL SIZE="2">
Select a listing to view details.
<UL>
<%BeginDetail%>
<LI><A HREF ="/JobForum/Queries/A_Detail.idc?AvailID=<%AvailID%>">Listing Number <%AvailID%>:</A> <%JobTitle%>, <%Company%>, Submitted: <%TextDate%><BR>
<%EndDetail%>
</UL>
<%If CurrentRecord EQ 0 %>
<I>No Job Available listings matched your search criteria.</I>
<%Else%>
Return to <A HREF="/JobForum/ViewAvail.htm">view job available listings.</A>
<%EndIf%>
<P>
<IMG SRC="/JobForum/images/1pixline.gif" WIDTH=595 HEIGHT=1 ALIGN="BOTTOM" BORDER=0><BR>
<P>
<A HREF="/JobForum/default.htm">Return to Microsoft Access Job Forum Home Page</A>
</BODY>
</FONT>
</HTML>

Internet Information Server creates an HTML file based on the formatting instructions contained in the .HTX template. The following graphic shows the browser view of an HTML file created from the information contained in the ViewAvail.htx file.

Figure 4. Browser view of an HTML file created from the information contained in the ViewAvail.htx file

The following table discusses the keywords used to format an .HTX file.

Referencing .IDC Files from a Web Browser

Initiating queries from HTML documents

You initiate a query from a hyperlink by referencing the IDC file containing the query from within an HTML anchor. The hyperlink requires that you append a question mark (?) to the .IDC filename. If a hyperlink contains an .IDC filename without the appended question mark, the Web browser will attempt to display the .IDC file and will generate an error. For example, the following hyperlink anchor contains a reference to a query that returns record number 233 from the Job Forum database:

<A HREF ="/JobForum/Queries/A_Detail.idc?AvailID=233">Listing Number 233:</A> Contractor, Microsoft, Submitted: 1/10/96

You can pass any parameters required by a query by hard-coding them in the HTML anchor. In the last example, the AvailID parameter is passed to the SQL statement contained in A_Detail.idc using a value of 233. If you want to pass user-supplied variables to a query, you must use an HTML form.

Initiating queries from HTML forms

You invoke queries from HTML forms using the POST method, referencing the .IDC file with the HTML ACTION keyword. For example:

<FORM METHOD="POST" ACTION="/JobForum/Queries/A_V_Cust.idc">

You do not append a question mark character (?) to the .IDC filename as discussed above. For example, a simple HTML form that lets the user submit a query that searches the database according to the value of the LastName parameter (supplied by the user) might look like the following:

<HTML>
<HEAD>
<TITLE>Search by last name</TITLE>
</HEAD>
<BODY>
<H1>Search by Last Name</H1>
<FORM METHOD="POST" ACTION="Queries/Lname.idc">
<BR>
Enter Last Name: <INPUT NAME="LastName" VALUE="" MAXLENGTH="50" >
<BR>
<P>
<INPUT TYPE="SUBMIT" VALUE="Submit">
</FORM>
</BODY>
<HR>
</HTML>

The form created using the HTML codes above is shown in Figure 5.

Figure 5. Browser view of form created using the HTML code directly above

When the user clicks the Submit button, the query contained in Lname.idc is passed to Internet Information Server using the user-supplied value for the LastName parameter.

Installation and Setup

Button to download the Job Forum sample application (zipped, XXK).

The Job Forum Sample Application

You can get the Job Forum sample application by downloading JobForum.exe above. JobForum.exe is a self-extracting compressed file that contains all the .HTM, .IDC, .HTX, and .GIF files necessary to run the application with the Internet Information Server software.

Create a JobForum directory in your Internet Server's Web root and expand the JobForum.exe file with the "-d" flag (in a DOS window, execute the following command: \jobforum\jobforum.exe -d). The Job Forum database (JobForum.mdb) and all .HTM files are stored in the JobForum directory. All .IDC and .HTX files are stored in the \Queries subdirectory. All .GIF files are stored in the \Images subdirectory.

Microsoft Internet Information Server

The following steps outline how to properly set up the Job Forum application on your server.

1. Open the Internet Server Manager and select your Web server.
   
   
2. Click the Properties menu item and then click Service Properties. Select the Directories tab in the resulting dialog box.
   
   
3. Click Add and browse your server directories to select the JobForum subdirectory. (For example, c:\inetsys\wwwroot\jobforum.)
   
   
4. Enter "Jobforum" as the alias for the virtual directory.
   
   
5. Click OK.
   
   
6. Click Add and browse your server directories to select the JobForum\Queries subdirectory. (For example, c:\inetsys\wwwroot\jobforum\queries.)
   
   
7. Enter "/JobForum/Queries" as the alias for the virtual directory.
   
   
8. In the Access section of the Directory Properties dialog box, deselect the Read option and select the Execute option.

Select the JobForum\Queries subdirectory and click on the Edit Properties button. In the Access section of the resulting dialog box, make sure the Execute box is checked and the Read box is unchecked. This lets Microsoft Internet Information Server execute your queries contained in .IDC files and prevents unauthorized access to the HTML source code used to create those files.

The ODBC Data Source

You must have the ODBC Access driver to open and query Microsoft Access databases using ODBC. The Microsoft Access ODBC driver is installed on your system when you install Microsoft Access.

The Microsoft ODBC Access driver is not provided with the Internet Information Server software. You can obtain the driver by:

• Installing Microsoft Access on the Internet Server.
  
  
• Installing the ODBC Service Pack.
  
  
• Calling Microsoft Product Support Services

You set up a new ODBC data source using the ODBC Administrator. To define a new data source for the ODBC Access driver, click the System DSN button, click the Add button, and select the Microsoft Access driver. Use "JobForum" as the Data Source Name, then click the Select button and choose the JobForum.mdb database file.

The data source name (DSN) you create in the ODBC Administrator is the value you will use for the DataSource field entry in your IDC files. For more information on setting up an ODBC data source, open Help from within the ODBC Administrator dialog box.

Using the Job Forum Sample Application

Once the Job Forum application is properly set up, the first thing a user browsing the Web will see is the Job Forum application main HTML page, default.htm, located at http://yourwebservername/jobforum.

The following graphic shows the Job Forum default.htm file displayed in Microsoft Internet Explorer. When a user clicks on one of the hyperlinks provided in default.htm, an HTML form is displayed to collect additional information.

Figure 6. Browser view of default.htm

To illustrate how the Job Forum sample application interacts with the Web browser and with the data stored in the Microsoft Access database, this paper discusses three user scenarios: Viewing available job listings, entering a new job wanted listing, and performing remote database administration.

Job Forum Example 1: Viewing Job Available Listings—Custom Search

When the user clicks the button next to the text "View job available listings," the browser displays the HTML form ViewAvail.htm.

Figure 7. Browser view of ViewAvail.htm

The user may select one of four options specifying how to search available job listings. The first option, "View all open listings," calls the A_V_Open.idc file, which contains a query that returns all open job listing records:

Datasource: JobForum
Template: ViewAvail.htx
SQLStatement:
+SELECT *
+FROM JobAvailable
+WHERE Filled=0

The remaining three hyperlinks, "View listing posted since a specific date," "Search for a specific listing number," and "Create a custom search," each call separate HTML forms to collect information from the user to further narrow the search criteria.

The "Create a custom search" hyperlink displays the HTML form A_V_Cust.htm.

Figure 8. Browser view of A_V_Cust.htm

This form lets the user enter criteria used to limit the number of records returned. When the user clicks the Search button, the browser uses HTTP to submit the following URL to the Internet Information Server:

<FORM METHOD="POST" ACTION="/JobForum/Queries/A_V_Cust.idc">

The A_V_Cust.idc file

The A_V_Cust.idc file contains all the information necessary for the Internet Information Server to execute a custom search query against the Microsoft Access database. The companion template file, ViewAvail.htx, contains the formatting information necessary to display the data returned by the query to the user in HTML format. A_V_Cust.idc contains the following fields and values.

There are two important things to notice about the SQL statement portion of this file. First, Internet Information Server interprets double percentage signs (%%) as an asterisk wildcard character. (Note: In .IDC files, all variable names are surrounded by single percentage signs, so variables with wildcard characters appended are surrounded by three percentage signs.) The following WHERE clause syntax lines would be equivalent where ListingDate, JobTitle, and JobDesc are variables provided by the user.

IDC WHERE Clause:

WHERE ListingDate > #ListingDate# AND JobTitle LIKE '%%%JobTitle%%%' AND JobDesc LIKE '%%%JobDesc%%%'

Microsoft Access WHERE Clause:

WHERE ListingDate>#ListingDate# AND JobTitle Like "*JobTitle*" AND JobDesc Like "*JobDesc*"

The second important consideration is that when you pass a date criteria to the query as a variable, you must surround the date variable with number sign characters (#).

The Internet Information Server uses ODBC to connect to the JobForum data source and execute the SQL statement contained in A_V_Cust.idc using values supplied by the user in the form A_V_Cust.htm. The default values listed in A_V_Cust.idc are used in place of any field in the A_V_Cust.htm form the user leaves blank.

The ViewAvail.htx file

The ViewAvail.htx file is the template used to format the data returned by the query contained in A_V_Cust.idc.

The detail section of the ViewAvail.htx template contains the following formatting instructions:

<%BeginDetail%>
<A HREF ="/JobForum/Queries/A_Detail.idc?AvailID=<%AvailID%>">Listing Number <%AvailID%>:</A> <%JobTitle%>, <%Company%>, Submitted: <%TextDate%><BR>
<%EndDetail%>

These instructions tell the Internet Database Connector that for each record returned by the SQL statement, the HTML document should display a hypertext link that references the A_Detail.idc file along with the AvailID. The JobTitle, Company, and TextDate fields follow the hyperlink. For example, a query based on the A_V_Custom.idc file and formatted according to the ViewAvail.htx file might return the HTML document shown in Figure 9.

Figure 9. Browser view of HTML document returned by a query based on the A_V_Custom.IDC file and formatted according to the ViewAvail.htx file

When the user clicks a Listing Number hyperlink, the Internet Database Connector uses the information in A_Detail.idc to execute an additional query against the Microsoft Access database to return and display a single record (specified by the Listing Number), formatted according to the information contained in the A_Detail.htx template.

Note   Date values should be returned to Internet Information Server formatted as text. You can convert DATE/TIME data types to text using the Cstring function. In addition, you can store Date values in a text field. This is the technique used in the Job Forum application.

Job Forum Example 2: Entering a New Job Wanted Listing

When the Job Forum home page is displayed, clicking the button next to the text "Enter a new job wanted listing" displays the HTML form that is created from an HTX template based on the query contained in the JobClass.idc file. This form differs from the first example in that it displays a dynamic list of values in the Job Classification drop-down combo box. The combo box is filled with data based on the query specified in the JobClass.idc file.

Using a query to fill a combo box on a form

The JobClass.idc file contains the following fields and values.

In this SQL statement, JobClass is a field in the Job Classification table in the JobForum.mdb file. The information returned from this query is used to populate the drop-down combo box on the form created from the JobClass.htx template.

The JobClass.htx template is designed to create a separate entry in the combo box for each record in the Job Classification table. The section of the JobClass.htx file code that populates this combo box is:

Job Classification:
<SELECT NAME="JobClass">
<%BeginDetail%>
<OPTION><%JobClass%>
<%EndDetail%>
</SELECT>

The following graphic illustrates how this combo box appears in the resulting HTML form.

Figure 10. Browser view of HTML form that results from the code listed directly above

Any additional classifications added to the Job Classification table in the Job Forum database will be automatically displayed whenever this form is viewed using a Web browser.

Allowing changes to data submitted to the database

When the user has filled in all necessary data in the JobClass.htm form and clicked the Submit button, the information submitted is displayed for review. If the information is correct, the user clicks the "Listing is correct" graphic, and an HTML document is displayed containing the Listing Number for the newly created record. If the information needs to be changed, the user clicks the "Change/Update Listing" graphic, and an HTML form is displayed in which the user may edit the information. In both cases, the newly created record must be retrieved from the database.

When the user clicks the Submit button after first entering new job wanted information, the INSERT INTO query contained in the NewWant.idc file creates a new record in the JobWanted table. The JobWanted table has an AutoNumber primary key field named WantedID. The WantedID value is assigned by Microsoft Access when a new record is created. There is no way to retrieve the value of the WantedID field at the time a new record is created. The Job Forum application uses hidden fields in an HTML form that contains sufficient information from the original INSERT query to find the newly created record.

The NewWant.htx file is the template that formats the data returned from the INSERT query for display, so the user can confirm that the listing is correct. The user sees two hyperlink buttons along with the information submitted to the database.

Figure 11. Browser view of the data returned from INSERT query

These hyperlinks are followed by a static display of all the information submitted by the user. What the user does not see are the hidden fields contained in this form that contain the information necessary to find the newly created record. The hidden fields contain data passed from the NewWant.idc file. The hidden fields in the form created from NewWant.htx are formatted in the following style:

<INPUT TYPE="HIDDEN" NAME="ContactName" VALUE="<%idc.ContactName%>">
<INPUT TYPE="HIDDEN" NAME="Company" VALUE="<%idc.Company%>">
<INPUT TYPE="HIDDEN" NAME="JobTypeID" VALUE="<%idc.JobTypeID%>">
<INPUT TYPE="HIDDEN" NAME="JobClass" VALUE="<%idc.JobClass%>">
<INPUT TYPE="HIDDEN" NAME="JobDesc" VALUE="<%idc.JobDesc%>">
<INPUT TYPE="HIDDEN" NAME="SpecialSkills" VALUE="<%idc.SpecialSkills%>">
<INPUT TYPE="HIDDEN" NAME="YearsExp" VALUE="<%idc.YearsExp%>">
<INPUT TYPE="HIDDEN" NAME="Education" VALUE="<%idc.Education%>">
<INPUT TYPE="HIDDEN" NAME="Location" VALUE="<%idc.Location%>">

You use the <%idc.VariableName%> format to return variables that were passed in the IDC file. For example, <%idc.ContactName%> contains the information supplied in the ContactName variable contained in the NewWant.idc file.

The hyperlinks that let the user submit or edit information are formatted as follows:

If the user clicks the "Listing is correct" graphic, the hidden values listed above are passed to the SQL statement in W_ListID.idc and are used to find the new record created by the user. If the user clicks the "Change/Update Listing" graphic, additional hidden fields are passed to the SQL statement in Chg_Want.idc. They are used to find the new record, and also to populate the form with the data supplied by the user so that the data can be edited. Chg_Want.idc uses Chg_Want.htx to create the form that lets the user edit the information in their new listing.

Because there are a number of fields that let the user choose from multiple options, the HTX template that displays the user's new listing data for editing must use conditional logic to display the data originally entered by the user. For example, when the user entered the original data, a selection may have been made in the Job Classification drop-down combo box. The Chg_Want.htx template contains the formatting instructions necessary to display the user's original selection in the same combo box on the form used to edit new listings:

<P>
<B>Job Classification: </B><SELECT NAME="JobClass">
<OPTION <%if JobClass EQ "Application Developer" %> Selected <%EndIf%>>Application Developer
<OPTION <%if JobClass EQ "Application Support" %> Selected <%EndIf%>>Application Support
<OPTION <%if JobClass EQ "Data Analysis" %> Selected <%EndIf%>>Data Analysis
<OPTION <%if JobClass EQ "Data Entry" %> Selected <%EndIf%>>Data Entry
<OPTION <%if JobClass EQ "Tester" %> Selected <%EndIf%>>Tester
<OPTION <%if JobClass EQ "Trainer" %> Selected <%EndIf%>>Trainer
<OPTION <%if JobClass EQ "Writer" %> Selected <%EndIf%>>Writer
<OPTION <%if JobClass EQ "Other" %> Selected <%EndIf%>>Other
</SELECT>
<P>

Job Forum Example 3: Performing Remote Database Administration

In many cases the Microsoft Access database may not be readily accessible after it has been posted to the server. You can remotely perform certain database administration functions using the Internet Database Connector. For example, the developer of the Job Forum sample application wanted to be able to delete records from the database that were over 90 days old.

Two files are stored in the Webroot\JobForum\Queries subdirectory that allow you to execute a Delete query that deletes records older than 90 days. The Del_90.idc file contains the following fields and values.

The SQL statement in the Del_90.idc file contains a reference to a query named "Available over 90," which selects all records from the Job Available table with a listing date that is less than 90 days before the current date. The query's ListingDate field uses the parameter <=Date()-90.

You would execute this query from a Web browser by entering the URL for the Del_90.idc file into the Web browser address box. For example, to run the query contained in the Del_90.idc file using Microsoft Internet Explorer, you would type the following URL in the browser Open box and press the Enter key.

Figure 12. Browser view of running the query contained in the Del_90.idc file

The HTX template file Del_90.htx contains the formatting information necessary to display the message, "The query to delete all available listings over 90 days old has run."

Conclusion

The methods discussed in this paper should give you a good start in making your Microsoft Access data available to World Wide Web browsers using Internet Information Server. The Job Forum sample application uses a wide variety of techniques to access and display data from a Microsoft Access database. This paper has discussed only a few of those techniques. To learn more, study the Job Forum .IDC files and companion .HTX templates to see how they execute queries, and return and format data from the Job Forum database.