Add new table from external source using existing connection

Question

I have an Excel 2013 workbook where I have created multiple tabs with each tab showing the results from a different view from an external database.

I did this by clicking on DATA->OTHER DATA SOURCES where I then setup a connection and selected all of the different views I wanted to see in the workbook. Once I selected the views the results were automatically populated in a new tab in the worksheet.

Now, I want to add another view as a tab in the workbook using the same connection, but cannot find out how to do this and the only way I can do it at the moment is to add the table using a different connection.

How can I do this?

Answer 1

My understanding is that you can't use a connection twice. The connection is tied to the specific SQL statement or table/view name. If you create a connection on one page, and you create a new query on another page using Data - Get External Data - Existing Connections, Excel copies the existing connection rather than running two external data queries through it.

Excel's Connection object has a file associated with it, usually in My Documents\My Data Sources. Even if you select a specific table, that file only contains the connection to the database, not the table/view. So Connections don't appear to be table specific, but they contain other objects and tie them to a specific table.

Every Connection object has an ODBCConnection property and an OLEDBConnection property that point to their respective objects. These objects have a CommandText property for the table name or SQL statement. There's only one of these per Connection, which is why I'm pretty sure that you can't use one Connection for two external data queries.