Microsoft KB Archive/201589

= ACC2000: "Too Many Rows" Error Using "Analyze It with MS Excel" =

Article ID: 201589

Article Last Modified on 6/24/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



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.



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:  Open the sample database Northwind.mdb. Make a copy of the Orders table and name it tblBigTable. Create a new query and view it in Design view. Do not add any tables.  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;  Save the query as qryDoubleTable.</li> 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.</li> In the Database window, click Tables under Objects, and select the tblBigTable table.</li> 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.</li></ol>

Additional query words: prb

Keywords: kbprb KB201589

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.