Microsoft KB Archive/163706

= ACC97: ASP Files Display Hyperlinks as Text in Web Browser =

Article ID: 163706

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Access 97 Standard Edition
 * Microsoft Internet Information Server 3.0

-



This article was previously published under Q163706



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



SYMPTOMS
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  tag. Microsoft Access outputs the data in a hyperlink field as it exists, without adding the  tags or parsing the data into an HTML format.



RESOLUTION
Create a select query that parses the hyperlink field and adds the appropriate  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 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.

 Start Microsoft Access 97 and open the sample database Northwind.mdb.  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: "" & 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))) & "" 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.  Save the HyperASP query and close it.</li> Select the HyperASP query in the Database window, and then on the File menu, click Save As/Export.</li> In the Save As dialog box, click "To an External File or Database," and then click OK.</li> 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.</li> 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:

159682 ACC97: "Data Source Name Not Found" Err Msg Opening Web Page

</li> 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.</li>  Use Notepad or another text editor to open the HyperASP.asp file. Near the bottom of the file you will see the following text: <pre class="fixed_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: <pre class="fixed_text">  COLOR=#000000><%=rs.Fields("GoodHyper").Value%><BR></FONT></TD> </li> Save the HyperASP.asp file and close it.</li> 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:

162975 ACC97: Permissions Necessary to View HTML, IDC, and ASP Files

</li> Start Microsoft Internet Explorer 3.0, or another Web browser program.</li> 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.</li> Your Web browser returns the SupplierID and the HomePage hyperlink field with the hyperlinks preserved.</li></ol>

<div class="moreinformation_section">

Steps to Reproduce Behavior
<ol> Start Microsoft Access 97 and open the sample database Northwind.mdb.</li> Select the Suppliers table in the Database window, and then on the File menu, click Save As/Export.</li> In the Save As dialog box, click "To an External File or Database," and then click OK.</li> 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.</li> <li>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.</li> <li>Copy the Suppliers.asp to a folder on your Web server computer where you have Execute permission.</li> <li>Start Microsoft Internet Explorer 3.0, or another Web browser program.</li> <li>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://%3Cservername%3E/test/Suppliers.asp

Note that the URL depends upon where your files are located on the Web Server.</li> <li>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.</li></ol>

<div class="references_section">