ACC97: IDC Files Display Hyperlinks as Text in Web Browser

• Microsoft Access 97
• Microsoft Internet Information Server version 2.0 and 3.0

SYMPTOMS

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

Hyperlink fields in an internet database connector (IDC) 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.

This issue is not specific to a single Internet server.

RESOLUTION

Create a select query that parses the hyperlink field and adds the appropriate <A> tags. Then export the query to an IDC file.

The following example demonstrates how to create a select query that you can export to IDC/HTX format so that 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.

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: HyperIDC
         ------------------------------------------------------------------
         Type: Select Query
   

         Field: SupplierID
            Table: Suppliers
   
         Field: GoodHyper: "<A HREF=""" & IIf(IsNull([HomePage]),""">", _
                IIf(InStr(1,[HomePage],"#")=1,Mid([HomePage],2, _
                InStr(2,[HomePage],"#")-1) & """>" & Mid([HomePage],2, _
                InStr(2,[HomePage],"#")-2),Mid([HomePage], _
                InStr(1,[HomePage],"#")+1,Len(Mid([HomePage], _
                InStr(1,[HomePage],"#")+1))) & """>" & Left$([HomePage], _
                InStr(1,[HomePage],"#")-1))) & "<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 IDC. The expression must also handle hyperlinks that do
      not contain the displaytext portion of the hyperlink field.
   
   

3. Save the HyperIDC query and close it.

4. Select the HyperIDC 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 'HyperIDC' In" dialog box, select Microsoft IIS 1-2 (*.htx;*.idc) in the Save As Type box, and type HyperIDC.htx in the File Name box. Select a folder to store the files, and then click Export.

7. In the HTX/IDC 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, and then click OK.

   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. The HTX/IDC output creates two files: HyperIDC.htx and HyperIDC.idc. Copy the two files to a folder on your Web server computer where you have Execute permission.

   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: Q160754
         TITLE     : ACC97: Error "HTTP/1.0 403 Access Forbidden" Browsing IDC
                     Page
   
   

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

10. Type the Uniform Resource Locator (URL) in the address box of your Web browser to view HyperIDC.idc. For example, if you saved your IDC files in a folder called Test in the wwwroot folder of your Web Server, type:

    http://<servername>/test/HyperIDC.idc

    Note that the URL depends upon where your files are located on the Web server.

11. The 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 IIS 1-2 (*.htx;*.idc) in the Save As Type box, and type Suppliers.htx in the File Name box. Select a folder to store the files, and then click Export.

5. In the HTX/IDC 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, and then click OK.

6. The HTX/IDC output creates two files: Suppliers.htx and Suppliers.idc. Copy the two files 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.idc. For example, if you saved your IDC files in a folder called Test in the wwwroot folder of your Web server, type:

         http://<servername>/test/Suppliers.idc
   

   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.