ACC2000: Error "Too many rows" Using "Analyze It with MS Excel"

ID: Q201589

The information in this article applies to:

Microsoft Access 2000

Advanced: Requires expert coding, interoperability, and multiuser skills.

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

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, on the File menu, click Export and export to Microsoft Excel 97-2000 format. 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: Following the steps in this example will modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and perform 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.

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.

In the Database window, click Tables under Objects, and select the tblBigTable table.

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.

Additional query words: prb


Keywords          : kbdta 
Version           : WINDOWS:2000
Platform          : WINDOWS 
Issue type        : kbprb

Last Reviewed: May 13, 1999