Project Management

Access and SQL Server datasheets on FrontPage webs

In FrontPage 2000, you can create datasheets for a Web site with a redesigned Export command. Rick Dobson demonstrates manual techniques for publishing static datasheets and shows you how to publish datasheets with ASP technology.

Webmasters and database administrators often need to publish datasheets. A datasheet is the data in a table, the data referenced by a query, or the datasheet view behind a form. Web sites use them for pages containing such items as product catalogs, price sheets, and telephone directories. Access 2000 updates its capabilities and operational features for datasheet publishing by improving its integration with FrontPage 2000. In particular, you now can create datasheets for a Web site with a redesigned Export command on the File menu. This command supports themes by referencing a file as a template.

The new capabilities preserve the ability to publish static and dynamic datasheets. As with the previous Access version, static datasheets publish with an HTML file type, and dynamic publication is possible via Active Server Pages (ASP) and IDC/HTX technology. Since the IDC/HTX approach is obsolete, we’ll focus on the other two formats (namely, HTML and ASP).

In this article, we’ll demonstrate manual techniques for publishing static datasheets. We’ll also show you how to publish datasheets with ASP technology. We’ll review the output from the ASP approach to show how you can alter the automatically generated ASP script. Finally, we’ll demonstrate how to use VBA to automate the production of multiple web pages, each with its own datasheet.

Manually publishing static datasheets
Publishing a static datasheet allows you to fix its contents. Essentially, you take a snapshot of the datasheet and publish the snapshot. No matter how much the datasheet changes after the snapshot, visitors to the site will view the datasheet as it appeared when you took the snapshot. The contents of the datasheet don’t update until you publish a new snapshot.

There are three basic styles of publishing static datasheets. All deliver a table on a web page, but they format the table or page differently. Suppose that you want to view some data quickly or generate a plain table so that you can add your own custom formatting. To publish a datasheet as a normal, unformatted table on a web page, first highlight a table, query, or form in the Database window. Then, choose File | Export to open the Export Table dialog box.

You need to make two selections and an entry before clicking Save to publish your web page in an unformatted style. First, select a folder to hold your new web page in the Save In drop-down box. (You can use a Microsoft Internet Information Server instead of the Personal Web Manager server if you want.) Second, select HTML Documents (*.html;*htm) from the Save As Type drop-down box. This option denotes a static web page. Third, enter a name for the web page file in the File Name box. Access automatically appends an HTML extension after your cursor leaves the box. Click Save to create (or save over) a web page with an HTML extension in the target folder.

If you want a formatted table, follow the same steps as above but also select the Save Formatted check box. Clicking Save opens the HTML Output Options dialog box. If you want just Access formatting, leave the HTML Template box blank. If you want formatting in the style of another web page as well as Access formatting, click Browse and navigate to that page. In either case, click OK to publish your formatted table to a web page. You can make the web page display automatically after publishing by selecting the Autostart check box in the Export Table dialog box. It may be necessary to click Refresh in the browser to view new formatting for the page.

Manually publishing dynamic datasheets
Publishing an Access datasheet in ASP format makes the page dynamic. If you think of a static page as a snapshot, then an ASP file resembles time-lapsed photography without the requirement for a fixed interval between snapshots. Every time a user opens a datasheet's ASP file with a browser, the Web server takes a new snapshot and sends it to the browser. Different snapshots at various points in time reveal values in the datasheet as of the time of that snapshot.

The File menu’s Export command for generating an ASP file works similarly to the process for generating an HTML file, but you need one additional bit of information: the name of the system DSN for the database containing the datasheet. Your ISP, Web server administrator, or database administrator will dispense this information to a Webmaster who is responsible for administering a particular site on a server.

After acquiring the DSN name for the database containing a datasheet, you can publish the datasheet dynamically with the Access menu. Select the datasheet's table, query, or form in the database container. Then, choose File | Export to open the dialog box.

Once you have the DSN name for the database containing a datasheet, select the datasheet's table, query, or form in the Database window. Make your selections and entry as you would for a static page but select Microsoft Active Server Pages (*.asp) in the Save As Type drop-down box. Completing this selection disables the Save Formatted and Autostart check boxes. Clicking Save opens the Microsoft Active Server Pages Output Options dialog box. In the Data Source Name text box, enter the DSN name for the data source containing the datasheet. You can enter the name or browse to a template file with formatting for the web page. (This step is optional; if you don’t choose to follow it, the datasheet will appear on the page with just standard Access formatting.)

The dynamic datasheet constructed by our ASP file will use the fpnwind DSN. This DSN points to the fpnwind.mdb file. In addition, it will format its newly constructed web pages with the themepageforsite.htm file in the Web site's root folder (wwwroot). The other dialog boxes aren’t required for typical datasheet publishing tasks.

