Excel query table still works after deleting File DSN, how is that possible?

views:

997

answers:

+1 Q:

Excel query table still works after deleting File DSN, how is that possible?

I have an xls with a pivot table using a proc to populate it.

I deleted the data connection in 'admin tools - odbc - file DSN' and it still works, how is that possible?

Does that mean that the recipent doesn't have to have the file DSN present in order to see data in the pivot table?

when you create pivot table once, afterward that .xls file will work independently in your Hard Drive. So, it doesn't require any DSN furthermore.

Nirmal 2009-04-07 12:41:40

but how is it possible that if I either send the xls to someone else (who doesn't have the DSN) or delete my own DSN it still works. It doesn't make sense. Either it is using the DSN or not, if not, how is it storing the connection, if it is, how is it working when the connection is deleted?

adolf garlic 2009-04-07 13:08:45

The Pivot Table keeps a Pivot Table data cache behind it. You can manipulate this data any way your like, but it won't refresh, obviously, until you reconnect.

Stan Scott

Stan Scott 2009-04-07 14:05:14

I sent it to someone who doesn't have the file DSN and they successfully refreshed the pivot table with no errors. What is going on? How on earth is that working?

adolf garlic 2009-04-07 14:57:29

Adarsha has the answer. The first time the query is run, the DSN is read and the information is used to make a connection and read the data. The second time the query is run, though, the query ALREADY has the information it needs to make the connection -- that's part of the information it keeps.

Stan Scott 2009-04-09 15:53:48

+1 A:

That is possible because when you use the file DSN, excel reads the content of the file, which essentially has all the entries to construct the connection string to outside data provider. Then Excel will construct the connection string and use that to populate the QueryTable. So once you successfully refresh the QueryTable for the first time, you no longer need that file DSN. So it will work event after you delete it or in some else’s machine without that DSN.

On a side note when you use file DSN in a QueryTable and later if you update the FileDSN (say to a new database or any thing like that) your QueryTable refresh will not reflect that change, you have to reconstruct the QueryTable.

Adarsha 2009-04-07 15:43:35

This is good to know as we are looking at parameterising the connection string for build purposes. Is the only solution to this to make the connection DSN-less? i.e. Have the connection made dynamically in VBA in say an Open Workbook event?

adolf garlic 2009-04-08 06:28:58

You can, and its just a matter of string manipulation. But having a dynamic Connection string has its drawbacks too. if in future your data provider properties (server, port or driver etc) changes then you need to update our code too.

Adarsha 2009-04-08 13:27:00

We have covered this issue: we use nAnt scripts for all our builds, one of which will use the DSOleFile library to update the Custom File Properties. VBA code will pick up the servername etc from the custom file properties. So if we want 2 create a build for dev/test, we can just alter 1 cfg file

adolf garlic 2009-04-09 08:50:09

ansaurus

tags:

views:

answers:

Excel query table still works after deleting File DSN, how is that possible?

related questions