Managing External Database Connections in Excel 12
Now that we have covered OLAP formulas, I would like to explain a set of work we did in the area of “connection management” in Excel 12 workbooks. Specifically, I want to show you three new things that you can do in a workbook that is connected to external data (whether that is an Access database, and Oracle database, and Analysis Services cube, etc.). In Excel 12, you can
- see a list of the connections that are used in your workbook;
- see where those connections are used;
- change the properties of the connection.
Since the term “connection” has different meanings for different people (especially developers), let me start by explaining what “connection” means in relation to Excel. Here is our definition: “The information needed to uniquely identify an external data source, and to connect to that external data source, so that some or all of the external data can be imported into Excel 12.” Put another way, a connection is nothing more than some information – what type of connection this is (i.e. ODBC), the server name (i.e. MyRelationalServer), the table name (i.e. MyTable), optional parameters (i.e. Persist Security Info = True), etc. – that is used by Excel to fetch data for use in Excel (a company’s sales data or inventory data or any other data that a user might wish to bring into Excel).
In the context of Excel, connection information can be stored inside an Excel workbook in what we call a “workbook connection”, or it can be stored in a file of its own, which we refer to as a “connection file.” Connection files created in Microsoft Office are given a file extension of “.odc” which stands for “Office Data Connection”. Excel can open .odc files and establish connections based on their contents. When a connection file is used by Excel 12, all of the connection information is copied from the file into the Excel 12 workbook, resulting in a workbook connection. Excel then has its own copy of the connection information which it can use to fetch data from the external data source. When you use Excel’s tools to connect to an external data source, Excel stores the connection information in a workbook connection and, in general, also creates an .odc file which it sticks in your “my data sources” folder. The theory here is that next time you want to connect to the same data source, you can just open that file and avoid the hassle of re-typing all the connection information into dialogs. Note that nothing in this paragraph is new to Excel 12 – it all exists in current versions of Excel.
That’s a lot of abstract nouns. To summarize, we have the following items:
- External Data Source – a separate database or data file
Connection Information – info needed to connect to external data
Connection File – standalone file containing Connection Information
Workbook Connection – copy of Connection Information within an Excel workbook
Now for the interesting part – let’s look at workbook connections in Excel 12 (this is the new part). In Excel 12, we have added a new dialog that shows all the workbook connections that can be found within the current workbook. You can get to this dialog from the Data tab by clicking on the word “Connections”. Here’s a shot of the Data tab.
(Click to enlarge)
The dialog that comes up will look empty when there are no connections in the current workbook, like this.
(Click to enlarge)
When there are several connections in the workbook, it will look like this.
(Click to enlarge)
Note that each connection in the workbook has a name that must be unique (within the workbook). You can see where a connection is used by selecting it and then clicking on the link in the bottom half of the dialog, like this:
(Click to enlarge)
You can also get to a lot more specific information about a connection, by selecting it in the top part of the dialog and then clicking on the button labeled “Properties…”. This will bring up the Connection Properties dialog which looks like this:
(Click to enlarge)
This dialog has two tabs. The first tab, labeled “Usage” is shown above and has settings for how the connection is used within this workbook. The second tab, labeled “Definition” is shown below and has settings that define the connection information itself.
(Click to enlarge)
The button labeled “Export Connection File…” can be used to save the connection information into an external connection file with a file extension of “.odc”.
The ability to view all of the connections in a workbook in a central location and then to manage those connections with a standardized dialog is new in Excel 12. In previous versions of Excel, connection information was stored with the object that used the connection. For example, the connection used by a query table was stored with that query table and the connection used by a pivot table was stored with that pivot table. In order to browse all the connections in a workbook, you had to navigate, via code, to each of the objects in the workbook that might be bound to external data and then look at the object model for each of those objects to see the connection information.
By providing a centralized place for browsing and managing connectivity to external data, it becomes much easier to make changes such as these:
- Switch between a test database and a production database
- Update a connection to point at a different server
- Update a connection to use a different query string
- Share a connection among multiple pivot tables
With the introduction of CUBE functions in Excel 12, the need to maintain connections became greater, as the connection to a particular OLAP cube must be specified in each of the CUBE functions. Additionally, we had received a lot of feedback from customers over the years that changing connections on QueryTables and PivotTables was too difficult. Hopefully, the work described above does a good job of addressing customer needs.
