ACC: Data in Excel Workbooks May Change When Opened in Access
ID: Q198440
|
The information in this article applies to:
-
Microsoft Access 97
-
Microsoft Excel 97 for Windows
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:
- Start Excel, and on the File menu, click Open.
- Open the Excel workbook that is linked to the Access database.
- Start Access and open the database that contains the linked Excel
workbook.
- In the Database window, click the Tables tab.
- Select the attached Excel table. Click Open
- 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
- 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.
- In the Chart Wizard - Step 1 of 4, click Next.
- 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.
- On the File menu, click Save. Type XLChange in the File Name box,
and then click Save.
- On the File menu, click Exit.
Attach and Open the File in Access
- Start Access and click Cancel when you see the Create a New Database
Using dialog box.
- On the File menu, click New Database, and then click OK. Type ACChange
in the File Name box, and then click Create.
- On the File menu, point to Get External Data, and then click Link
Tables. In the Files 0f Type list, click Microsoft Excel (*.xls).
- Select XLChange.xls, and then click Link.
- In the first dialog box of the Link Spreadsheet Wizard, click Next.
- 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.
- Click Sheet1, and then click Open.
Note that the data is not the same as what you typed in Excel.
- Change one of the values, and then move off that record so that it is
saved.
- On the File menu, click Exit.
- 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
|