Office Home And Business 2010 Switching Data Sourc

Today’s Author: Howie Dickerman, a program manager on the Excel team. Howie is going to discuss different ways to change data sources in Excel 2007.
Prerequisite: This post assumes that you have read the blog post entitled “Managing External Database Connections in Excel 12” which can be found here.
Folks who connect to external data from Excel will sometimes encounter the following user scenarios:
Switch between test and production data sources
I want to test my spreadsheet while connecting to a test server, but then want to switch from the test server to a production server. Or I’ve got a spreadsheet that’s getting its data from a production server and I want to switch to a test server while I make some changes to the spreadsheet.
Switch from old data source to newer data source
I’ve got a spreadsheet that connects to the old database, but now I want to switch it to connect to a newer version of the database.
Switch between data sources which contain different segments of the data
I’ve got a spreadsheet which pulls in data from the North American data source, and I want to modify it so that it pulls in data from the European data source. Or I have a spreadsheet that pulls in data from the database for Product Line #1 and I want to change it to pull in data from the database for Product Line #2.
There are three ways to accomplish any of these tasks in Excel 2007, and it’s worth considering the different approaches.
Option #1 – Change the connection string within the workbook to use a different data source
This is done by going to the Connection Properties dialog and changing the connection string so that it points at a different data source.
In some ways, this is the simplest option because the change only affects this workbook,Office 2007 Standard Key, but it is also a very technical approach because it requires the person making the change to know how to change the connection string. When a connection string is very long, it can seem daunting to try to find the portion of the connection string that needs to be changed. This approach gets very painful when you are changing the data source for a set of workbooks, because it requires you to make the same change many times. Note that this approach also disconnects the workbook from any existing connection files,Office Home And Business 2010, effectively orphaning the workbooks from any chance at centralized connection management.
Option #2 – Change the connection string within the connection file used by a workbook
This is done by using Notepad (or any other text editor) to edit the Connection file whose location is specified on the Definition tab of the Connection Properties dialog. This requires the same technical knowledge of the connection string that is required by option #1. The connection information is stored as XML within the connection file. Here’s an example of what a connection file opened in Notepad will look like with the name of the data server highlighted in this example:
Note that the connection properties dialog has a checkbox entitled “Always use connection file” on the definition tab (see picture in option #1 above) for each connection within each workbook. When this checkbox is checked, it means that the workbook always uses the information in the external connection file when obtaining data over the connection. If there are dozens of workbooks that all use a particular connection file, and this checkbox is checked in each of those workbooks,microsoft office professional plus 2010 key, then changing the connection file will automatically cause all of the workbooks to start using the new data source.
If the checkbox is not checked, then changes to the connection file will have no impact on the workbook until the workbook is modified to use the updated connection file. By pressing the Browse button beside the Connection File property, and by navigating to the newly updated connection file,Office 2007 Professional Plus, the contents of that connection file will be brought into the workbook and the newer data source will be used.
When there are multiple workbooks that all connect to the same data source,Key Office 2007, and when you want the data source to be switched for all of those workbooks at the same time, this approach has the advantage of letting you make the change in one place and having all the workbooks updated automatically.
Option #3 – Create a new distinct Connection File and change the workbook to use the new connection file
This is done by creating a new connection file using Excel’s user interface. Then any workbook can be modified to use the new connection file instead of the old one.
The easiest way to do this is to start in a new (empty) workbook and simply create a connection to the new data source using whichever wizard is most appropriate. This might be an OLEDB connection created via the data connection wizard, or it might be an ODBC connection created via MS Query. Other options include web queries and connections to OLAP data sources.
Once the connection has been created, you can simply look in Connection Properties to see where the new connection file was created. Usually this will be in the folder named “My Data Sources”, but the wizards allow you to create the file virtually anywhere.
You can place the connection file anywhere you want, and then open the workbook that you want to change and browse to the new connection file to switch the connection from the old data source to the new data source as defined by the new connection file.
One advantage of this approach is that you don’t need to understand anything about the internal workings of connection files, nor how to parse connection strings. You’re letting Excel and it’s wizards and the data providers build the connection information for you. All you need to do is modify your workbook(s) to use the newly created connection file.
Another advantage of this approach is that it’s trivial to change the workbook later to point back at the original data source. Simply modify the connection property to use the original connection file.
Summary
When you’re trying to figure out the best approach for your particular situation, here are some of the questions that you should ask yourself:
Am I comfortable modifying a connection string to point to the new server?
Do I want to switch the data source for a single workbook? Or for a large set of workbooks?
Is this switch a permanent one or a temporary change that I will want to reverse later?
Which of these approaches will be easiest to maintain over time by the folks responsible for that?
I’m happy to answer any additional questions that this may raise. By surfacing connection information in Excel 2007 as a set of top level connection objects, we’re hopeful that we’ve made it far easier for our customers to find their data connections and to modify those connections as needed to point at various data sources.

<div