Link Excel To Access

[James]

Can you please tell me how to enable the links command in excel (Edit
Menu...Links) Basically though I would like to be able to open up Excel and
automatically pull in data from Access, without having to have the user
Refresh Data. Thanks.

 

[Luke M]

Goto Data - Import Data - New Database Query.
Setup your query to pull from appropriate Access table(s), using crtieria if
desired. Then, edit again under Import Data - Edit Query, setup your query to
refresh upon opening.

Or, use VB to create a Workbook_Open event that automatically refreshes the
query.

 

[James]

Thanks that worked. However when you open up the spreadsheet you are still
promtped with "enable automatic refresh" or disable automatic refresh" is
their a way to supress this. Its not that big of a deal, just don't want to
give the user any options. Also what is the purpose of the Links command, and
how do I enable it. Thanks

 

[Luke M]

The links function is for when you have a cell formula reference to another
workbook. Example:

='C:/My documents/Another workbook.xlsSheet!'A1

You could then use the Links command to edit the file paths (very handy if a
workbook is moved). These formula produce a similar opening message of "do
you want to update" but its slightly different. Because they are formulas, if
you don't update, XL makes note that these formulas are not truly accurate,
and should not try to keep updating them in future calculation cycles.

A query is a one-step data dump from an outside source. XL suggests that you
might want to get the latest data when opening, but it has not problem
handling the data regardless of your choice.