Solved

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

Posted on 2010-09-22
5
702 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
  • 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

708 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now