Microsoft KB Archive/198440

= ACC: Data in Excel Workbooks May Change When Opened in Access =

Article ID: 198440

Article Last Modified on 10/16/2003

-

APPLIES TO


 * Microsoft Access 97 Standard Edition
 * Microsoft Excel 97 Standard Edition

-



This article was previously published under Q198440



Novice: Requires knowledge of the user interface on single-user computers.



SYMPTOMS
When you open a linked Microsoft Excel table from within Microsoft Access, the data in the Excel file may unexpectedly change.



CAUSE
You may experience this problem when the following conditions are true:


 * You create a link to an Excel workbook that contains a chart.

-and-
 * You edit the data using Access.



RESOLUTION
To prevent this problem, follow these steps:


 * 1) Start Excel, and on the File menu, click Open.
 * 2) Open the Excel workbook that is linked to the Access database.
 * 3) Start Access and open the database that contains the linked Excel workbook.
 * 4) In the Database window, click the Tables tab.
 * 5) Select the attached Excel table. Click Open
 * 6) Switch to Excel, and then on the File menu, click Save.

Switch back to Access; the data should be correct. You should only have to follow these steps the first time that you edit the linked table from within Access.



STATUS
Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article.



MORE INFORMATION
If the data is not correct, do not edit records from within Access. Instead close the linked table and open the file in Excel. The correct data should still be in the Excel workbook.

Create an Excel File
 Start Excel.  Type the following data:

     A1: Quarter   B1: Sales A2: 1        B2: 150 A3: 2        B3: 200 A4: 3        B4: 250 A5: 4        B5: 300  Select cells A1:B5. On the Insert menu, click Chart.</li> In the Chart Wizard - Step 1 of 4, click Next.</li> In the Chart Wizard - Step 2 of 4, click the Series tab. In the Series list, click Quarter, and then click Remove. In the Category (x) Axis Labels box, type =Sheet1!A2:A5, and then click Finish.</li> On the File menu, click Save. Type XLChange in the File Name box, and then click Save.</li> On the File menu, click Exit.</li></ol>

Attach and Open the File in Access

 * 1) Start Access and click Cancel when you see the Create a New Database Using dialog box.
 * 2) On the File menu, click New Database, and then click OK. Type ACChange in the File Name box, and then click Create.
 * 3) On the File menu, point to Get External Data, and then click Link Tables. In the Files 0f Type list, click Microsoft Excel (*.xls).
 * 4) Select XLChange.xls, and then click Link.
 * 5) In the first dialog box of the Link Spreadsheet Wizard, click Next.
 * 6) In the second dialog box of the Link Spreadsheet Wizard, click to select the First Row Contains Column Headings check box, and then Click Finish. When you get the message "Finished linking table 'Sheet1' to file...," click OK.
 * 7) Click Sheet1, and then click Open.

Note that the data is not the same as what you typed in Excel.
 * 1) Change one of the values, and then move off that record so that it is saved.
 * 2) On the File menu, click Exit.
 * 3) Start Excel and open the XLChange.xls file.

Note that the data is the same as it was in Access.

Additional query words: pra linked table

Keywords: kbbug kbpending KB198440

-

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

© Microsoft Corporation. All rights reserved.