You can use linked queries to create "live" Excel reports that can be refreshed dynamically. Because a linked query contains the underlying SQL for a report, each time you refresh the report the query is executed and the latest data is retrieved. The dynamic nature of linked query reports makes them ideal for distribution to users who need to see changes to data without waiting for scheduled updates or for new reports to be distributed.
Any user who opens an Excel linked query must have access to a user name and password for the queried database to refresh the data. If a user does not have access to the database, they can only see the original data in the spreadsheet.
If the Excel linked query was created from an ODBC connection that uses a Data Source Name (DSN), the DSN must also exist on the computer where the Excel file is opened to refresh the data.
You need to create an Excel linked query so that the Regional Sales Manager, can track whether the Sales Representatives in his region meet their monthly and quarterly goals. By providing a linked query, you only have to create the query once, and then the Regional Sales Manager can refresh the data at any time to view the latest sales information.
To create an Excel linked query
Select the result set and click one of the following options from the toolbar:
To refresh data in an Excel linked query
If prompted, enter a user name and password to connect to the database and refresh the data.
If the following error displays: "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified", you must specify the DSN used by the query on your computer. Complete the following steps to resolve this issue: