Creating Intranet and Internet Solutions with MS Access 97

Archived content. No warranty is made as to technical accuracy. Content may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

Presented at Tech-Ed 97
Paul Litwin
Senior Consultant
MCW Technologies

Paul Litwin is a senior consultant with MCW Technologies focusing on application development employing Microsoft Access, Visual Basic, Microsoft Office, SQL Server, Visual Basic Script and Microsoft Internet technologies. He's the editor of Smart Access, a monthly newsletter for Microsoft Access developers (www.pinpub.com), and has written numerous articles and reviews for various publications, including Smart Access,Visual Basic Programmer's Journal,Visual Basic Developer, and PC World. Paul is a Microsoft Access MVP and a section leader on the VBPJ CompuServe forum and Windows Developer's Exchange (www.windx.com). He recently completed work on Access 97 Developer's Handbook and has authored several books on Microsoft Access 2 and Microsoft Access 95. Paul trains developers for Application Developers Training Company and is a regular speaker at conferences

This talk has been adapted, with permission, from Access 97 Developer's Handbook by Paul Litwin, Ken Getz, and Mike Gilbert (Sybex 1997). Copyright© 1997 Sybex Inc.

Email: plitwin@mcwtech.com

On This Page

Introduction
What Are Hyperlinks?
Where to Find the Links
Using Hyperlinks for Intra-database Navigation
Controlling Hyperlinks with Visual Basic for Applications
Using the Microsoft Web Browser Control
The Publish to the Web Wizard
Publishing Static HTML
Publishing Internet Database Connector Pages
Publishing Active Pages
Recognizing Microsoft Access' Limits as an Internet Data Server
Back to the Stone Age?
Conclusion
Appendix

Introduction

Unless you've been living under a rock lately, you've probably run across a phenomenon called the Internet. This pervasive global network along with its associated protocols and standards has turned the computer industry on its ear. One of the biggest and most exciting facets of the Internet is the World Wide Web (Web). The Web is the hypertext and multimedia side of the Internet. Using a relatively simple piece of software called a browser, you can browse the Web for an almost unlimited amount of easily accessible information.

While publishing on the Web is a great idea if you want to publicly advertise your services or make some information available to millions of users, many businesses are also discovering that they can apply the protocols and standards of the Internet, and the elegant simplicity of the Web browser, to private TCP/IP-based networks called intranets.

The Microsoft Access 97 database includes a host of new Internet and intranet features. In general, these new features fall into one of two areas:

• Browsing

• Publishing

The browsing (or Internet client) features allow you to insert hyperlinks into your tables, forms, and reports that allow users to jump to Internet or intranet documents, as well as Microsoft Office documents and local files.

The publishing (or Internet server) features allow you to make data in your Microsoft Access databases available to other users over the Internet or your corporate intranet.

What Are Hyperlinks?

To understand how Microsoft Access stores and uses hyperlinks, you need to know a little about what they are and how they're formed. Hyperlinks allow you to navigate to other documents both on and off the Web using Universal Resource Locators (URLs). URLs support Web addresses (such as the ever-popular https://www.microsoft.com/), as well as file locations on your hard disk. Normally you activate, or follow, a hyperlink by clicking its text with your mouse. When a browser application follows a link, it locates the document at the given URL and processes it. Web browsers such as Microsoft Internet Explorer and Netscape Navigator use HTML (Hypertext Markup Language) to display formatted text and graphics. Microsoft Office 97 applications have special provisions for navigating to documents created by other Microsoft Office applications, launching the appropriate program and displaying the file.

A hyperlink actually consists of a number of components:

• Address: Specifies the path to an object, document, Web page, or other destination.

• Display text: Optional text that Microsoft Access displays to the user at the hyperlink location. If you don't supply display text, Microsoft Access displays the address instead.

• Subaddress: A particular location in a document specified by the address.

The following table lists some of the types of hyperlink addresses and subaddresses Microsoft Access supports. This table is not meant to be comprehensive. In addition to the protocols noted there, hyperlinks can contain references to mailto:, gopher:, news:, nntp:, telnet:, and other addresses.

Hyperlink Type	Address	Subaddress
Microsoft Access	Path to a Microsoft Access database	Name of form or report
Microsoft Excel	Path to a Microsoft Excel spreadsheet	Sheet name and range
Microsoft PowerPoint	Path to a Microsoft PowerPoint presentation graphics program	Slide number
Microsoft Word	Path to a Microsoft Word word-processing document	Name of a bookmark in the document
World Wide Web	URL to a Web page	An anchor on the HTML page*
FTP file	URL to an FTP file	Not supported
Local file of registered type	Path to a file on a local machine or on a LAN	Not supported

* A bug in Internet Explorer 3.0 prevents the subaddress of a hyperlink from working properly with Web page anchors; Netscape Navigator 3.0, however, has no problem with them.

