ACC97: Lookup Fields Ignored When Exporting to Internet Formats

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

SYMPTOMS

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

The data that is displayed in table Lookup fields that you created with the Lookup Wizard is ignored when you export the table to HTML, IDC, or ASP format. The bound column for the Lookup field is displayed in the Web browser, not the lookup data that you see when you open the table in Datasheet view in Microsoft Access.

CAUSE

If you exported to HTML format with the Save As/Export command on the File menu, you did not click to select the Save Formatted check box in the "Save Table <Table Name> In" dialog box.

If you exported to Internet Database Connector (IDC) or Active Server Pages (ASP) format, the SQL statement that Microsoft Access generated contains the actual data that is 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, not necessarily what you see in Datasheet view of the table in Microsoft Access.

RESOLUTION

If you want to create static HTML files, click Save As/Export on the File menu, and then click to select the Save Formatted check box in the "Save Table <Table Name> In" dialog box. If you click Save As HTML on the File menu to create static HTML files, the files you create from tables or queries automatically include the Save Formatted option.

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 QBE grid, except the CustomerID field; add the CompanyName field from the Customers table instead. When you export the query, it contains the same data as the Orders table, including the full customer name.

For more information about creating AutoLookup queries, search the Help Index for "AutoLookup queries," or ask the Microsoft Access 97 Office Assistant.

MORE INFORMATION

Steps to Reproduce Behavior

The following steps reproduce the behavior by exporting a static HTML file. The same behavior will occur if you export to IDC or ASP file formats.

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 under 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 Save As/Export.

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

7. In the "Save Table 'Products' In" 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.

8. In the "Save Table 'Products' In" 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 Save Formatted is not checked, and then click Export.

9. Start your Web browser program, and type the Uniform Resource Locator (URL) in the Address box to view the Products.html file. Note 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, note that the SupplierID column shows the actual SupplierID code and not the Company Name.

REFERENCES

For more information about saving Microsoft Access objects in HTML format, search the Help Index for "saving database objects, saving in Internet/Web formats," or ask the Microsoft Access 97 Office Assistant.

For more information about configuring Microsoft Internet Information Server permissions, please refer to the IIS Help Index.