ACC97: MS Excel Links to MS Access Data Not Updated

ID: Q167063

The information in this article applies to:

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.

If you click Yes, you receive the following additional message: 

       Microsoft Excel

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

If you click Yes again, you receive the following error message:
Cannot run 'MSACCESS.EXE'. The program or one of its components is damaged or missing.

If you then click OK, the cells in the spreadsheet are filled with #REF!


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

For more information about linking table data to Microsoft Excel spreadsheets, search the Microsoft Excel Help Index for "Create links to connect to data and files," or ask the Microsoft Excel 97 Office Assistant. 


Keywords          : kberrmsg kbinterop IntpOff IsmOthr 
Version           : WINDOWS:97
Platform          : WINDOWS 
Issue type        : kbprb

Last Reviewed: April 22, 1999