ACC97: ASP Files Display Hyperlinks as Text in Web Browser

• Microsoft Access 97
• Microsoft Internet Information Server version 3.0

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 Technical Support engineers 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:

        ARTICLE-ID: Q159682
        TITLE     : 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:

      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:

        ARTICLE-ID: Q162975
        TITLE     : 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.