Share Excel linked Queries
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.
Notes:
-
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.
|
Scenario 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
- Connect to the database.
- Create a query in the Query Builder or Editor.
- Execute the query to display data results.
-
Select the result set and click one of the following options from the toolbar:
- Save the Excel spreadsheet.
- Email or upload the spreadsheet to a shared network folder to share it with colleagues.
To refresh data in an Excel linked query
- Open the spreadsheet with the linked query in Excel. The original data from the query displays.
- In Excel, select Data | Refresh to update the original data with the current data.
-
If prompted, enter a user name and password to connect to the database and refresh the data.
or
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:
- Click OK.
- Select the Machine Data Source tab, and then click New.
- Use the wizard to select a User Data Source and create a DSN that points to the database.
- Click OK to return to Excel and open an ODBC Driver Connect window.
- Enter a user name and password to refresh the data.
(Excel 2003)