Microsoft KB Archive/282751

= ACC2000: Linked Excel Worksheet Grows by 50 to 100 Percent When Updated in Access =

Article ID: 282751

Article Last Modified on 6/25/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



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.



Steps to Reproduce the Behavior
 Open the sample database Northwind.mdb. On the View menu, point to Database Objects, and then click Tables. Right-click the Order Details table, and then click Export. 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. After the worksheet is saved, use Windows Explorer to determine the current file size of the worksheet (approximately 212 kilobytes (KB)). Create a new, blank Access database.</li> On the File menu in the new database, point to Get External Data, and then click Link Tables.</li> In the Link dialog box, click Microsoft Excel (*.xls) in the Files of type list, click Order Details.xls, and then click Link.</li> 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.</li> On the View menu, point to Database Objects, and then click Queries.</li> Click New, click Design View in the New Query dialog box, and then click OK.</li> Click Close to close the Show Table dialog box, and then click SQL View on the View menu.</li> 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;

</li> On the Query menu, click Run.</li> 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).</li></ol>

Additional query words: prb workbook

Keywords: kbprb KB282751

-

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

© Microsoft Corporation. All rights reserved.