Solved

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

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
When you try to extract and to view the contents of a Microsoft Update Standalone Package (MSU) for Windows Vista, you cannot extract the files from the MSU. Here we are going to explain how to extract those hotfix details without using any third pa…
The viewer will learn how to successfully download and install the SARDU utility on Windows 7, without downloading adware.
The Task Scheduler is a powerful tool that is built into Windows. It allows you to schedule tasks (actions) on a recurring basis, such as hourly, daily, weekly, monthly, at log on, at startup, on idle, etc. This video Micro Tutorial is a brief intro…

821 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