Analyzing and editing an ASP file
It’s not essential that you understand the code inside an ASP file because the File menu’s Export command generates it automatically. However, learning the layout of the file can help you build your own ASP files, and this knowledge is particularly beneficial for working with HTML forms on web pages. In addition, understanding the design of the ASP script on a web page that’s built with the Export command will enable you to modify the page's behavior.

Listing A shows the code in the ASP file, which is generated for the Shippers table and is based on the selections that we made. For example, the themepageforsite.htm file has a blends theme, and the meta element for Microsoft Theme in the file's ninth line assigns this theme to the datasheet for the datasheet's web page. This designation creates a blue-green fade on the left edge of the page.
<html>
<head>
<meta http-equiv="Content-Type" content="text/html;
charset=windows-1252">
<meta name="GENERATOR" content="Microsoft FrontPage 4.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<title>Shippers</title>
<meta name="Microsoft Theme" content="blends 1001">
</head>


<body><%
If IsObject(Session("fpnwind_conn")) Then
Set conn = Session("fpnwind_conn")
Else
Set conn = Server.CreateObject("ADODB.Connection")
conn.open "fpnwind","",""
Set Session("fpnwind_conn") = conn
End If
%>
<%
If IsObject(Session("Shippers_rs")) Then
Set rs = Session("Shippers_rs")
Else
sql = "SELECT * FROM [Shippers]"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open sql, conn, 3, 3
If rs.eof Then
rs.AddNew
End If
Set Session("Shippers_rs") = rs
End If
%>
<TABLE BORDER=1 BGCOLOR=#ffffff CELLSPACING=0>
<FONT FACE="Arial" COLOR=#000000><CAPTION>
<B>Shippers</B></CAPTION></FONT>


<THEAD>
<TR>
<TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 >
<FONT SIZE=2 FACE="Arial" COLOR=#000000>
Shipper 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>
Phone</FONT></TH>


</TR>
</THEAD>
<TBODY>
<%
On Error Resume Next
rs.MoveFirst
do while Not rs.eof
%>
<TR VALIGN=TOP>
<TD BORDERCOLOR=#c0c0c0 ALIGN=RIGHT>
<FONT SIZE=2 FACE="Arial" COLOR=#000000>
<%=Server.HTMLEncode(rs.Fields("ShipperID").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("Phone").Value)%>
<BR></FONT></TD>


</TR>
<%
rs.MoveNext
loop%>
</TBODY>
<TFOOT></TFOOT>
</TABLE></body>


</html>


After the preliminary elements appear between the HTML and body elements, a VBScript block of code creates a connection to the database that’s denoted by the fpnwind DSN. In this case, it’s an Access database named fpnwind.mdb in the fpdb folder on the FrontPage site. You can use the FrontPage Import command on the File menu to transfer the database from a remote workstation to the Web server.

By storing an ADO Connection object within a Session object, the application preserves the connection for the session's duration. It’s not essential, but it can be valuable when users typically make multiple queries against the same database within a single session. Notice that the code uses a standard CreateObject function (to instantiate the Connection object) and the Open method (to point the object at the fpnwind database). The fpnwind DSN contains the information that the Open method requires in order to open the database. This information can change, depending on the database. For example, SQL Server databases require different types of information.

A second Session object references the recordset that points at the Shippers table in the fpnwind database. Again, the ASP script uses a CreateObject function to instantiate an object—the Recordset object in this case—and an Open method to expose the contents of the data source for the recordset. The code for opening the connection and recordset reflects standard ADO coding practice. It’s easy to read because, by default, ASP script is in VBScript, which is a subset of VB.

After the VBScript for making the data available concludes, a block of HTML code specifies the table and writes its heading on the web page. Then, intermixed HTML and VBScript code write out the table one row at a time. The Do loop for passing through the recordset should be familiar to any VBA programmer. The HTML layout may be less familiar to Office developers.

The connection string for the Open method of the Connection object relies on a DSN. If you don’t have a DSN name conveniently available, you still can reference the database with OLEDB providers. When working from an Internet site that’s hosted by an ISP, you’ll often not know the root folder for specifying the path to your database. In this situation, you can use the Mappath method of the ASP Server object to return the directory structure for a virtual root. The following script can replace the Open method in Listing A:
conn.open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.mappath("/") & _
"/fpdb/fpnwind.mdb;Mode=Share Deny None"


The alternate version of the Open method illustrates the syntax for applying the Mappath method in the context of a connection string that uses the Jet 4.0 OLEDB provider. This syntax points to the same database in the same place as the sample in Listing A did, but its format is much more general. First, when relying on a connection string that includes an OLEDB driver, you eliminate the need for a DSN. Second, you don’t need to know the root path to a Web site on a server.

