0

In Excel 2016, I can create a connection to an Oracle database (via ODBC) using Microsoft Query or the Connection Wizard.

Creating a connection using one of those methods works as expected. The external data is brought into a sheet as a table. And a connection is created.

Next, I want to reuse the connection when creating PivotTables.


Option 1:

Click a cell within the Excel table and insert a PivotTable based on that named table/range.

That technically works; the PivotTable directly references the Excel table/range.

But there seems to be a quirk: In order to refresh the data in the PivotTable, I need to hit Refresh All twice. If I only hit Refresh All once, then only the table gets refreshed, not the PivotTable.

That quirk is confusing for users. The table and PivotTable become out-of-sync if Refresh All is only clicked once. So I'm exploring alternative options.

Related: "When you do a refresh all, all the refreshes launch in parallel."


Option 2:

I want to try to reuse the connection when creating a new PivotTable from scratch. Instead of using option #1 (clicking a cell within the table and inserting a PivotTable based on that named table/range), I can click outside of the table, and insert a PivotTable to create a PivotTable by manually selecting the existing connection.

When I do that, it technically works.

But when I look at the Workbook's list of connections, I see that Excel has actually made a copy of the connection, rather than simply use the existing connection.

That's not what I want; I want to reuse the connection to avoid creating multiple redundant connections. I have multiple PivotTables I want to make, and they would all reference the same underlying Oracle data (the table references the same data too). So I have no reason to make multiple identical connections.


Question:

Is there a way to reuse an existing external datasource connection for a PivotTable? (without creating a duplicate connection)

3
  • Based on your research you already seem to be aware, to avoid the parallel refresh issue you EITHER have to 1) use multiple connections or 2) use VBA do a double refresh, possibly more if you have more data manipulation layers (e.g., base powerquery data only connections, reference connections, load to table, and pivot from table would require 4 refereshes). Until MS fixes this, you'll have to pick the route that works best in your organization/environment.
    – gns100
    Aug 15 at 16:54
  • I found this link that provides some interesting details related to parallel refreshes, It was written in 2019 so I don't now if excel was updated to take advantage of this: blog.crossjoin.co.uk/2019/03/26/…
    – gns100
    Aug 15 at 17:12
  • @gns100 It turns out, all I had to do to get rid of the double refresh problem was disable Enable background refresh in the connection properties: i.stack.imgur.com/MGK50.png. Now, only a single refresh is required.
    – User1974
    Aug 15 at 20:03

1 Answer 1

0

It looks like I can solve the double refresh problem in Option #1 by disabling the Enable background refresh setting in the connection properties:

enter image description here

Now, only a single refresh is required.

So I can use Option #1, making Option #2 unnecessary.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.