?
Solved

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

Posted on 2010-09-22
5
Medium Priority
?
718 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 2000 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

While working, an annoying popup showing below will come and we cannot cancel or close it form the screen. The error message will come again and again.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

777 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