ACC2000: "Too Many Rows" Error Using "Analyze It with MS Excel" (201589)


The information in this article applies to:

  • Microsoft Access 2000
This article was previously published under Q201589
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 2002 version of this article, see 291951.

SYMPTOMS

If a table has more than 16,384 rows, and you click the Office Links button, and then Analyze It with MS Excel, you may receive the following error:
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 2000, even though the maximum worksheet size for Excel 2000 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, Access does not check the row count before attempting to send the data to Excel.

RESOLUTION

To work around this behavior, use one of the following methods:
  • Use Excel to import the data from Access.
  • In Access, click Export on the File menu, and then export to Microsoft Excel 97-2000 format. Make sure that the Save formatted check box in the Export Table dialog box in Access is not selected. Then open the file in Excel.
  • Limit the number of rows sent to Excel by creating a query in Access and setting the TopValues property to 16,384. Then, analyze the query with Microsoft Excel.

MORE INFORMATION

Steps to Reproduce 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 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 view it in Design view. Do not add any tables.
  4. In the new query, on the View menu click SQL View and 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 select the tblBigTable table.
  8. Click the Office links button on the toolbar, and then click Analyze it with Excel. Access tries to export the table to Excel.

    Eventually, Access generates the error mentioned in the "Symptoms" section of this article.
Modification Type:MajorLast Reviewed:6/24/2004
Keywords:kbprb KB201589
©2004 Microsoft Corporation. All rights reserved.