[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 249
  • Last Modified:

Using multiple SQL data sources in MS Excel query

I need to use more than one datasource in a Excel query however I'm only able to choose and see tables from a single datasource.

How do I create a Excel query that will provide access to multiple datasources?
0
jdr0606
Asked:
jdr0606
  • 5
  • 4
  • 3
  • +1
3 Solutions
 
ScriptAddictCommented:
Well I think you can do one datasource per tab.  So if you can make it work by combining those tabs your gold.  

0
 
jdr0606Author Commented:
Unfortunately that's a work around only and requires VLOOKUPS and other functions to do what a proper query joining multiple datasources could do.
0
 
ScriptAddictCommented:
This should get you a little closer.
Honestly if it was me I'd do it through access.  And then if ness. pull the query to Excel afterwards.

Reference on a Union Table for Excel
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
ScriptAddictCommented:
0
 
jdr0606Author Commented:
I guess I didn't explain enough about my issue.

What I'm trying to create is an Excel spreadsheet using MS Query that can be maintained in native Excel and be able to add additional tables and in some cases tables from different datasources from within MS Query and return results to Excel.

While I can create the query using one datasource and choose tables to join and filter in the query, I'm not seeing the ability to choose and select from a different datasource.
0
 
dlmilleCommented:
You need to create a link/connection of/to the disparate datasources.  One way would be to setup an access query that does that.  THEN you can query that in Excel.  MS Query works with one connection at a time, so your connection already has to have had the disparate datasources linked together, first.

Dave
0
 
Rory ArchibaldCommented:
What kind of datasources are you trying to get to? You can certainly use different databases on the same SQL server (assuming the same login works) and you can use sheets in different workbooks.
0
 
jdr0606Author Commented:
I found a work around that seems to be OK.

The two datasources are using linked servers.

I had two problems
1. I was unable to edit in MS SQL Query.  I had updated the SQL to use literal joins, but it appears that Excel query only recognizes joins that are created using the where syntax.
i.e.

select item a.code, b.code from table1 a,table2 b where acode=b.code
Can then be edited in Excel query

if I used
select item a.code, b.code from table1 a
join table2 b on b.code=a.code
It will NOT edit in Excel query

2. The second part is selecting datasources (for the linked servers)  and the only way I coiuld make that work was to actually add the datasources in the Data properties before calling the Excel query.  Whichever is defined as the primnary ODBC source is the only datasource selectable with Excel Query.

i.e. select a.code, b.code
from db1.dbo.table1 a, linkedserver.db2.dbo.table2 b

Since I used the ODBC connection that contained table1 I was able to add additional tables for that datasource when in Excel Query, however even though I can see the linkedserver datasource table "table2" I'm not able to select any tables from that linked server datasource.  If I need an additional table from the linked server datasource I have to first identify it in the Excel data properties query.

Hope that makes some sense
0
 
dlmilleCommented:
I'm not sure (and I'm sure rorya will correct me), but I believe you could have more luck setting up these connections and your query in an Access database.  THEN, create a connection to the Access database to run your query.  I'm not sure Excel will support building one query against multiple data connections, simultaneously.

Dave
0
 
jdr0606Author Commented:
If you read my last post closer you will see that I now have multiple data connections working correctly within the same SQL query and editor within Excel.

Regards
0
 
dlmilleCommented:
I see I did read your post too quickly, and I shared my initial premise, which appears to be incorrect, based on what you've discovered.

That's quite a work-around.  How often do you need to build connections like this?  Have you finalized on your own solution?  How can we help further?

Dave
0
 
jdr0606Author Commented:
Because of production linked servers, these connections are needed constantly.

I think I all my current issues are resolved.

Thanks for everyones input.
0
 
dlmilleCommented:
You've shown more persistance that I would have.  I thought for sure building a connection via Access (or creating the view in your back-end database) would have to be the solution.  Clever approach to do it within Excel and I'm going to try that out for myself and my learning.

Sometimes it works the other way - thanks for the learning!

Dave
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now