ACC97: MS Excel Links to MS Access Data Not Updated

• Microsoft Access 97
• Microsoft Excel 97 for Windows

SYMPTOMS

Moderate: Requires basic macro, coding, and interoperability skills.

When you open a spreadsheet in Microsoft Excel that has an established link to a Microsoft Access table through the Paste Link command, you may receive the following message:

   The Workbook you opened contains automatic links to
   information in another workbook. Do you want to update
   the workbook with changes made to the other workbook?

      To update all linked information, click Yes.
      To keep the existing information, click No.

       Microsoft Excel

    Remote Data not accessible. Start application "MSACCESS.EXE"?

    Cannot run 'MSACCESS.EXE'. The program or one of its components is
    damaged or missing.

CAUSE

The database that contains the linked table in Microsoft Access is closed.

RESOLUTION

To update the data in a Microsoft Excel spreadsheet that is linked to a table in Microsoft Access, you must have the database that contains the data open in Microsoft Access.

MORE INFORMATION

The Paste link option of the Paste Special command on the Edit menu in Microsoft Excel is used to paste information as a linked object and to keep the copied information up-to-date if the original data changes in the source file. If the source information is not available when the spreadsheet is opened, you will receive error messages and the data will not be updated in the spreadsheet.

Steps to Reproduce Behavior

1. Open Microsoft Access and the sample database Northwind.mdb.

2. Select the Employees table in the Database window. On the Edit menu, click Copy.

3. Open Microsoft Excel 97, and then on the Edit menu, click Paste Special. In the Paste Special box, click Paste Link. In the As box, select Biff5, and then click OK.

4. Save the workbook as Book1, and then close it.

5. Switch back to Microsoft Access, and close the database.

6. Switch back to Microsoft Excel and reopen the spreadsheet that contains the linked table. When prompted to re-establish the links, click Yes. When prompted to start the application, click Yes.

   Note that you receive the error message and the data in the spreadsheet is replaced with #REF! in each cell.

REFERENCES