Solved

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

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

When you start your Windows 10 PC and got an "Operating system not found" error or just saw  "Auto repair for startup" or a blinking cursor with black screen. A loop for Auto repair will start but fix nothing.  You will be panic as there are no back…
When you try to share a printer , you may receive one of the following error messages. Error message when you use the Add Printer Wizard to share a printer: Windows could not share your printer. Operation could not be completed (Error 0x000006…
This Micro Tutorial will give you a introduction in two parts how to utilize Windows Live Movie Maker to its maximum editing capability. This will be demonstrated using Windows Live Movie Maker on Windows 7 operating system.
The viewer will learn how to successfully download and install the SARDU utility on Windows 7, without downloading adware.

910 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

21 Experts available now in Live!

Get 1:1 Help Now