Presented by Paul Litwin
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.
Email: plitwin@mcwtech.com
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:
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.
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 http://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:
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 |
| http://www.microsoft.com | Link to the default HTML document at http://www.microsoft.com | http://www.microsoft.com |
| Microsoft’s Home Page#http://www.microsoft.com | Link to the default HTML document at http://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.
You'll find hyperlinks in Microsoft Access in three places:
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.
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:
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.
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.
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 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
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 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:="http://altavista.digital.com/cgi-bin/query", _
ExtraInfo:="pg=q&what=Web&fmt=.&q=" & _
varSearch, Method:=msoMethodGet, _
AddHistory:=True
Case adhcSiteYahoo
Application.FollowHyperlink _
Address:="http://search.yahoo.com/bin/search", _
ExtraInfo:="p=" & varSearch, _
Method:=msoMethodGet, _
AddHistory:=True
Case adhcSiteExcite
Application.FollowHyperlink _
Address:="http://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 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
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:
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 http://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.
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:
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) |
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.)
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:
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 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.
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:
http://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
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:
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.
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:
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
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.
If you're having trouble getting your IDC and HTX pages to work, the following tips may help:
In addition, when creating IDC SQL statements, keep in mind the following:
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 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.
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.
If you're having trouble getting Microsoft Access 97–published ASP pages to work, consider the following:
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.)
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.
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.
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: http://www.microsoft.com/iis/
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:
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:
© 1997 Microsoft Corporation. All rights reserved.
The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.
This document is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.
Microsoft, SQL Server, Visual Basic, Windows, and Windows NT are registered trademarks and ActiveX is a trademark of Microsoft Corporation.
Other product or company names mentioned herein may by the trademarks of their respective owners.
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.