Moving the database (i.e., the data
source )
Obviously,
moving the location of the data source is not something that
should happen on a regular basis. However, there are times
when it becomes necessary. One of those times will be when
the files associated with this tutorial are downloaded by
someone. Clearly, the location of the data source on their
machine will be different than on the machine where the files
were created. Another
instance will be if they implement cascading queries for their
organization. Typically, their testing will be done on
their own workstation but the eventual location will be a network
server. Of course, once the data source is on a network
server, the need to change the location will be minimal.
There are three files in the downloadable zip
file. The two analysis files are cascading query.xls and cascading
query source.xls. The third file, named updatesource.xls,
contains code to fix source links. Put all
the files in some folder on your machine. As the reader
must have guessed, cascading query source.xls is
the database file. There is nothing but data in it.
The updatesource.xls file simplifies the task of updating the
datasource links. See the section
Restoring the link to the data
source.
Open the file cascading query.xls
file. The first thing that should happen is an alert about
whether Excel should refresh queries. Select 'Disable
automatic refresh'. After all, we know the
refresh will fail until we fix the link to the data source.
The cascading query.xls looks like:
 |
Click on the thumbnail for the actual image
It contains three lists. Column B
contains a list of all departments. Columns F:G a list of
boxes in the department and columns K:L a list of all files in a
particular box.
As it turns out, changing the data source
through the user interface is cumbersome and error prone.
By contrast, changing it programmatically is very simple.
So, rather than describe the user interface method, the download
zip file contains a file with VBA code to update the link.
Open updatesource.xls. If you get a warning
about "This file contains potentially malicious macros" you must
select the Enable Macros
button.
Switch back to the cascading
query.xls workbook. The active sheet should be the
one containing the three queries. Use ALT+F8 to access the
Macros dialog.
Select the updatesource.xls!fixConnections
entry and click the Run
button. You will be asked for the new data source.
Locate and click on the cascading query datasource.xls
file. Once the macro completes save the cascading
query.xls file, preferably under a new name. Close the
file and reopen it. You must save,
close, and reopen the file. Otherwise, the queries will
not work.
This time around, when the Query Refresh
dialog box pops up click Enable
Automatic Refresh.
|
