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.