ACC97: ASP Files Display Hyperlinks as Text in Web Browser

ID: Q163706

The information in this article applies to:

SYMPTOMS

Moderate: Requires basic macro, coding, and interoperability skills.

Hyperlink fields in an Active Server Pages (ASP) file created with Microsoft Access 97 are displayed as text with number signs (#) when you view them with a Web browser.


CAUSE

Data that is stored in a Microsoft Access hyperlink field is stored in three parts (displaytext, address, and subaddress) separated by number signs. This is different from hyperlinks in HTML files, which are created using the <A> tag. Microsoft Access outputs the data in a hyperlink field as it exists, without adding the <A> tags or parsing the data into an HTML format.


RESOLUTION

Create a select query that parses the hyperlink field and adds the appropriate <A> tags. Then export the query to an ASP file, and modify the file so the hyperlink field is not HTML encoded.

If you do not modify the ASP file, the < and > symbols are translated to &lt and &gt respectively, and the hyperlink is still displayed as text.

NOTE: If you create form based on the query and export it to ASP format, the hyperlinks are not preserved. This method does not work with Microsoft Access forms exported to ASP format.

The following example demonstrates how to create a select query that you can export to ASP format so the hyperlink fields are preserved when you view them in a Web browser. The query contains a column that parses the HomePage Hyperlink field in Northwind's Suppliers table, and adds HTML tags to preserve the links.

NOTE: This example contains information about editing ASP files. It assumes that you are familiar with Active Server, Visual Basic Scripting, and editing HTML files. Microsoft Product Support professionals do not support modification of any HTML, HTX, IDC, or ASP files.

  1. Start Microsoft Access 97 and open the sample database Northwind.mdb.


  2. Create a new query in Design view based on the Suppliers table.

    NOTE: In the field expression below, an underscore (_) at the end of a line is used as a line continuation character. Remove the underscore from the end of each line when you create this example. 
    
        Query: HyperASP
        ------------------------------------------------------------------
        Type: Select Query

        Field: SupplierID
           Table: Suppliers
        Field: GoodHyper: "<A HREF=""" & Right([HomePage],Len(IIf(IsNull _
               ([HomePage]),"",[HomePage]))-InStr(IIf(IsNull([HomePage]), _
               "",[HomePage]),"#")) & """>" & IIf(Left([HomePage], _
               IIf(InStr(IIf(IsNull([HomePage]),"",[HomePage]),"#")>1, _
               InStr(IIf(IsNull([HomePage]),"",[HomePage]),"#")-1,0))="", _
               [HomePage],Left([HomePage],IIf(InStr(IIf(IsNull _
               ([HomePage]), "",[HomePage]),"#")>1,InStr(IIf(IsNull _
               ([HomePage]),"",[HomePage]),"#")-1,0))) & "</A>"
    The expression in the GoodHyper column is so long because it must account for nulls in the hyperlink field, and the NZ function cannot be used with ASP. The expression must also handle hyperlinks that do not contain the displaytext portion of the hyperlink field.


  3. Save the HyperASP query and close it.


  4. Select the HyperASP query in the Database window, and then on the File menu, click Save As/Export.


  5. In the Save As dialog box, click "To an External File or Database," and then click OK.


  6. In the "Save Query 'HyperASP' In" dialog box, select Microsoft Active Server Pages (*.asp) in the Save As Type box, and type HyperASP.asp in the File Name box. Select a folder to store the files, and then click Export.


  7. In the Microsoft Active Server Pages Output Options dialog box, type the name of a System DSN on your Web server that points to the Northwind sample database in the Data Source Name box.

    For more information on how to create a System DSN, search the Help Index for "ODBC, setting up data sources," and see the following article in the Microsoft Knowledge Base:


    8. Q159682 ACC97: "Data Source Name Not Found" Err Msg Opening Web Page
  8. In the Server URL box, type a Uniform Resource Locator (URL) of the Web server location where your ASP files will be stored. For example, if you store the ASP files in the \ASPsamp folder on the \\PubTest server, type http://pubtest/aspsamp/ in the Server URL box. Click OK to create the ASP files.


  9. Use Notepad or another text editor to open the HyperASP.asp file. Near the bottom of the file you will see the following text: 
    
    COLOR=#000000><%=Server.HTMLEncode(rs.Fields("GoodHyper").Value)%>
    <BR></FONT></TD>
    Remove the Server.HTMLEncode portion of the text so it looks like this:


    10. 
   COLOR=#000000><%=rs.Fields("GoodHyper").Value%><BR></FONT></TD>
  10. Save the HyperASP.asp file and close it.


  11. Copy HyperASP.asp to the folder on your Web server computer that you indicated in step 8. You must have Execute permission in this folder.

    For more information about configuring Microsoft Internet Information Server (IIS) permissions, please refer to the IIS Help Index, and see the following article in the Microsoft Knowledge Base:


    12. Q162975 ACC97: Permissions Necessary to View HTML, IDC, and ASP Files
  12. Start Microsoft Internet Explorer 3.0, or another Web browser program.


  13. Type the URL in the address box of your Web browser to view HyperASP.asp, for example, http://pubtest/aspsamp/HyperASP.asp. Note that the URL depends upon where your files are located on the Web server.


  14. Your Web browser returns the SupplierID and the HomePage hyperlink field with the hyperlinks preserved.



MORE INFORMATION

Steps to Reproduce Behavior

  1. Start Microsoft Access 97 and open the sample database Northwind.mdb.


  2. Select the Suppliers table in the Database window, and then on the File menu, click Save As/Export.


  3. In the Save As dialog box, click "To an External File or Database," and then click OK.


  4. In the "Save Table 'Suppliers' In" dialog box, select Microsoft Active Server Pages (*.asp) in the Save As Type box, and type Suppliers.asp in the File Name box. Select a folder to store the files, and then click Export.


  5. In the Microsoft Active Server Pages Output Options dialog box, type the name of a System DSN on your Web server that points to the Northwind sample database in the Data Source Name box. In the Server URL box, type a URL that points to the Web server location where your ASP files will be stored. Click OK to create the Suppliers.asp file.


  6. Copy the Suppliers.asp to a folder on your Web server computer where you have Execute permission.


  7. Start Microsoft Internet Explorer 3.0, or another Web browser program.


  8. Type the URL in the address box of your Web browser to view Suppliers.asp. For example, if you saved your ASP files in a folder called Test in the wwwroot folder of your Web server, type:
    http://<servername>/test/Suppliers.asp
    Note that the URL depends upon where your files are located on the Web Server.


  9. The Web browser returns the HomePage field, but note that the hyperlinks are not preserved, and text containing number signs appears in place of the hyperlinks.



REFERENCES

For more information about exporting ASP files, search the Help Index for "ASP files," or ask the Microsoft Access 97 Office Assistant.

Additional query words: IIS 3.0 PWS Personal Web Server Peer Services 


Keywords          : kbinterop IntPubWiz IntAsp 
Version           : WINDOWS:97; winnt:3.0
Platform          : WINDOWS winnt 
Issue type        : kbprb

Last Reviewed: July 8, 1999