ACC2000: Linked Excel Worksheet Grows by 50 to 100 Percent When Updated in Access (282751)


The information in this article applies to:

  • Microsoft Access 2000
This article was previously published under Q282751
Novice: Requires knowledge of the user interface on single-user computers.

This article applies only to a Microsoft Access database (.mdb).

SYMPTOMS

When you use Microsoft Access 2000 to update a linked Microsoft Excel 97 or Microsoft Excel 2000 worksheet, the size of the Excel file grows by 50 to 100 percent.

RESOLUTION

Open the worksheet in Excel 97 or Excel 2000, and then resave the file. This will restore the file to its original (or expected) size.

MORE INFORMATION

Steps to Reproduce the Behavior

  1. Open the sample database Northwind.mdb.
  2. On the View menu, point to Database Objects, and then click Tables.
  3. Right-click the Order Details table, and then click Export.
  4. In the Export Table 'Order Details' To dialog box, click Microsoft Excel 97-2000 (*.xls) in the Save as type list, and then click Save.
  5. After the worksheet is saved, use Windows Explorer to determine the current file size of the worksheet (approximately 212 kilobytes (KB)).
  6. Create a new, blank Access database.
  7. On the File menu in the new database, point to Get External Data, and then click Link Tables.
  8. In the Link dialog box, click Microsoft Excel (*.xls) in the Files of type list, click Order Details.xls, and then click Link.
  9. In the Link Spreadsheet Wizard, click Next, click to select the First Row Contains Column Headings check box, click Finish, and then click OK when you are prompted that the wizard has finished linking the table.
  10. On the View menu, point to Database Objects, and then click Queries.
  11. Click New, click Design View in the New Query dialog box, and then click OK.
  12. Click Close to close the Show Table dialog box, and then click SQL View on the View menu.
  13. Type or paste the following SQL statement in the Query1: Select Query window:

    UPDATE Order_Details SET UnitPrice = [UnitPrice]*1.5, Quantity = [Quantity]+100, Discount = [Discount]/2;

  14. On the Query menu, click Run.
  15. Click Yes to run the action, and then click Yes to update the records. Note that you have simply updated existing records. You have not added new records. However, Windows Explorer shows that Order Details.xls doubled in size (from approximately 212 KB to 416 KB, a nearly 100 percent growth).
Modification Type:MajorLast Reviewed:6/25/2004
Keywords:kbprb KB282751
©2004 Microsoft Corporation. All rights reserved.