ACC2000: Reference Data in Lookup Fields Is Ignored During Export to Internet Formats (207845)


The information in this article applies to:

  • Microsoft Access 2000
This article was previously published under Q207845
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).

SYMPTOMS

After you create Lookup fields in tables, the Lookup data is ignored when you export the table to Hypertext Markup Language (HTML), Internet Database Connector (IDC), or Active Server Pages (ASP) format. Data from the bound column for the Lookup field is displayed in the Web browser rather than as the Lookup data that you see when you open the table in Datasheet view in Microsoft Access.

CAUSE

If you exported data to HTML format by using the Export command on the File menu, you did not click to select the Save Formatted check box in the Export Table Table Name As dialog box.

If you exported data to IDC or ASP format, the SQL statement that Microsoft Access generated contained the actual data that was stored in the table's Lookup field. The bound column of a Lookup field's RowSource property is what is actually stored in the table, rather than what you see in Datasheet view of the table in Microsoft Access.

RESOLUTION

If you want to create static HTML files, click Export on the File menu, and then click to select the Save Formatted check box in the Export Table Table Name As dialog box.

If you are exporting to IDC or ASP format, create an AutoLookup query that includes the original table, as well as the table from which the Lookup field obtains its data. Use the corresponding fields from the Lookup table in the query instead of the Lookup fields from the original table. Then export the query to IDC or ASP format.

For example, if you open the sample database Northwind.mdb and view the Orders table in Datasheet view, you see the complete customer name in the Customer column. However, if you export the Orders table to IDC or ASP format, only the CustomerID is exported; CustomerID is the bound column in the Customer Lookup field in the Orders table. If you create an AutoLookup query that contains both the Customers and Orders tables, you can add all the fields from the Orders table to the query design grid except the CustomerID field; add the CompanyName field from the Customers table instead. When you export the query, it presents the same data as the Orders table, including the full customer name.

MORE INFORMATION

Steps to Reproduce Behavior

To reproduce this behavior in exporting a static HTML file, which is the same if you export to IDC or ASP file formats, follow these steps:

  1. Start Microsoft Access and open the sample database Northwind.mdb.
  2. Open the Products table in Design view.
  3. Select the SupplierID field, and then click the Lookup tab in the Field Properties section of the Design window.

    Information on this tab indicates that SupplierID is a Lookup field.
  4. Close the Products table, and leave it selected in the Database window.
  5. On the File menu, click Export.
  6. In the Export Table 'Products' As dialog box, select a folder on your Microsoft Internet Information Server (IIS) where you have Read permission, or save the file locally and copy the HTML file to the your IIS Web server folder after the export is complete.
  7. In the Export Table 'Products' As dialog box, select HTML Documents (*.html; *.htm) in the Save As Type box, and then type Products.html in the File Name box. Verify that the Save Formatted check box is not selected, and then click Save.
  8. Start your Web browser, and type the Uniform Resource Locator (URL) that you want in the Address box to view the Products.html file. Notice that the URL depends upon where your files are located on the Web server. For example, if you saved Products.html in the Wwwroot folder of your Web server, type:

    http://servername/products.html

    When the Products.html file opens, notice that the SupplierID column shows the actual SupplierID code and not the Company Name.

REFERENCES

For more information about autolookup queries, click Microsoft Access Help on the Help menu, type create an autolookup query that automatically fills in data in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

For more information about saving Microsoft Access objects in HTML format, click Microsoft Access Help on the Help menu, type export a datasheet to static html format in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For more information about exporting to IDC and ASP pages, click Microsoft Access Help on the Help menu, type how your web server processes server-generated html format in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

For more information about configuring Microsoft Internet Information Server permissions, please refer to the IIS Help Index.
Modification Type:MinorLast Reviewed:1/26/2005
Keywords:kbinterop kbprb KB207845
©2004 Microsoft Corporation. All rights reserved.