Listing B includes a more radical modification of the ASP script in Listing A. It changes the recordset from one that points at the Shippers table in the fpnwind database to one that points at the authors tables in the pubs database. The pubs database is one of the sample databases that ship with SQL Server. Therefore, this alteration changes not only the database name, but also the type of server. In fact, it changes the database type and its location because the database server resides on a different computer (cab2200) from the Web server (cabxli).
<%
If IsObject(Session("pubs_conn")) Then
Set conn = Session("pubs_conn")
Else
Set conn = Server.CreateObject("ADODB.Connection")
conn.open "Provider=sqloledb;data source = cab2200;" & _
"user id = sa; initial catalog =pubs"
Set Session("pubs_conn") = conn
End If
%>
<%
If IsObject(Session("authors_rs")) Then
Set rs = Session("authors_rs")
Else
sql = "SELECT * FROM authors"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open sql, conn, 3, 3
If rs.eof Then
rs.AddNew
End If
Set Session("authors_rs") = rs
End If
%>


The sample in Listing B demonstrates the essential changes for swapping the database and recordset. You’ll require additional minor changes elsewhere in the ASP script to reference the proper field names for the authors table. The Open method syntax for the Connection object uses an OLEDB provider. The SQL Server provider's name is sqloledb. Specify the database server's name as the data source parameter. Assign the database's name to the initial catalog parameter. The sample script designates sa as the user id, but it doesn’t include a password parameter because the password is blank for this demonstration. Of course, in production environments, you should use a password.

Publishing multiple datasheets with VBA
The manual techniques for creating datasheets are easy. However, if you have many datasheets to publish or you need to republish multiple static datasheets on a regular basis (such as every week), then an automatic approach is better. The OutputTo method for the DoCmd object provides the functionality to automate the tasks of publishing datasheets.

When publishing datasheets, you’ll often base them on queries rather than on tables because doing so permits the exposing of just a select set of columns and rows from a table. The OutputTo method is very flexible. It will handle all kinds of output chores from Access, and it has a list of arguments and intrinsic constants to help it facilitate these objectives. You can use the method to publish datasheets, both statically and dynamically. The sample in Listing C illustrates how to accomplish the task statically by publishing datasheets on web pages with an HTML extension.
Sub QueriesToHTML()
Dim rst1 As ADODB.Recordset
Dim strObjectName As String
Dim strOutputfile As String
Dim strTemplatefile As String


'Set reference to table of queries for
'conversion to HTML
Set rst1 = New ADODB.Recordset
rst1.Open "tblQueryToHTML", _
CurrentProject.Connection


'Loop through table
Do Until rst1.EOF


'Set strings to field values
strObjectName = rst1.Fields(0)
strOutputfile = rst1.Fields(1)
strTemplatefile = rst1.Fields(2)


'Write query to designated HTML file in
'field 1 formatted according to file in field 2
DoCmd.OutputTo acOutputQuery, _
strObjectName, acFormatHTML, _
strOutputfile, False, strTemplatefile


rst1.MoveNext
Loop


End Sub


The sample in Listing C uses tblQueryToHTML to determine the names and number of queries to publish. You can alter the number and identity of queries that the procedure publishes by altering the content of the table. The procedure starts by opening a recordset that’s based on the table. Then, it starts a Do loop, which passes through each record in the table. It assigns the first, second, and third columns of each row to three string variables. The procedure uses these variables, along with intrinsic constants, to set the arguments for the OutputTo method on each pass through the loop.

The argument list for the OutputTo method starts by designating the datasheet source as a query. (Other intrinsic constants are available for tables and forms.) The second argument designates the query name as one of the string variables. The third argument uses another intrinsic constant to specify the type of output—namely, an HTML file. The fourth argument uses another string variable to indicate the path for saving the datasheet. The fifth argument is a Boolean False. It tells Access to save the datasheet without opening it in a browser. The final argument specifies a template file for the published datasheet with a string variable.

Conclusion
In this article, we explained and demonstrated techniques for publishing datasheets statically and dynamically from Access 2000 to FrontPage 2000 web sites. In addition to showing manual and programmatic means of publishing datasheets, we covered methods for developing your own custom ASP scripts. One sample reveals how to modify an ASP script for publishing an Access datasheet to one that publishes a SQL Server datasheet.

Rick Dobson, Ph.D., and his wife operate a development and training consultancy. Rick is the author of Programming Microsoft Access 2000 for Microsoft Press. He presented training sessions and seminars on Access and web development topics in Australia, Canada, the United Kingdom, and throughout the United States. Rick is a Microsoft Certified Professional and a Microsoft Certified Trainer. His consultancy markets a collection of Access 2000 training kits. You can reach Rick at either of the two Web sites that his practice maintains (http://www.programmingmsaccess.com and http://www.cabinc.net/ ).

The authors and editors have taken care in preparation of the content contained herein, but make no expressed or implied warranty of any kind and assume no responsibility for errors or omissions. No liability is assumed for any damages. Always have a verified backup before making any changes.
0 comments

Editor's Picks