Information
The topic you requested is included in another documentation set. For convenience, it's displayed below. Choose Switch to see the topic in its original location.

Developing Applications for the Internet and the World Wide Web

CHAPTER 10

Developing Applications for the Internet and the World Wide Web

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

This chapter shows you how to use Microsoft® Access to develop applications that retrieve, publish, and share information about the Internet or a local area network (LAN). For example, you can publish or share information from a database located on a Web server. You can also create hyperlinks that you click to navigate to database objects and other Microsoft Office documents located on a local hard disk or a local area network.

Chapter Contents

What Is the Internet?   

Using Hyperlinks in Microsoft Access Applications   

Making Microsoft Access Data Available on the Internet   

Importing and Linking Data on the Internet   

Setting Up a Personal Web Server    

What Is the Internet?

In all likelihood, you are well aware of what the Internet is, and you’ve had a  chance to take advantage of its many resources. Even if you have used the Internet, the following overview will help to make sure you understand the terms used to describe it in this chapter.

The Internet is a collection of computer networks that connects millions of computers around the world. The World Wide Web is a client/server technology used to access a vast variety of digital information from the Internet. Using a software client called a Web browser, such as Microsoft® Internet Explorer, and a modem or other connection to an Internet Service Provider (ISP), you can easily access text, graphics, sound, and other digital information from practically any computer in the world that is running the appropriate server software on the Internet.

Your Web browser uses a variety of standardized methods for addressing and communicating with Internet servers. These methods are called protocols. The most common protocol is Hypertext Transfer Protocol (HTTP), which was originally created to publish and view linked text documents, but has been extended to display and run a growing variety of graphics, sound, video, and other multimedia content. Other common protocols include File Transfer Protocol (FTP), Gopher, telnet, RealAudio™, as well as protocols used to start other applications such as e-mail and Usenet newsreaders.

To run or display Internet content with a Web browser, you type an address called a Uniform Resource Locator (URL) into its address box. For example, this is the URL for Microsoft:

http://www.microsoft.com

A URL specifies the location of a file on an Internet server. Your Web browser uses  a URL to download and open the file, which is most typically a page formatted with Hypertext Markup Language (HTML) tags. HTML tags are codes enclosed in angle brackets that are used by a Web browser to determine the structure and appearance of an HTML document, such as graphic elements and text formatting. For example, the two HTML tags in the following sentence:

Make <B>this text</B> look bold.

Cause the text to display like this when viewed with a Web browser:

Make this text look bold.

To navigate to other pages or multimedia content, a user clicks a hyperlink on a Web page. A hyperlink is colored and underlined text, or a graphic, that activates a URL to download and open another file, such as another Web page or some form of multimedia content, such as a picture or sound file.

You can use HTML tags called anchors to create hyperlinks. An anchor with an HREF attribute jumps to a file outside of the current document. For example, the following anchor creates a hyperlink that jumps to the Microsoft home page:

<A HREF="http://www.microsoft.com">Microsoft Home Page</A>

An anchor with a NAME attribute creates a bookmark at a location within the same document. Other hyperlinks can jump to the bookmark created with this type of anchor.

HTML was originally a simple system for publishing documents on the Web, but it’s rapidly evolving to include features that you can use to create sophisticated, interactive applications.

If you install Internet server software on servers connected by a local area network (LAN), you can use these same Internet technologies to share data within an organization. Such a system is called an intranet or internal Web. For example, your organization could post human resources information for all employees on a Web page, or a project team could post information about its members and provide hyperlinks to important documentation about the project. All the features in Microsoft Access and Microsoft Office that are designed for the Internet can also be used on an intranet.

Microsoft Office and Microsoft Access Internet Features

In Microsoft Office 2000 applications, you can create hyperlinks in documents or files to display and run standard Internet content. These Microsoft Office applications also extend hyperlink technology so that you can create hyperlinks to navigate between Microsoft Word documents, Microsoft Excel worksheets, Microsoft PowerPoint® slides, and Microsoft Access database objects that are stored on a local hard disk or on a LAN. You don’t need Internet connections or servers to use hyperlinks to navigate between Microsoft Office documents or files. You can use both kinds of hyperlinks in the same application.

Access provides many ways to create applications that display and run content that is available on the Internet or an intranet. For example, you can create a data access page to enter and edit data, or to interactively report grouped records. You can also use Access to publish information from datasheets and reports as Web pages. In addition, you can use Access to create files that query a database on a Web server, and then return a Web page to display the results of the query.

The following table summarizes the features available in Access that you can use to work with content on the Internet or an intranet.

FeatureUses
Data access pagesCreate a web page to add, edit, view, or manipulate current data in an Access database or a Microsoft SQL Server™ database.
 Create a web page to enter or edit data, similar to Access forms.
Create a web page to interactively report grouped records, similar to Access reports.
Jumping to content on the Internet or an intranetStore hyperlinks in fields with the Hyperlink data type and follow the hyperlinks to display Internet content.
 Browse the folders of FTP sites from the Link To File dialog box when inserting or editing hyperlinks.
Bind a text box control on a form to a field with the Hyperlink data type to enter, display, or follow a hyperlink.
Bind a text box control on a report to a field with the Hyperlink data type to create a hyperlink when you save a report as an HTML document or to print the hyperlink address.
Add a screen tip to display text when the cursor is moved over a hyperlink
Reference a hyperlink from a label, image control, command button on a form or report, toolbar button or menu command.
Use Visual Basic® for Applications (VBA) methods and properties to work with hyperlinks. For example, use the FollowHyperlink method to navigate to the address referenced in a hyperlink, and use the AddToFavorites method to add a hyperlink to the Favorites folder.
Jumping to Microsoft Office documents and filesNavigate to Microsoft Access objects in the current database or other databases by using hyperlinks from the current Access database or from another Microsoft Office application.
 Navigate to documents from other Microsoft Office applications by using hyperlinks from Microsoft Access databases.
Use the Back and Forward buttons on the Web toolbar to navigate between followed hyperlinks to database objects and other Microsoft Office documents.
Locate Access databases and other Microsoft Office documents with Web Find Fast.
Publishing and sharing data in a database on the WebSave the data from table, query, and form datasheets, or reports as static HTML documents.
 Create Internet Database Connector/HTML extension (IDC/HTX) files to query data from a table, query, or form datasheet in a database on an Internet server and display it in a Web page.
