Exporting Tables, Queries, and Forms to Active Server Pages
Active Server Pages (ASPs) play the primary role in Microsoft's strategy to gain for IE and IIS the predominate position in the Internet browser and server markets. ASP uses server-side scripting, OLE DB, and ActiveX Data Objects (ADOs) to generate HTML files from desktop and client/server databases. ADO is an Automation wrapper (interface) for Microsoft's new OLE DB API, which first appeared in late 1996. OLE DB's objective is to provide a simpler mechanism than SQL-oriented ODBC for accessing data stored in any type of row/column format. ADO defines an Automation object, similar in concept to Access's ODBCDirect and Visual Basic's Remote Data Object (RDO). ADO supplies a Recordset object created from a table or query that you can manipulate with Visual Basic Script (VBScript) contained in .asp files stored on the Internet server. VBScript is a simplified subset of VBA 5.0. Jet and ODBCDirect Recordset, and RDO's rdoResultset objects require the full version of VBA 5.0.
▸▸ See "Using ODBCDirect and the Remote Data Object,"
When this book was written, the only OLE DB data provider was Microsoft's "OLE DB Over ODBC," which installs when you set up IIS 3.0 or install the ASP extensions to IIS 2.0. Microsoft has promised native OLE DB data providers for Jet and SQL Server databases, and you can expect third parties to deliver a variety of OLE DB data providers. Until the native OLE DB data provider is available for Jet 3.5 .mdb files, you must use an ODBC data source with OLE DB. The Nwind Web data source, described in the section Creating a System ODBC Data Source by Using Access 97 earlier in this chapter, is used for the ASP examples that follow.
Creating InetPub Subfolders for ASP Files
When an Internet client requests an .asp file from the server, the code in the .asp file executes to create an .htm(l) file that that the browser can display. Thus, the folders that contain .asp files require Execute permission. By default, subfolders of the InetPub Wwwroot folder do not have Execute permission. To create an IIS 3.0 InetPub subfolder in which to store the .asp files you create in the following sections, perform these steps:
1. | Use Windows Explorer to create a subfolder of InetPub, InetPubNwindASP, for the examples that follow. If you are using IIS 3.0 and NTFS, make sure that the anonymous IUSER_SERVERNAME user has at least Read, Write, and Execute permissions for the folder. |
2. | Choose Start, Programs, Microsoft Internet Server, Internet Service Manager to launch Internet Service Manager. |
3. | Double-click the Computer entry for the WWW Service to open the WWW Service Properties for Servername properties sheet. |
4. | Click the Directories tab and click the Add button to open the Directory Properties dialog. Click the Browse button to open the Select Directory dialog and navigate to the InetPubNwindASP folder (see Figure 18.32). Click OK to enter the folder name in the Directory text box. Figure 18.32. Making the InetPubNwindASP folder accessible to Internet or intranet users.
|
5. | Select the Virtual Directory option and type a name for the folder, Northwind for the following examples, in the Alias text box. The URL for the folder is http://servername/northwind; OAKLEAF3 is the name of the server used to create the following figures. ▸▸ See Defining the Client/Server Environment |
6. | Mark the Execute check box to give users permission to execute files contained in this folder (see Figure 18.33). Click OK to add your new folder to IIS 3.0 and close the Directory Properties dialog. Caution If you don't enable Execute permissions, .asp files fail to deliver .htm (l) files to the browser. Depending on the browser you use and your location on the network, you might see the source code for the .asp file or an empty browser window. Figure 18.33. Specifying a virtual directory alias and enabling execution of .asp files in the folder.
|
7. | Select the C:InetPubNwindASP Directory item, mark the Enable Default Document check box, and modify the default entry to Default.html (see Figure 18.34). The Switchboard page created by the Publish to the Web Wizard uses the .html file extension. Figure 18.34. Specifying a default document for the InetPubNwindASP folder.
|
8. | Click the OK button to close the WWW Service Properties for Servername properties sheet, and then close the Internet Service Manager window. |
9. | If you want to use a template with your .asp files, copy the template files to the InetPubNwindASP folder. The examples that follow use the 100.htm, 100.jpg, and Msaccess.jpg files for the template. ◂◂ See Using HTML Templates |
Exporting a Query to an ASP File
Creating a single .asp file is considerably simpler than using .idc/.htx files to create dynamic Web pages that display tabular data. You can export complete tables or a query result set to a single .asp file, which generates the .htm file from the database specified in the ODBC data source. The following example uses the File, Save A s/Export method to create the ASP version of the IDC example described in the section Using HTML Export to Create the Web Pages earlier in this chapter.
◂◂ See Creating a Web Page from a Query
To create the North American Customers.asp file, follow these steps:
1. | Open Northwind.mdb, if necessary, and select the North American Customers query that you created in the preceding chapter, "Exporting Data to World Wide Web Pages." |
2. | Choose File, Save As/Export to open the Save As dialog. Select the To an External File or Database option and click OK to open the Save Query "North American Customers" In dialog. |
3. | Navigate to the InetPubNwindASP folder, and then select Microsoft ActiveX Server (*.asp) from the Save as Type list (see Figure 18.35). Click Export to open the Microsoft ActiveX Server Pages Output Options dialog. Figure 18.35. Saving the North American Customers query in ASP format.
|
4. | If you added a template file to your InetPubNwindASP folder in the last step of the preceding section, type the file name of the template in the HTML Template text box. Type Nwind Web in the Data Source Name text box and http://servername/northwind in the Server URL text box (see Figure 18.36). Figure 18.36. Specifying the template file, ODBC data source, and URL for the North American Customers.asp file.
|
5. | Click OK to export the file to North American Customers.asp and close the dialog. |
6. | Launch IE 3+ and type http://servername/North American Customers.asp in the Address text box to execute the .asp file. The resulting HTML page appears as shown in Figure 18.37. Figure 18.37. The Web page generated by North American Customers.asp.
Note Internet Explorer 3.02 is the browser used to test the .asp pages created in these examples. IE 3.02 provides a number of security features and other minor improvements to the original IE 3.0 version. You can download the current version of IE from http://www. microsoft.com/ie/. |
7. | Choose View, Source to display in Notepad the HTML source generated by the .asp file (see Figure 18.38). The HTML source is essentially identical to that created by the .idc/.htx files you created earlier in the chapter. Spaces ordinarily aren't allowed in URLs, so ASP replaces spaces in generated HTML file names with %20. Figure 18.38. Part of the HTML source for the Web page generated by North American Customers.asp.
|
8. | Use Notepad to open the North American Customers. asp file to display the VBS source code used to create the .htm file (see Figure 18.39). Figure 18.39. Part of the VBS source code contained in North American Customers.asp.
|
Note
The reason for using a fully descriptive query name is to create an appropriate title for the query result set table. You can rename the North American Customers.asp file to NACusts.asp or the like to minimize the length of the URL and avoid including spaces.
Listing 18.6 contains the complete source, including VBScript code, of North American Customers.asp. Server-side VBScript code is enclosed within <%…%> tags. The following three VBScript statements establish a connection to Northwind.mdb using the ActiveX Data Object (ADODB) and the Nwind Web data source:
Set conn = Server.CreateObject("ADODB.Connection")
conn.open "Nwind Web","",""
Set Session("Nwind Web_conn") = conn
The SQL statement in the sql = … lines is the Jet SQL statement of the North American Customers query that opens an ADO Recordset object with the rs.Open sql, conn, 3, 3 VBS statement. The VBA code between the Do While Not rs.EOF and Loop statements populates the cells of the table.
Code Listing 18.6. The HTML Source and VBScript Code of the North American Customers.asp File
<HTML>
<TITLE>North American Customers</TITLE>
<BODY background = 100.jpg>
<%
Param = Request.QueryString("Param")
Data = Request.QueryString("Data")
%>
<%
Session.timeout = 5
If IsObject(Session("Nwind Web_conn")) Then
Set conn = Session("Nwind Web_conn")
Else
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Nwind Web","",""
Set Session("Nwind Web_conn") = conn
End If
%>
<%
sql = "SELECT Customers.CustomerID, Customers.CompanyName,
Customers.City, Customers.Region, Customers.PostalCode,
Customers.Country FROM Customers WHERE (((Customers.Country)='USA'))
OR (((Customers.Country)='Canada')) OR
(((Customers.Country)='Mexico'))"
If cstr(Param) <> "" And cstr(Data) <> "" Then
sql = sql & " And " & cstr(Param) & " = " & cstr(Data)
End If
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open sql, conn, 3, 3
%>
<TABLE BORDER=1 BGCOLOR=#ffffff CELLSPACING=0><FONT FACE="Arial"
COLOR=#000000><CAPTION><B>North American Customers</B></CAPTION>
<THEAD>
<TR>
<TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT SIZE=2
FACE="Arial" COLOR=#000000>Customer ID</FONT></TH>
<TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT SIZE=2
FACE="Arial" COLOR=#000000>Company Name</FONT></TH>
<TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT SIZE=2
FACE="Arial" COLOR=#000000>City</FONT></TH>
<TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT SIZE=2
FACE="Arial" COLOR=#000000>Region</FONT></TH>
<TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT SIZE=2
FACE="Arial" COLOR=#000000>Postal Code</FONT></TH>
<TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT SIZE=2
FACE="Arial" COLOR=#000000>Country</FONT></TH>
</TR>
</THEAD>
<TBODY>
<%
On Error Resume Next
rs.MoveFirst
Do While Not rs.EOF
%>
<TR VALIGN=TOP>
<TD BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial"
COLOR=#000000><%=Server.HTMLEncode(rs.Fields("CustomerID").Value)%>
<BR></FONT></TD>
<TD BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial"
COLOR=#000000><%=Server.HTMLEncode(rs.Fields("CompanyName").Value)%>
<BR></FONT></TD>
<TD BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial"
COLOR=#000000><%=Server.HTMLEncode(rs.Fields("City").Value)%>
<BR></FONT></TD>
<TD BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial"
COLOR=#000000><%=Server.HTMLEncode(rs.Fields("Region").Value)%>
<BR></FONT></TD>
<TD BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial"
COLOR=#000000><%=Server.HTMLEncode(rs.Fields("PostalCode").Value)%>
<BR></FONT></TD>
<TD BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial"
COLOR=#000000><%=Server.HTMLEncode(rs.Fields("Country").Value)%>
<BR></FONT></TD>
</TR>
<%
rs.MoveNext
Loop
%>
</TBODY>
<TFOOT></TFOOT>
</TABLE>
</BODY>
<BR><BR>
<IMG SRC = "msaccess.jpg">
</HTML>
|
If you're familiar with VBA, the similarities between server-side VBS and VBA code in the preceding listing are quite evident. Using .asp files to create dynamic Web pages from tables and queries saves a substantial amount of server disk space compared with exporting a corresponding static Web page.
Exporting Access Forms to ASP Files
Access 97 lets you export existing forms to .asp files. The form export process creates the necessary HTML text boxes and navigation buttons to emulate simple Access forms, including forms with subforms. IE 3.0+ uses the ActiveX HTML Layout Control to create the page design in a Formnamealx.asp file; subforms are implemented by an embedded Web Browser Control. Both Formname.asp and Formnamealx.asp files are required to emulate Access forms.
To create Customers.asp and Customersalx.asp and test the Web-based form, follow these steps:
1. | Select the Customers form and choose File, Save As/Export to open the Save As dialog. Make sure that the To an External File or Database option is selected and click OK to open the Save Form "Customers" In dialog. |
2. | Navigate to the InetPubNwindASP folder, and then select Microsoft ActiveX Server (*.asp) from the Save as Type list (refer to Figure 18.35). Click Export to open the Microsoft ActiveX Server Pages Output Options dialog. |
3. | The default background color of the exported form is gray, the same as the default background color of most browsers, so don't specify a template file. If necessary, type Nwind Web in the Data Source Name text box and type http://servername/northwind in the Server URL text box (refer to Figure 18.36). If you complete this example immediately after completing the example of the prior section, the text boxes of the Microsoft ActiveX Server Pages output Options dialog retain their prior settings. |
4. | Click OK to export the file to Customers.asp and Customersalx.asp and close the dialog. |
5. | Launch IE 3.0+ and type http://servername/Customers.asp in the Address text box to execute the two .asp files. The resulting HTML page appears as shown in Figure 18.40. Figure 18.40. The Web page generated by Customers.asp and Customersalx.asp.
|
6. | Choose View, Source to display in Notepad the HTML source generated by the Customers.asp file (see Figure 18.41). Very little source is required in the Customers(1).htm file because all the action takes place in the ActiveX HTML Layout Control of the Customersalx.asp file identified by the OBJECT ID= and PARAM NAME= tags. Figure 18.41. The Web page generated by Customers.asp and Customersalx.asp.
|
You can edit any of the entries that appear in text boxes and select a country from the drop-down list. Clicking the Commit button sends changes you make to the server, which performs an UPDATE operation on the affected table(s)—only Customers, in this case. Each time you move the record pointer with the navigation buttons, commit changes, or delete or refresh a record, the server resends the modified page to the client. Thus, browsing a table with a Web form is a relatively slow process, even if you're connected to the server via a local area network (LAN). The ActiveX Database Connector (ADC), which wasn't available when Microsoft released Access 97, lets you store "disconnected" ADO Recordset objects on the client, browse and make multiple updates to the Recordset, and then return the changes to the server. ADC eliminates the need for a round-trip to the server each time you update, delete, or add a new record.
Note
Changing the value of the primary key field of a table on which other records rely violates referential integrity rules. If referential integrity is enforced by Access relationships, changes you make to primary key values (such as to the CustomerID field of the Customers table) are ignored. You don't receive an error message, because the VBScript code in Customersalx.asp includes an On Error Resume Next statement.
▸▸ See Handling Runtime Errors
Accommodating VBScript and ADO Limitations
The Publish to the World Wide Web Wizard is competent but not infallible. Automating the conversion of Access objects to .asp format with VBScript code and the HTML Layout Control is a major programming feat. Following are some of the problems you're likely to experience when creating .asp files from your Access tables, queries, or forms:
Currency and percent formatting of Jet fields isn't preserved when exporting to .asp files. To solve this problem, using a query with explicit formatting for a field of the Currency data type, as in UnitPrice: Format([UnitPrice], "Currency"). Alternatively, you can modify the generated VBScript by using the FormatCurrency function. Use the FormatPercent function for percent formatting.
User-defined functions incorporated in query expressions don't export to .asp files. In some cases, you might be able to use VBScript code to emulate your user-defined functions.
Access's user-oriented sorting and filtering features don't migrate to .asp files. You must incorporate sorting (ORDER BY clause) and filtering (WHERE clause criteria) in your graphical query design or SQL statement.
Lookup fields don't work in .asp files. If you want to emulate lookup fields, use an AutoLookup query instead.
Queries that use the Like operator with ? or * wild cards cause an error when you execute the .asp file.
Hyperlink fields require a very complex query expression to be operable in .asp files. You also must modify the VBScript code in the .asp file to make hyperlink fields operable. Avoid exporting queries with Hyperlink fields, if possible.
Subforms might not convert correctly to .asp format. The wizard attempts to emulate subforms with VBScript code. Very simple subforms offer the best chance of proper conversion.
Note
For more details on the limitations of ADO and VBScript when dealing with exported Access objects, and some suggested workarounds, check out http://www.microsoft.com/AccessDev/articles/ASPT&T.HTM.