Solved

How do you pull in data from multiple databases into Excel through ODBC?

Posted on 2010-09-22
5
716 Views
Last Modified: 2012-05-10
For Excel 2007, I have created a SQL Native Client ODBC connection.  I need to use that ODBC to pull in data from more than one database into Excel.  I need to therefore pull in data from multiple tables and, if possible, I'd like to see these tables graphically and link fields between these tables just like in Crystal Reports and other report writers.

I have been experimenting with the Excel 2007 "Data" tab and have been clicking the "From Other Soures" button and choosing "From SQL Server".  This creates a good .odc file and I can therefore create multiple connections.

But, how do I "join" these files?  Again, I need to pull in data from more than one database.

And, even so, is the "From Other Sources" button the best one to use in order to pull in data from multiple databases?  If not, what is the best way to do this?

Attached is a printscreen of the "Workbook Connections" window, but I cannot see how to pull in data from both--only one at a time.
Doc2.docx
0
Comment
Question by:apitech
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 4

Expert Comment

by:r0bertdenir0
ID: 33742586
Hi
If I understand your question correctly you are asking how you can do in Excel what you can do in Access.
If you want to do joins on tables from different databases, then Excel is not good at that.

It can be done by linking or importing the data into Excel & then using data functions like VLookup. But it's nowhere near the performance & capabilities of Access.

You can do joins on multiple tables with a single database with native SQL of the host database. But you don't have a query designer that works across database.
The data functions in Excel are for bringing in data to apply business logic.
That's Excel's place so it's never likely to get Access's capabilities.

If you really must do it in Excel, you can use VBA & ADO to create the data connections as you like & then push that into a worksheet.

0
 
LVL 1

Author Comment

by:apitech
ID: 33743058
Thanks, for the response!

If it is not possible to conduct linking and joins in Excel like this, is it possible to at least "pour" data from multiple databases into a spreadsheet without graphically seeing the tables?  Or, is that something that also can only be done in Access rather than Excel?  If it is possible to pull the data onto a spreadsheet, how would I do this from multiple databases with a single ODBC connection?
0
 
LVL 20

Expert Comment

by:clarkscott
ID: 33743491
You can create an Access mdb.  Link all the tables you need (from various back-ends).  Then from Excel, ODBC to the Access mdb.  You will have access to all your data.  You can even create Access queries (using the GUI) and open these queries from within Excel.

Scott C
0
 
LVL 1

Author Comment

by:apitech
ID: 33745004
Thanks!  My questions have to do with Excel.  I just want to know if it is possible through one, and only one, ODBC connection to pour data from mulitple databases into a spreadsheet.  If it's not possible to do so through just one ODBC connection--or any for that matter--I want to know.

Or, are you saying that the only way to do this in Excel is through Access?  If so, that's fine.  I'm just trying to make sure that I understand.

Thanks, again!
0
 
LVL 20

Accepted Solution

by:
clarkscott earned 500 total points
ID: 33772600
Each record source (in different backends) must be ODBC'd individually.  You cannot use the same connection for multiple databases.  By linking all the sources to 1 Access mdb, you can then link to this Access mdb (one connection) and retrieve, query, etc. all your tables.

Scott c
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to successfully download and install the SARDU utility on Windows 7, without downloading adware.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

687 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question