Create an Active Server Page (ASP) to query data from a table, query, or form datasheet in a database on an Internet server and display it in a Web page.
Export HTML documents, IDC/HTX files, or Active Server Pages (ASP) by using the OutputTo method or action.
Attach HTML documents, IDC/HTX files, or Active Server Pages (ASP) to e-mail messages by using the SendObject method or action.
Export tables as HTML tables by using the TransferText method or action.
Using the Internet with database replicationSynchronize a database replica with a replica or Design Master on an Internet server.
Importing and linking data located on the Internet or an intranetImport and link HTML tables and lists by using the Import HTML Wizard and the Link HTML Wizard. When you link HTML tables, the data is read-only.
 Import and link any data on an Internet server that is supported by a built-in Access driver. When you link data on an Internet server, the data is read-only.
Use the Import and Link dialog boxes to browse FTP sites when you are importing or linking data. You can also use the Import and Link dialog boxes to enter an HTTP address when you are importing or linking data.
Import or link HTML tables by using the TransferText method or action. When you link HTML tables, the data is read-only.
Import or link data by using HTTP and FTP addresses, and export data by using FTP addresses in the following VBA properties, methods, and actions:

Connect property CreateTableDef method OutputTo method and action TransferDatabase method and action TransferText method and action TransferSpreadsheet method and action


The following sections describe most of these features in more detail.

See Also   For more information about data access pages, type data access pages in the Office Assistant or on the Answer Wizard tab in the Help window, and then click Search, or see Chapter 5, “Working with Office Applications,” in the Microsoft Office 2000/Visual Basic Programmer's Guide (Microsoft Press, 1999). You can also find this guide in the Office Developer Documentation section of the Microsoft Developer Network (MSDN) Online Library.

Using Hyperlinks in Microsoft Access Applications

Hyperlinks can jump to two kinds of objects: a Web page or other content on the Internet or an intranet, or to a Microsoft Office document (Microsoft Word document, Microsoft Excel worksheet, Microsoft PowerPoint slide, or Microsoft Access database object) stored on a local hard disk or a LAN.

There are two ways you can use hyperlinks in Access applications:

  • Create a field with the Hyperlink data type to store hyperlink addresses in a table, and then bind that field to a text box on a form. Like other bound fields, as the user moves from record to record, the value in the text box changes to display the current record’s hyperlink value. For example, you can use hyperlinks in this way to create an application in which users can jump to Web pages, or to other content on the Internet or an intranet, from a predefined list of addresses. You can also use hyperlinks in this way to create an application that displays and manages Microsoft Office documents.

  • Create a label, image control, command button on a form, toolbar button or menu command that references a specified hyperlink address. In this case, the hyperlink doesn’t change as you move from record to record. For example, you can use hyperlinks in this way to navigate to other database objects within the same database, or to open a Web page on an intranet that contains updated information about how to use your application.

Regardless of how a hyperlink is defined in your application, if the hyperlink jumps to a database object or opens another Microsoft Office document, you can use the Web toolbar to navigate between the hyperlinks you’ve previously followed. For example, in the following illustration, the Products form has a command button with a hyperlink that opens a report. Once you have clicked the hyperlink, you can use the Web toolbar to navigate between other objects you’ve opened with hyperlinks.

Aa188212.ba1001(en-us,office.10).jpg

Similarly, if you follow a hyperlink from an Access form to open a Microsoft Word document, you can click the Back button on the Web toolbar in Microsoft Word to return to the form.

Note   By default, after a user clicks a hyperlink on a form, Access continues to display the Web toolbar when the user closes the form. If you want to prevent this, you can use VBA code in the OnClose event of the form to hide the toolbar. To see an example of VBA code that does this, open the Products form in the Northwind sample application in Design view, and then display the event procedure in the OnClose event of the form.

Storing Hyperlinks in Tables

In Microsoft Access 2000, a field in a table can store hyperlinks as data. To create a Hyperlink field, add a field in table Design view and set its DataType property to Hyperlink. You can also create a Hyperlink field in table Datasheet view by clicking Hyperlink Column on the Insert menu.

You can follow a hyperlink stored in a table by clicking it in the table, but more typically, the field will be bound to a text box control on a form.

To add a text box that is bound to a Hyperlink field to a form

  1. Open the form in Design view.

  2. Make sure the form’s RecordSource property is set to the table that contains the Hyperlink field, or to a query that includes the table.

  3. Click Field List on the toolbar.

  4. Drag the Hyperlink field from the field list to the form.

To see an example of how to use a Hyperlink field, open the Suppliers form in the Northwind sample application. The Home Page text box on the form is bound to the HomePage field in the Suppliers table. Clicking a hyperlink in the text box starts your Web browser and displays the supplier’s home page.

Aa188212.ba1002(en-us,office.10).jpg

You can also use hyperlinks in Access to jump to database objects and other Microsoft Office documents. For example, you could create a document management application that uses a Hyperlink field to store paths to Microsoft Word documents on a network. Users of such an application could add records to track new documents, or click the hyperlink in a previously added record to open the specified document.

The Hyperlink Field Storage Format

