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

ID: Q198440

The information in this article applies to:


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:


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.


Additional query words: pra linked table 


Keywords          : kbdta IntpOff 
Version           : WINDOWS:97
Platform          : WINDOWS 
Issue type        : kbbug

Last Reviewed: April 14, 1999