Microsoft Access stores hyperlinks as plain text, with each component separated from the others by pound symbols (#). The general format is

DisplayText#Address#Subaddress

DisplayText is optional, but you must specify either an address or a subaddress. If you don't include a DisplayText component, Microsoft Access displays the address (or the subaddress, if no address is specified). The following table lists some hyperlink examples, including the text Microsoft Access displays to the user.

Hyperlink	Description	Microsoft Access Displays
https://www.microsoft.com	Link to the default HTML document at https://www.microsoft.com	https://www.microsoft.com
Microsoft's Home Page#https://www.microsoft.com	Link to the default HTML document at https://www.microsoft.com	Microsoft's Home Page
Sales Figures#\\salesserver\data\ sales.xls#B5	Link to cell B5 on the Last Quarter Sales Figures worksheet in the sales.xls workbook on the salesserver server	Sales Figures
Main Menu#file://c:\My Documents\MyApp.mdb# Form frmMain*	Link to the frmMain form in C:\My Documents\MyApp.mdb	Main Menu
##Form frmOrder	Link to the frmMain form in the current database	Form frmOrder

* the file:// protocol designator is optional.

Where to Find the Links

You'll find hyperlinks in Microsoft Access in three places:

• Stored in tables in fields of the Hyperlink data type

• Embedded in special properties of command button, label, or image controls of forms and printed reports

• In Microsoft Visual Basic for Applications code

Field-based Hyperlinks

Microsoft has added a special Hyperlink data type to Jet tables so you can store hyperlinks in a table just as you can normal text. The difference is that when Microsoft Access displays the hyperlink—on a datasheet or in a bound control on a form—you can click it with your mouse to follow the hyperlink.

The Off304.mdb database contains a form, frmHyperlinks, that provides several examples of using hyperlinks on forms. If you click the Field Links tab of this form, you'll see an embedded subform bound to the tblHyperlink1 table (see Figure 1). If you click a Web-based hyperlink, Microsoft Access launches your default browser, bringing up the Web page associated with the hyperlink, as shown in Figure 2 in Netscape Navigator.

Figure 1: : Various types of hyperlinks are displayed on the Field Links tab of the frmHyperlinks form.

Figure 2: : Result of clicking a Web hyperlink when using Netscape Navigator as your default browser.

You can enter a hyperlink into a table by typing it directly (using the syntax described earlier in this paper) or by using the Edit Hyperlink dialog box shown in Figure 3.

Figure 3: : The Edit Hyperlink dialog box lets you build hyperlinks interactively.

Note: When inserting a hyperlink into a Null field, you actually use the Insert Hyperlink dialog box; thereafter you use the Edit Hyperlink dialog box to edit the hyperlink.

You use the Link to file or URL combo box on the Edit Hyperlink dialog box to enter the address portion of a hyperlink. Here you can enter a URL directly; select one from your browser's history list using the drop-down list; or click the Browse button to choose a file on your hard drive, a network share, or an Internet FTP (File Transfer Protocol) site.

Use the "Named location in file (optional)" text box to enter the optional subaddress portion of the hyperlink. If you're establishing a link to an object in a Microsoft Access database, you can click the Browse button to select an object from the database.

You can't enter the DisplayText portion of a hyperlink using the Edit Hyperlink dialog box. You can, however, right-click the field and select Hyperlink|Display Text from the shortcut menu to enter display text for the link (see Figure 4).

Figure 4: : Using the shortcut menu, you can enter display text for a hyperlink.

Note: Underneath the covers, Jet stores hyperlink data internally as a memo field with a special bit mask (dbHyperlinkField, value 32768) on the field's Attributes property. Hyperlinks are really a function of how Microsoft Access interprets these special memo fields. In fact, if you access hyperlink fields from outside of Microsoft Access—for example, from the Visual Basic programming system —you will get memo data with no special hyperlinking capabilities.

Control-based Hyperlinks

Microsoft Access forms support hyperlinks in several places. First, you can use text box, combo box, and list box controls bound to fields with a Hyperlink data type. You can also associate hyperlinks with the following types of unbound controls:

• Labels

• Command buttons

• Image controls

Note: Microsoft Access includes limited support for hyperlinks in list box controls. The hyperlinks appear correctly in list boxes and can be manipulated programmatically, but users can't follow the hyperlinks from the user interface (UI).

For example, the Static Links tab on frmHyperlinks contains the three types of unbound controls that can contain hyperlinks (see Figure 5).

Figure 5: : You can associate command button, label, and image controls with hyperlinks.

You create control-based hyperlinks by entering each of the hyperlink parts into three different properties of the control:

Hyperlink Part	Control Property
Address	HyperlinkAddress
Subaddress	HyperlinkSubAddress
DisplayText	Caption

The image control doesn't have a Caption property; you must use the image itself for this purpose.

As with field-based links in tables, you can use the Edit Hyperlink dialog box to help create the hyperlink. Click the Build button to the right of either the HyperlinkAddress or the HyperlinkSubAddress property on the control's property sheet to bring up this dialog box.

Using Hyperlinks for Intra-database Navigation

While you might not need to link your workgroup's reporting system to the World Wide Web, you may wish to use hyperlinks to navigate within a database. Using just the subaddress portion of a hyperlink, you can jump from one database form or report to another.

By combining this technique with another new feature of Microsoft Access 97, lightweight forms, you can create user interfaces without any code or macros. Lightweight forms are forms with no Microsoft Visual Basic for Applications class module associated with them. Since Microsoft Access does not need to load Visual Basic for Applications when opening the form, it opens the form more quickly than a form that has Visual Basic for Applications code behind it. Hyperlinks don't need Visual Basic for Applications and thus are great for use with lightweight forms.

frmLWSwitchboard, shown in Figure 6, is a switchboard form that lets you navigate among several forms and reports in the Off304.mdb database. This lightweight form was constructed using no Visual Basic for Applications code or macros; thus it loads more quickly than the equivalent code-based switchboard form.

Figure 6: : This lightweight switchboard form contains no code or macros but is fully functional.

All Microsoft Access forms start out as lightweight. Microsoft Access adds a class module only if you try to insert Visual Basic for Applications code. You can make a heavyweight form lightweight by setting its HasModule property to No in Design view. This, however, destroys any existing Visual Basic for Applications code in the form's module.

Controlling Hyperlinks with Visual Basic for Applications

As mentioned earlier in the paper, you can set the HyperlinkAddress and HyperlinkSubAddress properties of a control using Visual Basic for Applications code. In addition, you can manipulate hyperlinks as objects. Hyperlink-based controls (unbound label, command button, and image controls; text box, combo box, and list box controls that have been bound to Hyperlink-type fields) have an additional property, HyperLink, that doesn't appear on the controls' property sheet. You use this property to establish a reference to a Hyperlink object. The methods and properties of the Hyperlink object are summarized in the following table.

Type	Property/Method	Description
Property	Address	Address component of the hyperlink
	SubAddress	Subaddress component of the hyperlink
Method	AddToFavorites	Adds the hyperlink to the favorites list
	Follow	Jumps to the hyperlink using the default browser

The Address and Subaddress Properties

The following code prints the hyperlink information for a text box named txtLink to the Debug window using the Hyperlink object properties:

With Me!txtLink.Hyperlink
    Debug.Print .Address
    Debug.Print .SubAddress
End With

The AddToFavorites Method

As you can probably guess from its name, the AddToFavorites method adds the hyperlink to your browser's list of favorite sites. Its syntax is quite simple because it has no parameters:

Hyperlink.AddToFavorites

For example, to add the hyperlink stored in the txtLink text box control to your browser's favorites list, you might use the following code:

Me!txtLink.Hyperlink.AddToFavorites

The Follow and FollowHyperlink Methods

The Follow method links directly to a specified hyperlink. It takes several parameters, as shown here:

Hyperlink.Follow [newwindow], [addhistory], [extrainfo], [method], [headerInfo)

The parameters are described in the following table.

Parameter	Description	Default Value
newwindow	Boolean value that, when set to True, opens the document in a new window	False
addhistory	Boolean value that, when set to True, adds the hyperlink to the History folder	True
extrainfo	String or byte array that specifies additional information about the hyperlink. You can use this to specify a search parameter for an IDC or ASP file.	Null
method	Integer that specifies the format of the extrainfo argument. Can be msoMethodGet, for a string argument that is appended to the address, or msoMethodPost, for a string or byte array that is posted to the page	Null if extrainfo is Null; msoMethodGet for non-null extrainfo values
headerinfo	String that specifies additional HTTP header text that is passed to the browser	Zero-length string

The FollowHyperLink method of the Application object is very similar to the Follow method of the Hyperlink object. With the FollowHyperLink method, however, you don't need to establish a reference to a Hyperlink object. Instead you can use FollowHyperLink to jump to any arbitrary address. Its syntax is shown here:

[Application.]FollowHyperLink address, [subaddress], [newwindow], 
[addhistory], [extrainfo], [method], [headerInfo]

where address and subaddress are the address and subaddress parts of the hyperlink. The rest of the parameters are the same as for the Follow method.

Creating a Web Search Form Using the ExtraInfo and Method Parameters

The frmWebSearch form in the sample database illustrates how you can use the extrainfo and method parameters of the FollowHyperlink method of the Application object to pass search parameters to a Web page. This form lets you enter a string to search for on the Web using one of three popular search engine sites: Alta Vista, Yahoo, or Excite (see Figure 7). If you enter a search string like that shown in Figure 7, the FollowHyperlink method will open your default browser and pass it the hyperlink address and extrainfo parameters, in this case displaying the results of the search, as shown in Figure 8.

Figure 7: : When you click the Perform Search button on frmWebSearch, code behind the button uses the FollowHyperlink method to navigate to the selected Web search site and perform a search on the entered search string.

Figure 8: : Result of the search specified in Figure 7.

The code attached to the Click event of the cmdSearch button is shown in the following listing. The subroutine begins by replacing spaces in the search string with a plus sign (+) using the adhReplaceAll function. The subroutine then uses the FollowHyperlink method of the Application object, passing it the address of the selected Web search engine. The extrainfo parameter is formatted in the correct syntax for the particular search engine. This varies from a simple "p=searchstring" for the Yahoo search engine to more complex strings for the AltaVista and Excite sites. (We determined the correct strings by performing searches using our browser and observing the address passed to the search page.) In addition, the method parameter is set to msoMethodGet.

Private Sub cmdSearch_Click()
    Dim varSearch As Variant
    Const adhcSiteAltaVista = 1
    Const adhcSiteYahoo = 2
    Const adhcSiteExcite = 3
    varSearch = adhReplaceAll(Me!txtSearch, " ", "+")
    Select Case Me!optSite
    Case adhcSiteAltaVista
        Application.FollowHyperlink _
         Address:="https://altavista.digital.com/cgi-bin/query", _
         ExtraInfo:="pg=q&what=Web&fmt=.&q=" & _
         varSearch, Method:=msoMethodGet, _
         AddHistory:=True
    Case adhcSiteYahoo
        Application.FollowHyperlink _
         Address:="https://search.yahoo.com/bin/search", _
         ExtraInfo:="p=" & varSearch, _
         Method:=msoMethodGet, _
         AddHistory:=True
    Case adhcSiteExcite
        Application.FollowHyperlink _
         Address:="https://excite.com/search.gw", _
         ExtraInfo:="search=" & varSearch & _
         "&collection=Web&searchButton.x=11&searchButton.y=8", _
         Method:=msoMethodGet, _
         AddHistory:=True
    End Select
End Sub

You can use the extrainfo and method parameters to pass information to common gateway interface (CGI), Internet Database Connector (IDC), or Active Server Pages (ASP) pages.

The HyperlinkPart Function

The HyperlinkPart function accepts a complete hyperlink and returns a specified part of it, such as the display text or subaddress. Its syntax is shown here:

strReturn = HyperlinkPart(hyperlink, [part])

Part, which defaults to acDisplayedValue, can be any of the following constants:

acAddress
acDisplayText
acDisplayedValue
acSubAddress

Using the Microsoft Web Browser Control

When you click a Web-based hyperlink or execute code that employs the Follow or FollowHyperlink method, Microsoft Access starts up your default Web browser and passes it the necessary information to display the desired Web page. This works well in many situations, but sometimes it would be nice if you could browse Web pages in place on a form. You can accomplish this by using a Web ActiveX control such as Microsoft's Web browser control (WebBrowser). The Microsoft control is installed automatically when you install Internet Explorer 3.0 or later on your machine.

WebBrowser has a couple of idiosyncrasies:

• Unlike most other ActiveX controls, its properties (except for the TopLevelContainer and Busy properties) are not merged with the Microsoft Access property sheet or exposed through a custom property sheet.

• Help for the control does not come with Internet Explorer.

Fortunately, a Help file is included in the Valupack\Access\Webhelp folder of the Microsoft Office 97 installation CD-ROM. (If you can't locate your CD-ROM, you can also find a copy at https://www.microsoft.com/intdev/sdk/docs/iexplore/default.htm). In addition, you can find the control's event properties by opening the form's Visual Basic for Applications module and selecting the control using the Object drop-down list.

The sample database contains an example of using the WebBrowser control in a form named frmWebBrowser (see Figure 9). This Microsoft Access–based Web browser form, complete with search capabilities, was created with surprisingly little code.

Figure 9: : You can use the Microsoft Web browser control to create a Web browser embedded in a Microsoft Access form.

The Publish to the Web Wizard

Microsoft Access 97 includes a wizard for publishing your Microsoft Access data on the World Wide Web. You invoke the Publish to the Web Wizard by selecting the File|Save As HTML command. You can publish data in three formats:

• Static HTML

• Dynamic Internet Database Connector (IDC) pages

• Dynamic Active Server Pages (ASP)

You select the output format on page 4 of the wizard. If you select multiple objects to publish, you can select different output formats for each different object.

You can publish table and query datasheets, as well as the record source for forms and reports, using the static HTML format. You can also elect to produce dynamic Web pages using either IDC pages or Active Server Pages.

The three publishing formats are contrasted in the following table. As you can see from this table, the dynamic formats work only with certain servers and browsers, while the static format works with all servers and any browser that supports tables (virtually all browsers). We discuss each of the output formats in more detail in the following sections.

Format	Wizard Output	Servers Supported	Browsers Supported	Comments
Static HTML	HTML files	All	All	Simple, standard; doesn't require an ODBC connection to a database.
IDC pages	IDC and HTX files	Microsoft IIS 1.x/2.0 (running Windows NT 3.1 or 4.0), Microsoft Peer Web Services (Windows NT 4.0), or Microsoft Personal Web Server (Windows 95)	All	Requires IIS, Peer Web Services, or Personal Web Server with an ODBC connection to your database; data is dynamic but read-only.
Active Server Pages	ASP files	Microsoft IIS 3.0 (Windows NT 4.0), Microsoft Peer Web Services (Windows NT 4.0), or Microsoft Personal Web Server (Windows 95)	Internet Explorer 3.0. (For published forms you'll also need the ActiveX layout control, which you can download from www.microsoft.com.)	Requires IIS 3.0, Peer Web Services, or Personal Web Server; data is dynamic and can be edited (forms only)

Publishing Static HTML

The static HTML format is a good choice anytime you wish to create a static table of data that will be updated infrequently. Microsoft Access creates one or more pages with the data laid out in HTML tables. The user will not be able to query the database using this format or update the data. This format is much like the results of a printed report and represents a snapshot of the data at the time it was published.

For example, if you publish the tblMenu table from the Off304.mdb database, you'll produce an HTML page that looks like the page shown in Figure 10.

Figure 10: : Result of publishing the tblMenu table as a static HTML page

Of course, you can easily doctor up the pages the wizard produces. For example, you might take the output shown in Figure 10 and alter the title, add a background image, and so forth. The wizard is responsible for outputting the data; it's up to you to make it look the way you want. Fortunately, the wizard supports templates you can use to apply a standard look to all your published pages.

The static HTML format doesn't require any special Internet server, nor does it require a special browser. This is a big advantage. On the other hand, you'll have to republish the data anytime you wish to update the page.

Tip If you publish hyperlink fields using the static HTML format, Microsoft Access creates hyperlinks for the field on the generated Web page. (This feature is not supported by the IDC and ASP formats.)

Publishing Internet Database Connector Pages

The Internet Database Connector (IDC) format is a good choice if you need to produce dynamic tables that always contain the latest information. When you choose the IDC (or ASP) format, you're asked to provide a data source name (DSN) and an optional user name and password (see Figure 11). Don't worry if you haven't created the data source yet; the wizard won't actually look for the data source at this time, so you can create it later.

Figure 11: : This page of the wizard requests information on the data source the Internet server will use to query your Microsoft Access database at run time.

You create a data source using the Open Database Connectivity (ODBC) Data Source Administrator program (also called the ODBC Driver Manager). You need to create the data source on the server machine that will be hosting your Web site. In addition, the data source must be a system data source, not a user data source. A system data source is a data source that's available to all users systemwide, not just the user who created it.

Obviously, if the data you are publishing is stored in a Microsoft Access database, you'll need to create a system data source that uses the Microsoft Access ODBC driver (and this means you'll need to install the Microsoft Access driver on your server if it's not already there).

For IDC pages, the wizard doesn't produce an HTML file. Instead, the wizard creates two files your browser never sees:

• Internet Database Connector (IDC) file

• HTML extension (HTX) file

The IDC File

The IDC file is the main file used to produce an IDC page. It's a simple text file that follows this basic format:

Datasource: system_DSN
Template: htx_file_name
SQLStatement: sql_statement
[Password: optional_password]
[Username: optional_username]

The system_DSN is the name of the system data source.

htx_file_name is the name of the accompanying HTX file. The wizard names this file with the same root as the IDC file.

sql_statement is the SQL (Structured Query Lanaguage) query that returns the desired rows. If the SQL statement is continued on multiple lines, you need to begin continuation lines with a plus sign (+). For example:

SELECT MenuID, MenuDescription
+ FROM tblMenu
+ ORDER BY MenuDescription

The username and password parameters are required only when you wish to log in to a secured database.

The IDC file generated by the wizard for the tblMenu table in the sample database is shown here:

Datasource:ch23
Template:tblMenu.htx
SQLStatement:SELECT * FROM [tblMenu]
Password:
Username:

The HTX File

The HTML extension file (HTX) describes the layout of the records that will be generated by the IDC query. It's mostly made up of standard HTML, with some embedded placeholders for the data. The basic format of the HTX file is

<HTML>
<HEAD>
<http_header_info>
<TITLE>title</TITLE>
</HEAD>
<BODY>
<TABLE><CAPTION><B>table_caption</B></CAPTION>
<THEAD>
<TR>
<TH>col_1_heading</TH>
<TH>col_2_heading</TH>
...
<TH>col_n_heading</TH>
</TR>
</THEAD>
<TBODY>
<%BeginDetail%>
  <TR VALIGN=TOP>
  <TD><%field_1%><BR></TD>
  <TD><%field_2%><BR></TD>
  ...
  <TD><%field_n%><BR></TD>
  </TR>
<%EndDetail%>
</TBODY>
<TFOOT></TFOOT>
</TABLE>
</BODY>
</HTML>

We've left out all the font tags and other tags the wizard uses to control the formatting of the table, but this is all standard HTML. The only thing that's not standard HTML is the section that falls between the <%BeginDetail%> and <%EndDetail%> tags. These tags tell the IDC to place the data—one table row for each record returned by the IDC query—here, substituting each of the query's fields for each <%field_n%> tag. The field names must correspond to the names of the fields used in the SQL statement in the IDC file.

Hyperlinking to an IDC Page

The IDC produces the actual HTML file at the time the page is requested by submitting the SQL statement from the IDC file to Microsoft Access and merging the results of the request with the HTX file. This produces a temporary HTML file that is then sent over the Internet or intranet to the client browser. Because the browser receives standard HTML, IDC files can be used with any browser.

The hyperlink to the IDC page must be in this format:

https://Web_site/idc_file.idc?

The question mark tells the server that the request can take parameters. In this case there are no parameters, but the question mark is still required. The IDC version of the tblMenu page is shown in Figure 12 as it would look using Netscape Navigator.

Figure 12: : tblMenu table published using the IDC format.

Parameter Queries

In addition to standard queries, the Publish to Web Wizard supports parameter queries. When you ask it to publish a query with one or more parameters, the wizard creates a third file, an HTML document with the HTML extension, that prompts the user for the parameters.

For example, if you published the qryOrderDetailsParam query from the sample database using the GRAY.HTM HTML template, the wizard would create these three files:

• qryOrderDetailsParam_1.IDC

• qryOrderDetailsParam_1.HTX

• qryOrderDetailsParam_1.HTML

The HTML file uses standard HTML to create a text box for each parameter. The qryOrderDetailsParam_1.HTML is shown here:

<HTML>
<TITLE>qryOrderDetailsParam</TITLE>
<BODY background = gray.jpg>
<FORM METHOD="GET" ACTION="qryOrderDetailsParam_1.IDC">
Customer ID #? <INPUT TYPE="Text" NAME="Customer ID #?"><P>
<INPUT TYPE="Submit" VALUE="Run Query">
</FORM>
</BODY>
<BR><BR>
<IMG SRC = "msaccess.jpg">
</HTML>

The lines in the file between the <FORM> and </FORM> tags create the text box (Type="Text") and command button (Type="Submit") controls that appear on the form. Figure 13 shows this page as it looks in Internet Explorer. Figure 14 shows the result of the query when a customer ID number of 5 is entered.

Figure 13: : Parameter page created by the Publish to Web Wizard for the qryOrderDetailsParam query.

Figure 14: : Results of the parameter query with Customer ID # = 5.

Customizing IDC Pages: An Example

The Publish to Web Wizard makes it very easy to get data published on the Web, but you'll likely want to customize the pages the wizard creates. For example, say you've published the qryOrderDetailsParam query from the last section but you'd like to improve the final product in these ways:

• Make the Customer ID # text box on the parameter page into a combo box that looks up customers from the customer table.

• Format the OrderDate, Cost, Discount, and TotalCost columns of the query output so they look more standard.

• Remove the repeating data elements of the query output—CustomerName and City—from the table and place them in the header of the page.

You can improve the usability of the parameter page by changing the text box into a combo box that draws its values from the qryCustomerSorted query in the sample database. Because you will need to establish a connection to the Off304.mdb database to populate the combo box, you'll have to create a pair of IDC and HTX files to grab the combo box row source from the database. The result of this query will then be used to generate a dynamic parameter page that prompts the user for the parameter and passes the parameter along to a second pair of IDC and HTX files that generate the output of the parameter query. The next few sections discuss each of the components of this solution.

The Qryodp1.idc File

You specify the query that will populate the combo box using the Qryodp1.idc file, which is shown here:

Datasource:ch23
Template:qryODP1.HTX
SQLStatement:
+ SELECT CustomerID, CustomerName
+ FROM qryCustomerSorted

The Qryodp1.htx File

The query returns two columns, CustomerID and CustomerName, which are then merged with the Qryodp1.htx file to create the HTML form. The HTX file is shown here:

<HTML>
<TITLE>Customer Query</TITLE>
<BODY background = gray.jpg>
<FORM METHOD="GET" ACTION="qryODP2.IDC">
Select customer:
<SELECT NAME="Customer ID #?" SIZE=1>
<%begindetail%>
    <OPTION VALUE=<%CustomerID%>><%CustomerName%>
<%enddetail%>
</SELECT>
<INPUT TYPE="Submit" VALUE="Run Query">
</FORM>
</BODY>
<BR><BR>
<IMG SRC = "msaccess.jpg">
</HTML>

This HTX file produces the combo box from the results of the Qryodp1.idc query. The combo box is defined by the lines between the first pair of <SELECT> and </SELECT> tags, which contains a <%begindetail%>, <%enddetail%> pair. Thus, for each row returned by the query, the IDC and HTX files create a new OPTION VALUE, generating the following HTML combo box code:

<SELECT NAME="Customer ID #?" SIZE=1>
    <OPTION VALUE=12>Ayala, Steve
    <OPTION VALUE=10>Babitt, Lucy
    <OPTION VALUE=7>Fallon, Jane
    ...continues...
</SELECT>

The resulting parameter query prompt page is shown in Figure 15.

Figure 15: : Parameter page produced by the Qryodp1.idc and Qryodp1.htx files.

You may wonder how this page linked to the second IDC query. The answer is in the ACTION parameter, which is set to Qryodp2.idc. This will cause that IDC file to be loaded when the submit button is pressed, sending along the contents of any controls, which in this case will pass to Qryodp2.idc the value selected in the combo box.

The Qryodp2.idc File

When you select a customer and click the Run Query button, the second IDC query is executed which references the value of the selected customer in the WHERE clause of Qryodp2.idc's SQL statement:

Datasource:ch23
Template:qryODP2.htx
SQLStatement:
+ SELECT tblOrder.OrderId,
+ tblCustomer.FirstName & ' ' & tblCustomer.LastName AS Customer,
+ Format(OrderDate,'mm/dd/yy') AS OrderDate1,
+ tblCustomer.City, MenuDescription, Unit,
+ Format(Price,'$#,##0.00') AS Price1, Quantity, 
+ Format(Discount,'0%%') AS Discount1,
+ Format([price]*[quantity]*(1-[discount]),'$#,##0.00') 
+ AS TotalPrice
+ FROM tblMenu INNER JOIN
+ (tblEmployee INNER JOIN (tblCustomer INNER JOIN
+ (tblOrder INNER JOIN tblOrderDetails
+ ON tblOrder.OrderId = tblOrderDetails.OrderId)
+ ON tblCustomer.CustomerId = tblOrder.CustomerId)
+ ON tblEmployee.EmployeeId = tblOrder.OrderTakerId)
+ ON tblMenu.MenuId = tblOrderDetails.MenuId
+ WHERE (((tblCustomer.CustomerId)=%Customer ID #?%))
+ ORDER BY OrderDate;

If you look back at the output from the query produced by the wizard (see Figure 14), you'll see that the Date, Price, TotalPrice, and Discount fields are formatted poorly. Unfortunately, there's no way to format the results using the HTX file. Perhaps a future version of the HTX file will support this need, but it's not there at this time.

Instead, you will have to do all the formatting using formatting functions in the SQL Select statement in the IDC file. Be aware that not all Microsoft Access functions are supported here; only a small subset of functions is available through ODBC. In particular, we've found the following functions to work:

• Format

• Month

• Day

• Year

• Left

• Right

• Mid

• Instr

• Int

• Date

• Now

Other functions may work here as well; this list represents only the functions we were able to get to work in our limited testing.

The Qryodp2.htx File

The final HTML extension file, Qryodp1.htx, formats the output records. It's similar to the wizard-produced HTX file, with a few improvements.

We improved the heading with the following HTML:

<CAPTION><B>Orders for <%City%> Customer: <%Customer%>
(Customer# = <%idc.Customer ID #?%>)</B></CAPTION>

Because the value of Customer and City is constant for a particular customer, there's no need to print it in the table. Instead, we've moved the value of the fields to the header. In addition, we've included a reference to the value of the parameter that was passed to the IDC file. The format used to reference a parameter is:

<%idc.parameter_name%>

The final output page is shown in Figure 16.

Figure 16: : Output of the parameter query produced by the Qryodp2.idc and Qryodp2.htx files.

Debugging IDC and HTX Pages

If you're having trouble getting your IDC and HTX pages to work, the following tips may help:

• The DSN must be created on the server machine and its name must match the name of the DSN specified in the wizard.

• You must use Internet Information Server, the Microsoft Peer Web Services, or the Microsoft Personal Web Server.

• The DSN must be a System DSN.

• You must have Execute privileges set on the directory containing the IDC and HTX files. (Use the IIS Server Manager to adjust directory settings.)

In addition, when creating IDC SQL statements, keep in mind the following:

• You must use single quotes in expressions.

• You must use ODBC wildcard characters: % and _ instead of * and ?, respectively.

• You must enclose parameters in percent symbols (%).

• If you want to pass percent symbols (%) through to Microsoft Access, you'll need to double them (as with the Discount field).

Publishing Active Pages

Like the IDC format, the Active Server Pages produced by the Publish to the Web Wizard also support dynamic read-only querying of the data. In addition, the wizard publishes Active forms that can be used for form-based record navigation and editing of the data from your browser.

Unlike the IDC format, the Active format requires only one type of file, the ASP file, although there may be several of these files produced by the wizard.

The ASP File and Visual Basic Script

The ASP file is a combination of HTML and the Visual Basic Script language. Visual Basic Script is a special, limited version of Visual Basic for Applications adapted for Web use. In Visual Basic Script, all variables are variants, and many of the advanced aspects of the language have been removed to make the language "lean and mean," but it's still the same basic language used in Microsoft Access, Visual Basic, and Microsoft Office.

The Active pages also make use of a new type of database connection, the Active Data Object (ADO). This is an object layer on top of ODBC that is similar to the Remote Data Object (RDO). ADO is optimized for Internet and intranet data access.

An Active Form Example

The Active pages the wizard produces don't offer much of an advantage over the IDC pages when publishing datasheets—both formats produce similar results. It's the publishing of forms that makes the difference. When you publish forms using the IDC format, the wizard doesn't actually publish the form. Instead, it merely publishes the datasheet for the form's underlying recordset. When you publish forms using the Active format, however, the wizard generates a Microsoft Access form-like page that can be used to browse, update, insert, and delete records!

For example, say you'd like to publish the frmMenu form from the sample database. The Microsoft Access version of this form is shown in Figure 17. If you publish the form using the ASP format and browse the form using Internet Explorer with the ActiveX layout control, you should see a Web page similar to the one in Figure 18.

Figure 17: : The frmMenu form is a standard Microsoft Access form.

Figure18: ASP version of the frmMenu form

The wizard creates two ASP files for each published form. The main ASP file (form_name_1.ASP), which is the file to which you need to establish a hyperlink, queries the data and reacts to the navigation button events. The second file (form_name_1ALX.ASP), which is referenced by the main file, is responsible for creating the form layout using the ActiveX HTML Layout control.

Debugging ASP Pages

If you're having trouble getting Microsoft Access 97–published ASP pages to work, consider the following:

• Are you attempting to open the ASP files directly with your browser? They must be processed by IIS 3.0 which then renders the HTML immediately and passes it back to your browser. Even if you are doing this on a machine running IIS, you have to be careful not to open the ASP file directly. You must use an https://www.microsoft.com address.

• Make sure you have ASP support installed on your Web server. (PWS, for example does not include ASP by default. You must download and install ASP from https://www.microsoft.com/WindowsServer2003/iis/default.mspx )

• Make sure you have Execute privileges on the directories containing the Active Server pages (and their parent directories).

• The ActiveX HTML Layout control was updated on January 20, 1997. You should download the new control which may fix some bugs that people have been encountering. It's located at https://msdn.microsoft.com/library/

• The security of your IE 3 (select View|Options, click the Security tab, and click the Safety Level button) must be set to the middle setting, not the high setting, for the Visual Basic Script to be processed. In addition, make sure that the "Enable ActiveX controls and plug-ins" and "Run ActiveX scripts" options are selected on the Security tab.

• Start with simple forms. Try the Customers form from the NorthWind database as a test form.

• For subforms, the crucial wizard entry is the Server URL. This must point to the exact location of the ASP pages. So if you keep your server pages in the \InetPub\Wwwroot\AccessPages directory of your SuchAndSuch server, this should be filled in like so: "https://www.SuchAndSuch/AccessPages/". If you're not publishing a form with an embedded subform, then leave the Server URL text box blank—it's not needed.

• The beta version of Microsoft Office 97 and ASP may be the problem. There was a change in the object name of ADO half way through the beta. The shipping release of Microsoft Access 97 writes code that uses ADODB. But earlier versions of Microsoft Access and ASP used ADO. Make sure that you upgrade your ASP and Office versions to the shipping versions of these products. This may require you to regenerate your pages using the shipping version of Microsoft Access.

• The ODBC data source that you named in the wizard must exist on the Web server and must be a System DSN (data source name).

Recognizing Microsoft Access' Limits as an Internet Data Server

Microsoft Access is a file server database and thus is limited in the number of simultaneous users it can support. Every time a user launches a dynamic Web page (IDC or ASP) on your server, IIS will need to load the Jet Engine DLLs and open the shared database. This shouldn't be a problem within a limited corporate intranet environment with ten users—perhaps even more if you're using read-only pages—but going much beyond this will likely cause speed, contention, or corruption problems.

If you plan on having many users—especially if they will be concurrently updating records—or if you're unhappy with the response time of your Microsoft Access–published pages, you may wish to consider upsizing to a client/server database, such as the Microsoft SQL Server client-server database management system or ORACLE®. You can edit the pages produced by the Microsoft Access Publish to Web Wizard so they point at a client/server database system data source instead of your Microsoft Access data source. (This may also require some changes to the SQL.)

Back to the Stone Age?

Using the Internet to browse and update data stored in database tables may seem like going back to the Stone Age. In many ways, it is. Many of the tools, as well as the final product produced by these tools, are primitive when compared with the robust tools and applications you can pull together using a desktop database such as Microsoft Access. While you may find it amazing when your published ASP pages work, you would like to reach for slightly higher functionality. Web-based applications are slow and lack much of the functionality you've come to expect from standard non-Web database applications.

There's a lot your Internet- and intranet-enabled applications just simply cannot do or can do only rather poorly. This means there still is a place for your regular Microsoft Access applications.

When it makes sense to publish on the Web, by all means publish, but it's probably a bad idea to start converting all your applications to Web-only applications. That's not to say there won't be a time in the future when most or all of the user interface of desktop applications will be run through a Web browser. That day may come—perhaps sooner than we think.

Conclusion

The Internet is an exciting frontier. Microsoft Access 97 has many enhancements that make it easy to create applications for the Internet or your corporate intranet.

Appendix

For more information on Internet and intranet development with Microsoft Access, check out the following resources:

Access 97 Developer's Handbook, Paul Litwin, Ken Getz, and Mike Gilbert (Sybex 1997).

This paper is excerpted from the book. The Internet and intranet chapter in the book is much more detailed and includes additional information on Active Server Pages, HTML, the Microsoft Web Browser control, templates, profiles and more. The book is available in most major bookstores as well as many electronic bookstores, including www.caryp.com and www.amzon.com.

More on the Internet Database Connector

The best place to look for additional information on IDC is the "Publishing Information and Applications" chapter of the Installation and Administration Guide that comes with Microsoft Internet Information Server. (This is an electronic document in HTML format that is copied to your server when you install IIS.) Also check out the IIS Web page at:

https://www.microsoft.com/WindowsServer2003/iis/default.mspx

More on HTML

If you'd like to learn more about basic HTML or HTML forms, you may wish to perform a search using your favorite Web search tool for sites that have information on HTML and Web page authoring. There are plenty of excellent sites. Here are a few we found useful:

• https://www.library.carleton.edu/Webtutorial/welcome.html

• https://gort.ucsd.edu/iassist/

• https://www.w3.org/pub/WWW/

• https://www.ncsa.uiuc.edu/General/Internet/WWW/HTMLPrimer.html

• https://www.microsoft.com/workshop/author/default.htm

More on ASP, ADO, and Visual Basic Script

The best place for information on Active Server Pages, ActiveX Data Objects and Visual Basic Script is the documentation and samples that come with Microsoft Internet Information Server. Also look at these Web pages:

• https://www.microsoft.com/WindowsServer2003/iis/default.mspx

• https://www.microsoft.com/ado/

• https://www.microsoft.com/vbscript/