A Hyperlink field stores up to four pieces of information: the displaytext, the address, the subaddress, and the screentip. Each piece is separated by the number sign (#), in the following format:

displaytext#address#subaddress#screentip

The following table describes each piece of the Hyperlink field storage format.

PieceDescriptionRequired?
displaytextThe text the user sees in the Hyperlink field in a table, or in a text box bound to the Hyperlink field. You can set the display text to any text string. For example, you may want the display text to be a descriptive name for the Web site or object specified by the address and subaddress. If you do not specify display text, Access displays the value of address instead.No
address A valid URL that points to a page or file on the Internet or an intranet, or the path to a file on a local hard drive or LAN. If you enter a path on a LAN, you can omit a mapped drive letter and use the universal naming convention (UNC) format: \\server\share\path\filename. This prevents the path from becoming invalid if the database is later copied to another computer’s hard drive or into a shared network folder.Yes, unless subaddress points to an object in the current database (.mdb) file.
subaddress The location within a file or document; for example, a database object, such as a form or report. When referring to a database object, the name of the object should be preceded by its type: Table, Query, Form, Report, Macro, or Module. Other possible values for subaddress include a bookmark in a Microsoft Word document, an anchor in an HTML document, a Microsoft PowerPoint slide, or a cell in a Microsoft Excel worksheet.No
screentipThe text that appears when you rest the pointer over a hyperlink.No

Each piece of the Hyperlink field storage format can be up to 2,000 characters. The maximum length of the entire Hyperlink field value is 6,000 characters.

The following table gives examples of valid Hyperlink field values.

Hyperlink field valueJumps to
Cajun Delights#http://www. cajundelights.com/cajun.htm#The Cajun Delights Web page. Only the words “Cajun Delights” are displayed in the field or control.
#http://www.cajundelights.com/cajun.htm#The Cajun Delights Web page. The text “http://www.cajundelights.com” appears in the field or control because no display text is specified.
#http://www.cajundelights. com/cajun.htm#PriceThe HTML anchor with the NAME attribute Price on the Cajun Delights Web page. The text “http://www.cajundelights.com/ cajun.htm” is displayed in the field or control.
Resume#c:\windows\personal\resume.doc#A Microsoft Word file named Resume.doc located in the \Windows\Personal folder. Only the word “Resume” is displayed in the field or control.
#c:\windows\personal\resume.doc#A Microsoft Word file named Resume.doc located in the \Windows\Personal folder. The text “c:\windows\personal\resume.doc” appears in the field or control because no display text is specified.
#c:\windows\personal\resume. doc#QualificationsThe section in the Resume.doc Microsoft Word file marked with the bookmark name Qualifications. The text “c:\windows\ personal\resume.doc” is displayed in the field or control.
#\\databases\samples\northwind.mdb#Form SuppliersThe Suppliers form in the Northwind sample application located in the Samples share on the Databases server on a LAN (UNC format path). The text “\\databases\ samples\northwind.mdb” is displayed in the field or control.
Suppliers Form##Form SuppliersThe Suppliers form in the current database. The words “Suppliers Form” are displayed in the field or control.
#c:\windows\personal\1996 Sales.ppt#13Slide 13 in the 1996 Sales Microsoft PowerPoint presentation located in the \Windows\Personal folder. The text “c:\windows\personal\1996 Sales.ppt” is displayed in the field or control.
#c:\windows\personal\budget.xls#Sheet1!A2The A2 cell in Sheet1 of the Budget.xls file located in the \Windows\Personal folder. The text “c:\windows\personal\budget.xls” is displayed in the field or control.

You can display the stored hyperlink format by pressing F2 when the insertion point is in the Hyperlink field. You can edit the stored hyperlink in this form as long as you enter number signs (#) in the appropriate locations. You can add or edit the displaytext part of a hyperlink field by right-clicking a hyperlink in a table, pointing to Hyperlink on the shortcut menu, and then typing the display text in the Display Text box.

See Also   For more information about the Hyperlink field storage format, type type a hyperlink in the Office Assistant or on the Answer Wizard tab in the Help window, and then click Search.

Entering a URL as a Hyperlink Address

To create a hyperlink that jumps to a Web page or other Internet content, you must enter a valid URL as the hyperlink address. You can enter a URL that points to any Internet file type or resource supported by the browser that will be used to display or run it. You enter most URLs in the following format:

protocol://serveraddress/path

Protocol specifies the Internet protocol used to establish the connection to the server, and is generally followed by a colon and two slash marks. Serveraddress specifies what is usually called the domain name of the Internet server. Path specifies the location and name of the page or file on the Internet server. For example, the URL to the home page of the Microsoft Access Developer Forum is:

http://msdn.microsoft.com/officedev/

When you type a URL into a Hyperlink field, Access automatically recognizes the following Internet protocols.

ProtocolProtocol nameDescription
httpHypertext Transfer ProtocolJumps to Web pages that contain text, graphics, sound, and other digital information from a Web server on the World Wide Web.
ftpFile Transfer ProtocolTransfers files between computers on the Internet.
gopherGopher protocolDisplays information about a Gopher server.
waisWAIS protocolAccesses a Wide Area Information Servers database.
fileFile protocolOpens a file on a local hard drive or LAN.
httpsHypertext Transfer Protocol with privacyEstablishes an HTTP connection that uses Secure Sockets Layer (SSL) encryption.

ProtocolProtocol nameDescription
mailtoMailTo protocolOpens your electronic mail program to send a message to the specified Internet e-mail address. A URL that uses the MailTo protocol has a different format: (mailto:username@domain)
msnMicrosoft Network protocolJumps to a location on The Microsoft Network.
newsNews protocolStarts a newsreader and opens the specified Usenet newsgroup. A URL that uses the News protocol has a different format: (news:newsgroupname)
nntpNetwork News Transfer ProtocolPerforms the same function as News protocol, except two slashes follow the colon (nntp://newsgroupname).
midMusical Instrument Digital Interface (MIDI) protocolPlays MIDI sequencer files if the user’s computer has a sound card.
cidCompuServe® Dialer (CID) protocol Establishes a point-to-point protocol (PPP) connection with the Internet through CompuServe’s network.
prosperoProspero protocolOpens files on the Prospero distributed file system.
telnetTelnet protocolStarts a telnet terminal emulation program. A terminal emulation program is a command-line interface that you can use to issue commands on a remote computer. For example, by using telnet to connect to a UNIX server, you can issue UNIX commands to perform operations on that server.
rloginRlogin protocolStarts an Rlogin terminal emulation program.
tn3270TN3270 protocolStarts a TN3270 terminal emulation program.
pnmRealAudio protocolPlays RealAudio streaming audio from a RealAudio server. Streaming audio and other streaming media formats establish a connection to the server and start playing immediately without downloading an entire file.
mmsMicrosoft Media Server (MMS) protocolPlays media such as ActiveMovie™ streaming format files (.asf) from an MMS server.

If you create a field by importing a column of data and all records in the imported data begin with one of these protocols, Access automatically sets the data type of the imported field to Hyperlink. Similarly, if you create a new table in Datasheet view, and every entry you make in a field begins with one of these protocols, Access sets the data type of the new field to Hyperlink when you save the table.

Creating a Label, Image Control, or Command Button That Follows a Hyperlink

To add a label, image control, or command button that follows a hyperlink to a form, set the HyperlinkAddress and HyperlinkSubAddress properties of the control to point to the content on the Internet or an intranet, or to the Microsoft Office document or Access database object you want to jump to.

Note   The HyperlinkAddress and HyperlinkSubAddress property settings correspond to the address and subaddress values entered for a Hyperlink field. For more information about these values, see “The Hyperlink Field Storage Format” earlier in this chapter.

Additionally, to create the hyperlink display text for a label or command button control, you must set the Caption property. No text displays for an image control, so there is no corresponding display text setting.

You can also create a label that follows a hyperlink by opening the form in Design view, and then using the Hyperlink command (Insert menu). However, this method won’t define the display text. To define display text, you must set the label’s Caption property.

See Also   For more information about creating a label, image control, or command button that follows a hyperlink, type create a hyperlink on a form or report in the Office Assistant or on the Answer Wizard tab in the Help window, and then click Search. For more information about creating a toolbar button or menu command that follows a hyperlink, type create a hyperlink on a button or command in the Office Assistant or on the Answer Wizard tab in the Help window, and then click Search.

Using VBA Methods and Properties to Work with Hyperlinks

Microsoft Access 2000 provides a several methods and properties that you can use to work with hyperlinks in VBA code. The following table summarizes these methods and properties.

Method or property nameDescription
Follow methodThe Follow method has the same effect as clicking a hyperlink. When you use the Follow method, you don’t need to know the address specified by a control’s HyperlinkAddress or HyperlinkSubAddress property, or by the Hyperlink field that is bound to a text box control. You only need to know the name of the control that contains the hyperlink.
FollowHyperlink methodFollows a hyperlink specified in code or passed to the method from an unbound text box. For example, you can prompt a user to type a hyperlink address in a dialog box, and then use the FollowHyperlink method to navigate to that address. You can also use the FollowHyperlink method to specify a hyperlink for controls other than labels, image controls, and command buttons, or text boxes bound to Hyperlink fields.
AddToFavorites methodAdds the hyperlink address specified in the referenced control to the Favorites folder.
Hyperlink propertyReturns a reference to a hyperlink object in code. You can use the Hyperlink property to access the properties and methods of any control that contains a hyperlink.
HyperlinkAddress propertySets or returns the address of a hyperlink for a label, image control, or command button. The HyperlinkAddress property is equivalent to setting or returning the Address property for the control in VBA; for example, object.HyperlinkAddress is equivalent to object.Hyperlink.Address. You can also set the HyperlinkAddress property in the control’s property sheet.
HyperlinkSubAddress propertySets or returns the location within the Microsoft Office document or object specified by the HyperlinkAddress property. When no HyperlinkAddress property is specified, HyperlinkSubAddress specifies a database object in the current database. The HyperlinkSubAddress property is equivalent to setting or returning the SubAddress property for the control in VBA; for example, object.HyperlinkSubAddress is equivalent to object.Hyperlink.SubAddress. You can also set the HyperlinkSubAddress property in the control’s property sheet.
HyperlinkPart functionParses the four parts of a hyperlink stored in a table.

See Also   For more information about these methods and properties, type the name of the method or property in the Office Assistant or on the Answer Wizard tab in the Help window, and then click Search.

Example of Using the Follow Method

This example uses the Follow method to automatically open the Web page specified in a text box bound to a Hyperlink field on a form. Add the following code to the OnCurrent event of a form. Note that you must use the Hyperlink property to return a reference to the object that contains the hyperlink.

Private Sub Form_Current()

   Dim txt As TextBox

On Error GoTo ErrorHandler

   ' Set reference to the txtAddress text box bound to a Hyperlink field.
   Set txt = txtAddress

   ' Follow the hyperlink.
   txt.Hyperlink.Follow

   Exit Sub

ErrorHandler:
   MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description

End Sub

Example of Using the FollowHyperlink Method

This example uses the FollowHyperlink method to add a hyperlink to a control that doesn’t support the HyperlinkAddress or HyperlinkSubAddress properties. Add the following code to the Click event of an unbound object frame named OLEUnbound1 to start a Web browser and open the specified hyperlink address when you click the image.

Private Sub OLEUnbound1_Click()

   Dim strAddress As String

On Error GoTo ErrorHandler

   ' Set reference to hyperlink address.
   strAddress = "http://www.microsoft.com"

   ' Follow hyperlink address.
   Application.FollowHyperlink strAddress, , True

   Exit Sub

ErrorHandler:
   MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description

End Sub

Tip   Controls that don’t support the HyperlinkAddress or HyperlinkSubAddress properties don’t provide any feedback to the user to indicate that they contain a hyperlink. One way to inform a user that the control contains a hyperlink is to set the control’s ControlTipText property so that a text message appears when users rest the pointer on the control.

Note   You can also use the FollowHyperlink method to prompt a user to enter a hyperlink address and then to follow it. For an example of how to do this, type FollowHyperlink method in the Office Assistant or on the Answer Wizard tab in the Help window, and then click Search

The HyperlinkPart Function

The HyperlinkPart function returns information about data stored in a Hyperlink field. The syntax for the HyperlinkPart function is:

object.HyperlinkPart(hyperlink As Variant, part As Integer)

The following table describes the arguments of the HyperlinkPart function.

ArgumentDescription
objectOptional. The Application object.
hyperlinkRequired. A Variant that represents the data stored in a Hyperlink field.
partOptional. An intrinsic constant that represents the information you want returned by the HyperlinkPart function.

You can set the part argument to the following constants.

ConstantValueDescription
acDisplayedValue0(Default) The underlined text displayed in a hyperlink.
acDisplayText1The displaytext part of a Hyperlink field.
acAddress2The address part of a Hyperlink field.
acSubAddress3The subaddress part of a Hyperlink field.
acScreenTip4The screentip part of a Hyperlink field.
acFullAddress5The address and subaddress parts of a Hyperlink field delimited by a # character.

Note   If you use the HyperlinkPart function in an SQL statement or a query, the part argument is required and you can’t set it to the constants listed in the preceding table - you must use the value instead.

You use the HyperlinkPart function to return one of four values stored in a Hyperlink field (displaytext, address, subaddress, or screentip) or the displayed value. The value returned depends on the setting of the part argument. If you don’t use the part argument, the HyperlinkPart function returns the value Access displays for the hyperlink (which corresponds to the acDisplayedValue setting for the part argument).

When a value is provided in the displaytext part of a Hyperlink field, the value displayed by Access will be the same as the displaytext setting. When there’s no value in the displaytext part of a Hyperlink field, Access displays the value of the address or subaddress part of the Hyperlink field, depending on which value is first present in the field.

The following table shows the values returned by the HyperlinkPart function for data stored in a Hyperlink field.

Hyperlink field dataHyperlinkPart function returned values
#http://www.microsoft.com/#acDisplayedValue: http://www.microsoft.com/

acDisplayText: No value returned.

acAddress: http://www.microsoft.com/

acSubAddress: No value returned.

acScreenTip: No value returned.

acFullAddress: http://www.microsoft.com

Microsoft#http://www.microsoft.com/#acDisplayedValue: Microsoft

acDisplayText: Microsoft

acAddress: http://www.microsoft.com/

acSubAddress: No value returned.

acScreenTip: No value returned.

acFullAddress: http://www.microsoft.com

Customers#http://www.microsoft.com#Form CustomersacDisplayedValue: Customers

acDisplayText: Customers

acAddress: No value returned.

acSubAddress: Form Customers

acScreenTip: No value returned.

acFullAddress: http://www.microsoft.com#Form Customers

##Form Customers#Enter InformationacDisplayedValue: Form Customers

acDisplayText: No value returned.

acAddress: No value returned.

acSubAddress: Form Customers

acScreenTip: Enter Information

acFullAddress: #FormCustomer


The following example uses all six of the part argument constants to display information returned by the HyperlinkPart function for each record in a table containing a Hyperlink field. To try this example, paste the DisplayHyperlinkParts procedure into the Declarations section of a module. You can call the DisplayHyperlinkParts procedure from the Immediate window, passing to it the name of a table that contains hyperlinks and the name of the field that contains Hyperlink data, as shown in the following example.

‘Call this procedure from the IMMEDIATE window.
DisplayHyperlinkParts "MyHyperlinkTableName", "MyHyperlinkFieldName"

Sub DisplayHyperlinkParts(strTable As String, strField As String)
   Dim dbs As DAO.Database
   Dim rst As DAO.Recordset
   Dim strMsg As String

On Error GoTo ErrorHandler

   Set dbs = CurrentDb
   Set rst = dbs.OpenRecordset(strTable)

   Do While Not rst.EOF
      strMsg = "DisplayValue = " & _
               HyperlinkPart(rst(strField), acDisplayedValue) & _
               vbCrLf & "DisplayText = " & _
               HyperlinkPart(rst(strField), acDisplayText) & _
               vbCrLf & "Address = " & _
               HyperlinkPart(rst(strField), acAddress) & _
               vbCrLf & "SubAddress = " & _
               HyperlinkPart(rst(strField), acSubAddress) & _
               vbCrLf & “ScreenTip = “ & _
               HyperLinkPart(rst(strField), acScreenTip) & _
               vbCrLf & “FullAddress = “ & _
               HyperLinkPart(rst(strField), acFullAddress)

      ' Show parts returned by HyperlinkPart function.
      MsgBox strMsg
      rst.MoveNext
   Loop

   Exit Sub

ErrorHandler:
   MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Sub

When you use the HyperlinkPart function in a query, the part argument is required. For example, the following SQL statement uses the HyperlinkPart function to return information about data stored as a Hyperlink data type in the URL field of the Links table:

SELECT Links.URL,
   HyperlinkPart([URL],0) AS Display,
   HyperlinkPart([URL],1) AS Name,
   HyperlinkPart([URL],2) AS Addr,
   HyperlinkPart([URL],3) AS SubAddr,
   HyperlinkPart([URL],4) AS ScreenTip,
   HyperlinkPart([URL],5) AS FullAddress
FROM Links

Making Microsoft Access Data Available on the Internet

Besides creating data access pages, you can make your data available on the Internet or an intranet by:

  • Saving data as HTML documents.

  • Synchronizing a database replica with a replica or Design Master on an HTTP or FTP server.

Saving Data as HTML Documents

Access has three ways to save data from your database as  HTML documents:

Save data as static HTML documents   You can create static HTML documents from table, query, and form datasheets, and from reports. When you save data as static HTML documents, the resulting pages reflect the state of the data at the time it was saved, like a snapshot. If your data changes, you must save the pages again to share the new data.

Save table, query, and form datasheets as IDC/HTX files   You can save your table, query, and form datasheets as Internet Database Connector/HTML extension (IDC/HTX) files that generate HTML documents by querying a copy of your  database located on a Web server for current data.

Save table, query, and form datasheets as Active Server Pages   You can save table, query, and form datasheets as Active Server Pages that display current data from a copy of your database located on a Web server.

The following sections discuss each of these options in more detail.

Saving Data as Static HTML Documents

With Microsoft Access 2000, you can save table, query, form datasheets, and reports as static HTML documents.

To save a table, query, form datasheet, or a report as a static HTML document

  1. In the Database window, click the table, query, form, or report you want to save.

  2. On the File menu, click Export.

  3. In the Save As Type box, click HTML Documents (*.html; *.htm).

  4. If you want to preserve formatting, select the Save Formatted check box. To automatically open the resulting HTML document in your Web browser, select  the Autostart check box.

  5. Specify the file name and location to save the file, and then click Save.

  6. In the HTML Output Options dialog box, if you want Access to merge an HTML template with the resulting HTML document, specify that as well, and then click OK.

    See Also   For information about HTML templates, see “Using an HTML Template When You Save Data as HTML Documents” later in this chapter.

You can also save data as static HTML documents by using the OutputTo method in code or the OutputTo action in macros.

See Also   For more information about saving table, query, form datasheets, and reports as HTML documents, type Export HTML in the Office Assistant or on the Answer Wizard tab in the Help window, and then click Search.

When saving table, query, and form datasheets, Access saves each datasheet to a single HTML file. Access saves reports as multiple HTML documents, with one HTML file per printed page. To name each page, Access uses the name of the object and appends _Pagenn to the end of each page’s file name after the first page; for example, ProductList.htm, ProductListPage2.htm, ProductListPage3.htm, and so on.

Navigation Controls When Saving Multiple HTML Documents Per Object

When you save a report as multiple HTML documents, Access creates hyperlinks that the user can use to navigate to the first, previous, next, and last pages in the publication. You can specify an HTML template that contains placeholders for navigation controls. Where Access places the hyperlinks depends on where you locate the placeholders in the HTML template.

See Also   For information about HTML templates and placeholders, see “Using an HTML Template When You Save Data as HTML Documents” later in this chapter.

How Access Saves Data Types in HTML Format

When you save data as static HTML documents, Access saves values from most data types as strings and formats them as closely as possible to their appearance in the datasheet or report. There are two exceptions:

  • OLE Object fields are not saved.

  • Hyperlink field values are saved as hyperlinks in the HTML document. The hyperlinks use HTML anchor tags with an HREF attribute, as described in the following table.
    IfAnchor tag format
    The hyperlink doesn’t include a subaddress<A HREF="address">displaytext</A>
    The hyperlink includes a subaddress<A HREF="address#subaddress">displaytext</A>
    Display text isn’t specified<A HREF="address">address</A>

Access determines the displaytext, address, subaddress, and screentip values by parsing the value stored in the Hyperlink field.

See Also   For information about the displaytext, address, subaddress, and screentip values, see “The Hyperlink Field Storage Format” earlier in this chapter.

Using an HTML Template When You Save Data as HTML Documents

When you save data as HTML documents, you can use an HTML template to give  a consistent look to the HTML documents you create. For example, you can include your company’s logo, name, and address in the page’s header, use the background that is used throughout your company, or include standard text in the header or footer of the HTML document.

Note   You can use an HTML template when you save data as static HTML documents, when you save datasheets as IDC/HTX files, and when you save a form or datasheet as an Active Server Page.

The HTML template can be any HTML document; that is, a text file that includes HTML tags and user-specified text and references. In addition, the HTML template can include placeholders that tell Access where to insert certain pieces  of data in the HTML documents. When data is saved as HTML documents, the placeholders are replaced with data. The following table describes each of the placeholders that you can use in an HTML template.

PlaceholderDescriptionLocation
<!--AccessTemplate_Title-->The name of the table, query, form, or report placed in the title bar of the Web browserBetween <TITLE> and </TITLE>
<!--AccessTemplate_Body-->The output of the table, query, form, or reportBetween <BODY> and </BODY>
<!--AccessTemplate_FirstPage-->An HTML anchor tag that links to the first page of a reportBetween <BODY> and </BODY> or after </BODY>
<!--AccessTemplate_PreviousPage-->An HTML anchor tag that links to the page previous to the current report pageBetween <BODY> and </BODY> or after </BODY>
<!--AccessTemplate_NextPage-->An HTML anchor tag that links to the next page after the current report pageBetween <BODY> and </BODY> or after </BODY>
<!--AccessTemplate_LastPage-->An HTML anchor tag that links to the last page of a reportBetween <BODY> and </BODY> or after </BODY>
<!--AccessTemplate_PageNumber-->The current page number Between <BODY> and </BODY> or after </BODY>

Saving Table, Query, and Form Datasheets as IDC/HTX Files

With Access, you can save a table, query, or form datasheet as Internet Database Connector/HTML extension (IDC/HTX) files that generate HTML documents by querying a copy of your database located on a Web server. In contrast to static HTML documents, which contain the data that was current at the time the HTML document was created, IDC/HTX files generate an HTML page with current data from your database; therefore, the HTML documents that they generate are called dynamic.

To save a table, query, or form datasheet as IDC/HTX files

  1. In the Database window, click the table, query, or form you want to save.

  2. On the File menu, click Export.

  3. In the Save As Type box, click Microsoft IIS 1-2 (*.htx/*.idc).

  4. Specify the file name and location to save the files, and then click Save.

  5. In the HTX/IDC Output Options dialog box, specify:
    • The data source name that will be used for a copy of the current database.

    • A user name and password, if required to open the database.

    • An HTML template, if you want Access to merge one with the HTML extension (HTX) file.

    Note   You can specify any of these items later, except the HTML template, by editing the resulting IDC file in a text editor such as Notepad.

You can also save a table, query, or form datasheet as IDC/HTX files by using the OutputTo method in code or the OutputTo action in macros.

See Also   For more information about saving table, query, or form datasheets as IDC/HTX files, type Export to IDC/HTX or OutputTo in the Office Assistant or on the Answer Wizard tab in the Help window, and then click Search.

How the Internet Database Connector Works

When you save a table, form, or query datasheet as Internet Connector files, Access creates two files: an Internet Database Connector (IDC) file and HTML extension (HTX) file. These files are used to generate a Web page that displays current data from your database.

An IDC file contains the necessary information to connect to a specified Open Database Connectivity (ODBC) data source and to run an SQL statement that queries the database. The information needed to connect to the database includes the data source name, and if user-level security is established for the database, the user name and password required to open the database. For example, if you save the Current Product List query datasheet from the Northwind sample application as IDC/HTX files, Access creates the following IDC file:

Datasource:Northwind
Template:Current Product List.htx
SQLStatement:SELECT [Product List].ProductID, [Product List].ProductName
+FROM Products AS [Product List]
+WHERE ((([Product List].Discontinued)=No))
+ORDER BY [Product List].ProductName;

Password:
Username:

An IDC file also contains the name and location of an HTML extension (HTX) file. The HTX file is a template for the HTML document; it contains field merge codes  that indicate where the values returned by the SQL statement should be inserted. For example, if you save the Current Product List query datasheet from the Northwind sample application as IDC/HTX files, Access creates the following  HTX file:

<HTML>
<TITLE>Current Product List</TITLE>
<BODY>
<TABLE BORDER=1 BGCOLOR=#ffffff><FONT FACE="Arial" COLOR=#000000>
<CAPTION><B>Current Product List</B></CAPTION>

<THEAD>
<TR>
<TD><FONT SIZE=2 FACE="Arial" COLOR=#000000>Product ID</FONT></TD>
<TD><FONT SIZE=2 FACE="Arial" COLOR=#000000>Product Name</FONT></TD>
</TR>
</THEAD>
<TBODY>
<%BeginDetail%>
<TR VALIGN=TOP>
<TD ALIGN=RIGHT><FONT SIZE=2 FACE="Arial" COLOR=#000000><%ProductID%><BR></FONT></TD>
<TD><FONT SIZE=2 FACE="Arial" COLOR=#000000><%ProductName%><BR></FONT></TD>
</TR>
<%EndDetail%>
</TBODY>
<TFOOT></TFOOT>
</BODY>
</HTML>

Access saves the HTX file to be used with an IDC file with the same name as the IDC file, except with an .htx file name extension rather than an .idc file name extension. After the database information has been merged into the HTML document, it is returned to the Web browser.

If you open Current Product List.idc from a Microsoft Internet Information Server that has an appropriately defined Northwind data source name (DSN), the Web page shown in the following illustration is generated.

Aa188212.ba1003(en-us,office.10).jpg

Note   You can also reference an HTML template when you create IDC and HTX files. An HTML template contains additional HTML code to enhance the appearance of the resulting pages. If you specify an HTML template, it is merged with the HTX file. For information about the format of an HTML template, see “Using an HTML Template When You Save Data as HTML Documents” earlier in this chapter.

See Also   For information about how to publish IDC/HTX files, type “Publish web pages” in the Office Assistant or on the Answer Wizard tab in the Help window, and then click Search. For more information about Microsoft Internet Information Server, see the Microsoft Windows NT Server Web site.

Tip   You can learn more about applications that use IDC/HTX files by reading about the Job Forum application. For information about the Job Forum application, see the Microsoft Developer Network (MSDN) Web site. For applications that require many users to access the database simultaneously, you should consider upsizing the Access database back-end server to Microsoft SQL Server. For information about upsizing an Access database to Microsoft SQL Server, type Upsize in the Office Assistant or on the Answer Wizard tab in the Help window, and then click Search.

Saving Table, Query, and Form Datasheets as Active Server Pages

You can save table, query, and form datasheets as Active Server Pages. When you open a datasheet saved as an Active Server Page, Access displays current data from a copy of your database located on an Internet server, much like IDC/HTX files do. However, unlike IDC/HTX files, Active Server Pages require only one file per datasheet. The ASP file uses VBA scripting to establish a connection to the database on the server, and contains information that it uses to format the datasheet.

To save a form or datasheet as an Active Server Page

  1. In the Database window, click the form or datasheet you want to save.

  2. On the File menu, click Export.

  3. In the Save As Type box, click Microsoft Active Server Pages(*.asp).

  4. Specify the file name and location to save the file, and then click Save.

  5. In the Microsoft Active Server Pages Output Options dialog box, specify:
    • The data source name that will be used for a copy of the current database.

    • A user name and password, if required to open the database.

    • An HTML template, if you want Access to merge one with the Active Server Page.

      See Also   For information about HTML templates, see “Using an HTML Template When You Save Data as HTML Documents” earlier in this chapter.

    • The URL for the server where the Active Server Page will reside.

    • The Session Timeout setting, which determines how long a connection to the server is maintained after the user stops working with the Active Server Page.

You can also save table, query, and form datasheets as Active Server Pages by using the OutputTo method in code or the OutputTo action in macros.

See Also   For more information about saving forms and datasheets as Active Server Pages, type Export to ASP or OutputTo in the Office Assistant or on the Answer Wizard tab in the Help window, and then click Search. For information about how to publish ASP files, type Publish web pages in the Office Assistant or on the Answer Wizard tab in the Help window, and then click Search. For more information about Microsoft Internet Information Server, see the Microsoft Windows NT Server Web site.

Synchronizing Database Replicas over the Internet

With Microsoft Access 2000, you can synchronize replicas over the Internet. Before  you can synchronize over the Internet, you must configure your Internet server for replication. To configure your Internet server, you need Replication Manager, which includes a wizard that takes you through the configuration process.

See Also   For more information on Replication Manager, see “Replication Manager” in Chapter 16, “Multiuser Database Solutions,” in the Microsoft Office 2000/Visual Basic Programmer's Guide (Microsoft Press, 1999) or type replication in the Office Assistant or on the Answer Wizard tab in the Help window, and then click Search.

Importing and Linking Data on the Internet

With Access, you can import or link data from HTML tables or other  data sources on an Internet server. You can also export data in your database to  an Internet server.

Importing and Linking Data from HTML Tables

You can import or link data formatted as an HTML table to an Access database. Before Access imports or links the data, it copies the data into the local cache. Whenever you open a linked table, Access makes a local copy from the original on the Internet or an intranet before opening it. For this reason, the data in the table is read-only. Similarly, if you export the linked HTML table to an HTML file, Access exports it to a local file, not the original file on the Internet.

To import or link data from HTML tables

  1. On the File menu, point to Get External Data, and then click either Import or Link Tables.

  2. In the Files Of Type list, click HTML Documents (*.htm; *.html).

  3. Specify the file to import or link from by using any of the following methods:
    • Use the Look In box and the list of files below it to browse through the file system on your local hard drive or LAN.

    • In the File Name box, type a valid http:// or ftp:// URL.

    • In the Look In box, click Internet Locations (FTP) and select a previously defined FTP site.

    • In the Look In box, click Add/Modify FTP Locations, then specify a new FTP site and browse its files.
  4. Click Import or Link.

When you import or link data from an HTML table, Access parses the information contained within the HTML tags. The primary HTML tags that define tables are:

HTML tag pairDescription
<TABLE>...</TABLE>Specifies the beginning and end of the table.
<TH...>...</TH>Specifies table header cells.
<TR...>...</TR>Specifies a row in a table.
<TD...>...</TD>Specifies table data cells.
<CAPTION...>...</CAPTION>Specifies the table’s caption, usually at the beginning or end of the table.

Access applies the following rules when it interprets the progression of tags and tag pairs within the HTML table it imports or links:

  • The <TD> tag pair may be closed with either a </TD> tag or a </TH> tag.

  • The <TH> tag pair may be closed with either a </TH> tag or a </TD> tag.

  • The <TR> tag is not required to start a new row. If a <TD> tag follows a </TR> tag, Access assumes this is the beginning of a new row.

  • If a </TABLE> tag is not preceded by a </TR> tag, then Access assumes this is the end of the row.

In many cases, a table cell can display something other than text. If a table cell contains an embedded graphic file, there is a <IMG SRC...> tag associated with it. This tag may or may not have additional text that would be displayed. If additional text is present, Access imports it, but Access doesn’t import the embedded graphic and the tag that defines it. However, this is not true of embedded anchor <A HREF> tags; Access imports anchor tags as Hyperlink fields.

HTML tables can contain lists that are embedded within a table cell. Lists in an HTML table cell are formatted with the <LI> and </LI> tags. Access inserts a carriage return and line feed (<CR><LF>) after each list item and imports each item in the list as a separate field for that record.

HTML tables can also contain tables that are embedded within a table cell. You can import these as separate tables.

To achieve the most predictable results, import simple HTML tables with a fixed number of fields per record, without embedded lists or tables.

Importing and Linking Data from Other Data Sources on Internet Servers

You can import and link any Access-compatible external data file, except Access databases, ODBC data sources, and text files by using an FTP or HTTP connection. An FTP or HTTP connection is only maintained long enough to perform a single transaction. Therefore, when you import or link a file located on an FTP or HTTP server, Access copies the data file to the user’s local cache. Access then imports or links to the data file in the local cache. For this reason, files linked with an FTP or HTTP connection are read-only.

To import or link data from external data sources on Internet servers

  1. On the File menu, point to Get External Data, and then click either Import or Link Tables.

  2. In the Files Of Type list, select the type of file you want to import or link.

  3. To specify an FTP server, use any of the following methods:
    • In the File Name box, type a valid ftp:// URL.

    • In the Look In box, click Internet Locations (FTP) and select a previously defined FTP site.

    • In the Look In box, click Add/Modify FTP Locations, then specify a new FTP site and browse its files.

    To specify an HTTP server:

    • In the File Name box, type a valid http:// URL.
  4. Click Import or Link.

Setting Up a Personal Web Server

Microsoft provides two products that make it easy to create a personal Web server on your computer for low-volume Web publishing: Microsoft Personal Web Server and Microsoft Peer Web Services. These products are ideal for publishing departmental home pages, personal home pages, or small-scale Web applications on your company’s intranet.

Although Personal Web Server and Peer Web Services are intended for small-scale Web publishing, they provide most of the same services and features as Microsoft Internet Information Server, a robust Web server intended for high-volume Web publishing. You can use Personal Web Server or Peer Web Services to develop and test your Web applications, and then transfer them to a Web server running Microsoft Internet Information Server.

Both Personal Web Server and Peer Web Services can:

  • Publish Web pages on the Internet or over a LAN on an intranet by using the HTTP service.

  • Support Microsoft ActiveX® controls.

  • Transmit or receive files by using the FTP service.

  • Run Internet Server API (ISAPI) and Common Gateway Interface (CGI) scripts.

  • Send queries to ODBC data sources by using the Internet Database Connector component (Httpodbc.dll).

  • Support the Secure Sockets Layer.

In addition, Peer Web Services can:

  • Use pass-through security to Microsoft® Windows NT® Server and Novell NetWare.

  • Use local-user security if Microsoft File and Print Sharing are not installed.

  • Perform remote administration by using a Web-based application.

  • Distribute documents by using the Gopher service.

Installation Requirements

To run Personal Web Server or Peer Web Services, you must meet the following installation requirements.

Personal Web Server

  • A computer with Windows 98 installed.

  • A CD-ROM drive for the installation compact disc.

  • Adequate disk space for your information content.

Peer Web Services

  • A computer with Windows NT Workstation version 4.0 installed.

  • A CD-ROM drive for the installation compact disc.

  • Adequate disk space for your information content. It is recommended that all drives used with Peer Web Services be formatted with the Windows NT File System (NTFS).

Publication Requirements

When using Personal Web Server or Peer Web Services, each computer you want to access the server must have Transmission Control Protocol/Internet Protocol (TCP/IP) installed. The TCP/IP protocol is included with Windows 98 and Windows NT Workstation version 4.0. To install and configure the TCP/IP protocol and related components, double-click the Network icon in Control Panel. Each system must meet additional requirements depending on whether you want to use the server on an intranet or the Internet.

Intranet Publication Requirements

  • A network adapter card and local area network (LAN) connection.

  • The Windows Internet Name Service (WINS) server or the Domain Name System (DNS) server installed on a computer in your intranet. WINS and DNS run only on Microsoft® Windows NT® Server. This step is optional, but it does allow users to use “friendly names” instead of IP addresses when connecting to your server.

Internet Publication Requirements

  • An Internet connection and Internet Protocol (IP) address from your Internet Service Provider (ISP).

  • DNS registration for that IP address. This step is optional, but it does allow users to use “friendly names” instead of IP addresses when connecting to your server. For example, “microsoft.com” is the friendly domain name registered to Microsoft. Within the microsoft.com domain, Microsoft has named its World Wide Web (WWW) server “www.microsoft.com.” Most ISPs can register your domain names for you.

  • A network adapter card suitable for your connection to the Internet.

Installing Microsoft Personal Web Server

Microsoft Personal Web Server 4.0 is a Windows 98 Internet tools component. You can install it from the Windows 98 CD by running setup.exe in the \add-ons\pws folder.

Installing Peer Web Services

The files to install Peer Web Services are provided on the Microsoft Windows NT Workstation version 4.0 Setup CD-ROM.

To install Peer Web Services

  1. Click the Windows Start button, point to Settings, and click Control Panel.

  2. Double-click Network.

  3. Click the Services tab, and then click Add.

  4. In the Network Service list, double-click Peer Web Services.

  5. This starts the installation process. You may be required to supply additional files from your Windows NT Setup disks.

  6. In the first Microsoft Peer Web Services Setup dialog box, click OK.

  7. In the second Microsoft Peer Web Services Setup dialog box, select which services you want to install, and then click OK.

In the Publishing Directories dialog box, specify the directories you want to use for each service, or accept the default directories, and then click OK.

Show: