Traditional Approaches

The traditional Access Web technologies are particularly appropriate for developing low-volume (around 10,000 visitors per day or fewer) Web and database solutions. Since browsers can be incompatible across manufacturers and versions, traditional approaches are safe and relatively easy to use.

Access 2000 offers three such approaches:

• Publishing datasheets in HTML, IDC/HTX, and ASP format.
• Using HTML forms. There is no built-in development environment within Access for HTML forms, but you can adapt your old HTML form solutions to OLE DB/ADO (ActiveX Data Objects) technology.
• Publishing reports in snapshot format to an FTP folder. This simplifies access to Access reports using Netscape browsers.

Publishing Datasheets

You can choose Export from the File menu in the database window to export an Access datasheet, such as a table. This command (which also publishes datasheets behind forms and reports) offers some of the functionality associated with the obsolete Publish To The Web wizard. It publishes datasheets in HTML, IDC/HTX, and ASP formats. The HTML format is static but easy to edit with standard HTML editors. The latter two formats are dynamic. They use the server to write out HTML content to reflect the most recent results in a datasheet.

The IDC/HTX format targets the first two releases of Microsoft Internet Information Server (IIS), but it also works with more recent releases. The ASP format works only with more recent releases, but it lets you mix Microsoft Visual Basic Scripting Edition and Microsoft JScript with HTML code. You should not automatically choose one of the dynamic formats. If you have large datasheets or many users, and if the content in a datasheet changes infrequently or if timeliness is not an issue, the static HTML format might be the best choice because static pages publish faster.

Publishing in HTML format

To publish a datasheet in HTML format, select a table, query, form, or report in the database window and then choose Export from the File menu. In the Export dialog box, use the Save In drop-down list box to select a location for the file. If you are publishing to an intranet, this can be a folder at the site. If you are publishing to a remote Web server, you can use any local folder to collect your HTML pages and then transfer the pages to a remote server using the File-Import command in FrontPage 2000. You can also use another route, such as File Transfer Protocol (FTP).

Figure 13-1 shows the Export dialog box for publishing the datasheet behind the Products form from the Northwind database. The Save As Type drop-down list box shows the HTML format. The Save In box designates the folder CAB_Office_2000, the root folder of a local intranet site with the same name. The Autostart check box is selected, so the page will appear as soon as it is published. This can cause a browser session to start with the Products datasheet open.

Figure 13-1. The Export dialog box.

When you click Save, the HTML Output Options dialog box appears. Here you can specify a template for the page containing the datasheet. If you have a theme set for the default.htm page, you can reference that page. Otherwise, you can reference a local file with formatting that you will use at a remote site.

One of the advantages of the HTML format is that you have an HTML table to edit. Figure 13-2 shows the published Products datasheet in FrontPage 2000 and the Table Properties dialog box, which shows some of the formatting options, including cell padding, cell spacing, and background color or image. You can also set properties for cells, rows, and columns to specify other formatting options. You can undo any custom formatting in FrontPage, and you can even select individual columns and rows and selectively delete them.

Figure 13-2. After loading your datasheets into FrontPage, you can edit the resulting HTML table.

Publishing in IDC/HTX format

When you publish a datasheet in either dynamic format, you need an open database connectivity (ODBC) Data Source Name (DSN). The DSN must reside on the Web server along with the database to which it refers. The file DSN approach is in theory more flexible since you can send it to the server, but ISPs often impose special rules that make it less than straightforward to run them. On the other hand, Internet service providers (ISPs) manage DSNs as a Web site resource. They routinely issue one or more DSNs for each Web site account with database usage privileges. Therefore, it's often easier to use system, rather than file, DSNs. In any event, system DSNs offer a performance advantage.

After selecting a datasheet source in the database window and choosing File-Export, select Microsoft IIS 1-2(*.htx;*.idc) in the Save As Type list box. Make other selections as you would for an HTML file. Clicking Save opens the HTX/IDC Output Options dialog box (shown in Figure 13-3). You can use the Browse button to select a template that formats the page returning the results from the IDC file. In order to run, the IDC file needs a DSN name on the server. In the Data Source Name text box, enter the name of the DSN that your Webmaster set up for this task. The dialog box also includes user ID and password fields in case the database on the Web server runs with user-level security. (This is very rare, particularly in Internet applications.)

Figure 13-3. The HTX/IDC Output Options dialog box.

The IDC and HTX files complement one another. The IDC file runs a query against an Access database on the Web server. The HTX file uses HTML extensions to format the return set in HTML for viewing by a browser. While you can code the HTX file in an HTML extension, it returns pure HTML to the browser so that any browser can read its results. Because the only way that an IDC file can expose its return set is through an HTML file, this format processes datasheets. However, it is not appropriate for processing forms because the HTML return format does not serve as a good input for most HTML forms.

When you run an IDC/HTX pair from a Web server, browsers reference the IDC file, which must reside in a Web server folder that can run scripts. The IDC file automatically invokes the HTX file, which in turn processes the return set from the IDC file and sends HTML-formatted results back to the browser.

Publishing in ASP format

Like IDC and HTX files, ASP files require a reference to a DSN. In the Export dialog box, select Microsoft Active Server Pages (*.asp) in the Save As Type list box. In the Microsoft Active Server Pages Output Options dialog box, enter a DSN name. Other fields in the dialog box are optional and depend on your formatting preferences and database security settings. (These fields are optional in the IDC/HTX and HTML dialog boxes as well.)

The ASP format differs from the IDC/HTX format in that there is only one file. This ASP file performs the query and formats the result for the browser. The file runs its query and creates its HTML on the server, and then the Web server transports the HTML page back to the browser. The following ASP code writes the datasheet behind the Products form in the Northwind database to a browser:

 <HTML><HEAD> <META HTTP-EQUIV="Content-Type"     CONTENT="text/html;charset=windows-1252"> <TITLE>Products</TITLE> </HEAD><BODY> <% If IsObject(Session("Nwind2000_conn")) Then     Set conn = Session("Nwind2000_conn") Else     Set conn = Server.CreateObject("ADODB.Connection")     conn.open "Nwind2000","",""     Set Session("Nwind2000_conn") = conn End If %> <% If IsObject(Session("Products_rs")) Then     Set rs = Session("Products_rs") Else     sql = "SELECT * FROM [Products]"     Set rs = Server.CreateObject("ADODB.Recordset")     rs.Open sql, conn, 3, 3     If rs.eof Then         rs.AddNew     End If     Set Session("Products_rs") = rs End If %> <TABLE BORDER=1 BGCOLOR=#ffffff CELLSPACING=0 RULES=none> <FONT FACE="Arial" COLOR=#000000><CAPTION><B>Products</B> </CAPTION></FONT> <THEAD><TR> <TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 > <FONT SIZE=2 FACE="Arial" COLOR=#000000>ProductID</FONT></TH> <TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 > <FONT SIZE=2 FACE="Arial" COLOR=#000000>ProductName</FONT></TH> <TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 > <FONT SIZE=2 FACE="Arial" COLOR=#000000>SupplierID</FONT></TH> <TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 > <FONT SIZE=2 FACE="Arial" COLOR=#000000>CategoryID</FONT></TH> <TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 > <FONT SIZE=2 FACE="Arial" COLOR=#000000>QuantityPerUnit</FONT></TH> <TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 > <FONT SIZE=2 FACE="Arial" COLOR=#000000>UnitPrice</FONT></TH> <TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 > <FONT SIZE=2 FACE="Arial" COLOR=#000000>UnitsInStock</FONT></TH> <TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 > <FONT SIZE=2 FACE="Arial" COLOR=#000000>UnitsOnOrder</FONT></TH> <TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 > <FONT SIZE=2 FACE="Arial" COLOR=#000000>ReorderLevel</FONT></TH> <TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 > <FONT SIZE=2 FACE="Arial" COLOR=#000000>Discontinued</FONT></TH> </TR></THEAD> <TBODY> <% On Error Resume Next rs.MoveFirst do while Not rs.eof %> <TR VALIGN=TOP> <TD BORDERCOLOR=#808080 ><B> <FONT SIZE=1 FACE="MS Sans Serif" COLOR=#000000> <%=Server.HTMLEncode(rs.Fields("ProductID").Value)%><BR></FONT> </B></TD> <TD BORDERCOLOR=#808080 > <FONT SIZE=1 FACE="MS Sans Serif" COLOR=#000000> <%=Server.HTMLEncode(rs.Fields("ProductName").Value)%><BR> </FONT></TD> <TD BORDERCOLOR=#808080 > <FONT SIZE=1 FACE="MS Sans Serif" COLOR=#000000> <%=Server.HTMLEncode(rs.Fields("SupplierID").Value)%><BR> </FONT></TD> <TD BORDERCOLOR=#808080 > <FONT SIZE=1 FACE="MS Sans Serif" COLOR=#000000> <%=Server.HTMLEncode(rs.Fields("CategoryID").Value)%><BR> </FONT></TD> <TD BORDERCOLOR=#808080 > <FONT SIZE=1 FACE="MS Sans Serif" COLOR=#000000> <%=Server.HTMLEncode(rs.Fields("QuantityPerUnit").Value)%><BR> </FONT></TD> <TD BORDERCOLOR=#808080  ALIGN=RIGHT> <FONT SIZE=1 FACE="MS Sans Serif" COLOR=#000000> <%=Server.HTMLEncode(rs.Fields("UnitPrice").Value)%><BR> </FONT></TD> <TD BORDERCOLOR=#808080  ALIGN=RIGHT> <FONT SIZE=1 FACE="MS Sans Serif" COLOR=#000000> <%=Server.HTMLEncode(rs.Fields("UnitsInStock").Value)%><BR> </FONT></TD> <TD BORDERCOLOR=#808080  ALIGN=RIGHT> <FONT SIZE=1 FACE="MS Sans Serif" COLOR=#000000> <%=Server.HTMLEncode(rs.Fields("UnitsOnOrder").Value)%><BR> </FONT></TD> <TD BORDERCOLOR=#808080  ALIGN=RIGHT> <FONT SIZE=1 FACE="MS Sans Serif" COLOR=#000000> <%=Server.HTMLEncode(rs.Fields("ReorderLevel").Value)%><BR> </FONT></TD> <TD BORDERCOLOR=#000000  ALIGN=RIGHT><B> <FONT SIZE=2 FACE="System" COLOR=#000000> <%=Server.HTMLEncode(rs.Fields("Discontinued").Value)%><BR> </FONT></B></TD></TR> <% rs.MoveNext loop %> </TBODY><TFOOT></TFOOT> </TABLE></BODY></HTML>

