Locate Pivot Table Source Access File And Query

For a pivot table that’s based on a Microsoft Access query, you might need to find out which database and query were used as the source data.

In Excel, if you click the Change Data Source command, on the Ribbon’s Options tab, you can see the connection name in the Change PivotTable Data Source dialog box. However, that bit of information isn’t too useful — it doesn’t show you the name and path of the Access file, and you can’t see which query was used to create the pivot table.

sourcequery01 

View the Connection Properties

To find the details on the source data’s Access file and query, you can follow these steps:

  • In the Excel file, select a cell in the pivot table
  • On the Ribbon’s Data tab, in the Connections group, click Properties.

ribbondataproperties

  • In the Connection Properties dialog box, click the Definition tab.
    • In the Connection File box, you can see the name and path of the database.
    • In the Command Text box is the name of the Access query.
  • Click Cancel to close the Connection Properties dialog box.

sourcequery02

______________

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.