ACC: Data in Excel Workbooks May Change When Opened in Access (198440)


The information in this article applies to:

  • Microsoft Access 97
  • Microsoft Excel 97 for Windows
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.

Steps to Reproduce Behavior

Create an Excel File


  1. Start Excel.
  2. Type the following data: 
          A1: Quarter   B1: Sales
      A2: 1         B2: 150
      A3: 2         B3: 200
      A4: 3         B4: 250
      A5: 4         B5: 300
  3. Select cells A1:B5.
  4. On the Insert menu, click Chart.
  5. In the Chart Wizard - Step 1 of 4, click Next.
  6. 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.
  7. On the File menu, click Save. Type XLChange in the File Name box, and then click Save.
  8. On the File menu, click Exit.

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.
  8. Change one of the values, and then move off that record so that it is saved.
  9. On the File menu, click Exit.
  10. Start Excel and open the XLChange.xls file.

    Note that the data is the same as it was in Access.
Modification Type:MajorLast Reviewed:10/16/2003
Keywords:kbbug kbpending KB198440
©2003 Microsoft Corporation. All rights reserved.