Notice that the code mixes HTML with VBScript. It also uses ADO to create the connection to a data source, extract a recordset, and insert the results into an HTML table. A Do loop transfers the recordset contents to an HTML table. None of the VBScript code stays in the version that the server sends to the browser; the browser receives pure HTML. This makes ASP appropriate for many different kinds of browsers. The versatility of composing HTML on the fly is the cause for a pause when a browser launches an ASP file. Pages with more iterations and field translations require more composing time than those with just a few fields and rows. You should plan your ASP page contents so that they deliver sufficient information in a single page without incurring undue processing delays.

Using HTML Forms

One of the strengths of ASP technology is its support of HTML forms. This is important because all browsers support HTML forms. You can use these forms to gather information from site visitors or return database results in a record layout (as opposed to a datasheet layout). An HTML form consists of one or more controls for displaying or storing information. Typically, the form contains at least one button for submitting the form fields to the Web server. If the form requires a reply, a program on the server can process the form fields and generate a response page.

HTML forms often work like a call-and-response game between a browser and a Web server. The browser user fills in the fields and clicks the Submit button for the form. When the browser sends the form to the server, it transmits form field values and the name of a program on the server that knows what to do with those field values. This is the call part of the game. The Web server sends the form field values to the right processing program on the server. That program can echo the input values, check them for validity, append them to a record source, do a lookup of data from a record source, or more. It typically prepares some kind of return page for the browser. This is the response. ASP files can serve as both the initial form that accepts the call and the response program that replies to the call.

Figure 13-4 shows a pair of HTML forms that illustrate the call-and-response character of HTML form processing. The top form includes a drop-down list box with the names of employees in the Northwind database. A user can select a name, such as Buchanan, and click Get Extension to submit the form to the server. This is the call action. The Web server takes the input form and passes it to telereturn.asp, which reads the entry in the list box, looks up the telephone number for the employee, and writes a page with the information for the browser. The Web server sends the page back to the browser. This is the response action.

