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

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: This happens as well with Microsoft Excel 2000, even though the maximum worksheet size for Excel 2000 is 65,536 rows by 256 columns.

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.

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:

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.