You receive a "Too Many Rows" error message when you use "Analyze It with MS Excel" in Access 2002 (291951)


The information in this article applies to:

  • Microsoft Access 2002
This article was previously published under Q291951
Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).

For a Microsoft Access 2000 version of this article, see 201589.

SYMPTOMS

If a table has more than 16,384 rows, and you click the OfficeLinks button, and then Analyze It with Microsoft Excel, you may receive the following error message:
There are too many rows to output, based on the limitation specified by the output format or by Microsoft Access.
This happens as well with Microsoft Excel 2002, even though the maximum worksheet size for Excel 2002 is 65,536 rows by 256 columns.

CAUSE

For backward compatibility, Microsoft Access defaults to the Excel 5.0 specification, which is limited to 16,384 rows. Also, Microsoft Access does not check the row count before attempting to send the data to Microsoft Excel.

RESOLUTION

To work around this behavior, use one of the following methods:
  • Use Microsoft Excel to import the data from Microsoft Access.
  • In Microsoft Access, on the File menu, click Export and export to Microsoft Excel 97-2002 format. Then open the file in Microsoft Excel.
  • Limit the number of rows sent to Microsoft Excel by creating a query in Microsoft Access and setting the TopValues property to 16,383. Then, analyze the query with Microsoft Excel.

MORE INFORMATION

Steps to Reproduce the Behavior

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

The following steps run a query that appends over 16,384 rows to a new table, and then attempts to analyze that table with Microsoft Excel:
  1. Open the sample database Northwind.mdb.
  2. Make a copy of the Orders table and name it tblBigTable.
  3. Create a new query, and then view it in Design view. Do not add any tables.
  4. In the new query, click SQL View on the View menu, and then type the following SQL statement:
       INSERT INTO tblBigTable ( CustomerID, EmployeeID, OrderDate,
      RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress,
      ShipCity, ShipRegion, ShipPostalCode, ShipCountry )

      SELECT tblBigTable.CustomerID, tblBigTable.EmployeeID,
      tblBigTable.OrderDate, tblBigTable.RequiredDate,
      tblBigTable.ShippedDate, tblBigTable.ShipVia, tblBigTable.Freight,
      tblBigTable.ShipName, tblBigTable.ShipAddress, tblBigTable.ShipCity,
      tblBigTable.ShipRegion, tblBigTable.ShipPostalCode,
      tblBigTable.ShipCountry
      FROM tblBigTable;
  5. Save the query as qryDoubleTable.
  6. When you run this query, it doubles the size of the tblBigTable table by appending it with its own data. Run the append query several times until the record count in tblBigTable exceeds 16,384.
  7. In the Database window, click Tables under Objects, and then click the tblBigTable table.
  8. Click the OfficeLinks button on the toolbar, and then click Analyze it with Microsoft Excel. Microsoft Access tries to export the table to Microsoft Excel.

    Eventually, Microsoft Access generates the error that is mentioned in the "Symptoms" section of this article.
Modification Type:MinorLast Reviewed:8/11/2004
Keywords:kberrmsg kbprb KB291951
©2004 Microsoft Corporation. All rights reserved.