ACC97: Incorrect Records in Query Exported to File

• Microsoft Access 97

SYMPTOMS

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

If you modify an existing query and then export it to another file format without saving it, the expected data may not appear in the resulting file. For example, this behavior can occur when you export a query to a text file (*.txt), to Microsoft Excel 97 (*.xls), or to a dBASE file format (*.dbf).

CAUSE

When you export a query, Microsoft Access uses the SQL property of the QueryDef object to determine which records and fields should be exported. Modifications that you make to a query exist in a temporary object until you save the query. At that time, the SQL property of the QueryDef object is updated.

RESOLUTION

Save the query before you export it to another file format.

MORE INFORMATION

Steps to Reproduce Behavior

  1. Start Microsoft Access and open the sample database Northwind.mdb.

  2. Create the following query based on the Shippers table:

       Query: qryExportTest
       --------------------
       Type: Select Query

       Field: ShipperID
          Table: Shippers
       Field: CompanyName
          Table: Shippers
       Field: Phone
          Table: Shippers

  3. Save the query as qryExportTest.

  4. On the Query menu, click Run. Note that the query returns three
     records.

  5. On the View menu, click Design View and add the following criteria to
     the ShipperID field:

       <> 3

  6. Run the query without saving it. Note that the query returns two
     records.

  7. On the File menu, click Save As/Export.

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

  9. In the Save In dialog box, click Microsoft Excel 97(*.xls) in the Save
     As Type list.

 10. In the File Name box, type "qryExportTest.xls" (without the quotation
     marks), and then click Export.

 11. Start Microsoft Excel and open qryExportTest.xls. Note that in
     addition to the column names, the spreadsheet contains three records
     instead of the two records returned by the unsaved query.

REFERENCES

For more information about exporting, search the Help Index for "exporting data" and display the topic "Export data to another database or file format," or ask the Microsoft Access 97 Office Assistant.