Figure 13-4. The HTML form on top calls the Web server and invokes the reply page on the bottom.

Access 2000 does not include built-in wizards for building these kinds of pages with HTML forms, but the process is straightforward. The main challenge is learning the syntax for a few HTML form controls and how to intermix HTML and script, such as VBScript, in an ASP file. The following code is all that the top page in Figure 13-4 requires. The filename for this code is telelookup.asp. Its name appears in the Address list box on the top page.

 <% set cnn1 = server.createobject("ADODB.Connection") cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _     "Data Source=C:\Program Files\Microsoft Office\Office\" & _     "Samples\Northwind.mdb;" set rs = Server.CreateObject("ADODB.Recordset") rs.open "select * from employees", cnn1 %> <html> <head><title>Extension Lookup Window</title></head> <body> <form name=MyForm method=Post action=telereturn.asp> Last name: <select name=DCombo> <% do while not rs.eof %>     <option value=<%=rs.fields("EmployeeID")%>>         <%=rs.Fields("LastName")%> <% rs.MoveNext Loop %> </select> <input type="Submit" value="Get Extension"><br><br> </form></body></html> 

Notice that the script is delimited by <% and %>. The first script segment opens a connection to the Northwind database through OLE DB drivers that do not require a DSN. The first segment also creates a recordset with the information to populate the drop-down list box. Next, some HTML code starts a page and a form on the page. In the form declaration, the code assigns telereturn.asp as the program that processes the form. Just before starting a Do loop, the program declares a drop-down list box with the HTML select keyword. It names the control dcombo. (Names in HTML are not case sensitive.). The Do loop populates the list box with values from the recordset created by the first script segment. The form closes with a Submit button that has the label Get Extension.

The telereturn.asp file also starts by opening a connection and creating a recordset, as shown below. However, this open action has two important differences from the one in telelookup.asp. First, it uses a DSN to help create the connection. This is not necessary, but many legacy ASP files are designed this way. Second, the code reads the value of dcombo and uses it to form the SQL statement for the query that looks up an employee's telephone number.

 <% set rs = Server.CreateObject("ADODB.Recordset") sql = "select * from employees" sql = sql & " where employees.employeeid = " sql = sql & request.form("dcombo") rs.open sql,"DSN=Nwind2000" %> <html> <head><title>Extension Result Window</title></head> <body> <form> First Name: <input TYPE="TEXT" VALUE="     <%=rs.fields("FirstName")%>"> </form> The extension for <%=rs.fields("FirstName")%>&nbsp;     <%=rs.fields("LastName")%>     is <%=rs.fields("Extension")%>.     <a href="telelookup.asp">     Go back to the combo box form for another extension lookup. </a> </body></html> 

After creating a recordset with information for the employee selected in telelookup.asp, telereturn.asp formats a page. The page contains the employee's first name in an HTML text box, a simple HTML string with the name and extension, and a hyperlink for returning control to telelookup.asp. When the Web server passes this page back to the browser, the call-and-response cycle ends.

Using FTP Snapshots with Netscape Browsers

Chapter 6 discussed the snapshot format for reports and showed examples of its capabilities, but it did not mention using snapshots with FTP. When you place snapshots in an FTP folder for a Web server, Netscape browsers can open them remotely at the Web server. The user need not download the file and launch a separate viewer, as is the case with Hypertext Transfer Protocol (HTTP).

When a Netscape browser opens a snapshot of a report from an FTP folder, it automatically creates a new window for the report. This scenario assumes that you have installed the Snapshot Viewer and that the workstation has an association between .snp file types and that viewer. Figure 13-5 shows an excerpt from a snapshot of the Northwind Catalog report. It shows an image, multiple fonts, and other formatting touches, such as dividing lines. The order entry form at the end of the Catalog report also reproduces faithfully.

Figure 13-5. An excerpt from a snapshot report opened with a Netscape browser.

When you save the snapshot file, you simply place it in the FTPROOT folder for the Web server (or another FTP folder). Type ftp://webserver/filename.snp in the Netscape address Location box. Replace webserver with the name of your Web server. (For the samples in this chapter, the Web server is cab2200.) Since the FTP protocol is very popular, you can use this publishing mechanism with many different types of Web servers and Web browsers.