Set Location Dialog Box

The Set Location dialog box, shown in Figure 13-8, is used to change the data source of an existing table. You might need to do this when the reporting requirements change, or when you find that, after having used one data source for coding and testing, you have to change to the production data source before releasing the program.

Figure 13-8. The Set Location dialog box.

The Set Location dialog box lets you replace an existing data source with a new data source. You can switch the database server so that all the tables from one server are switched to a different server. You can also change the data source for just one table and it won’t affect the other tables in that report. This includes changing the database server for that table or switching out the table for a totally different table.

Open the Set Location dialog box by right-clicking on the report and selecting Database | Set Location. It has a combobox on the left hand side labeled “Current Data Source”. This lists the current connections and any tables that are being used with that connection. The window on the right is labeled “Replace With”. It lists the same data sources that are displayed in the Database Expert dialog box.

To replace the entire data source for all the tables, select that database server from the Current Data Source window on the left.

To replace a single table drill down to select that table from the combo box. The window below the combo box is refreshed to display the information about the table.

The Replace With window on the right is where you select the data source that will replace what was selected in the combo box. Scroll through this window to find the new data source. If the Current Data Source combobox lists a database server, choose a database server within the Replace With window. If the Current Data Source is a table, you have to have a table selected within the Replace With window.

When you open the Set Location dialog box and click on one of the database servers, a list of all the available tables in the database is created. If at a later point you go into the database server to make changes to the tables (e.g. adding or deleting tables), the Set Location dialog box won’t recognize those changes. It will still show the original list of tables. The Set Location dialog box only queries the database server the first time it is opened. To force the table names to be refreshed you have to close and reopen Visual Studio.

When both the current data source and the new data source are selected, click the Replace button. The existing data source gets replaced with the new data source. The information on the left is updated to reflect the properties of the new data source.

If you find that the Replace button isn’t enabled, you haven’t selected data sources that are compatible with each other. For example, if you have a table selected in the combo box, then you have to have a table selected in the Replace With window. If the combo box has a data source selected, then the Replace With window can’t have a table selected.