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?

Best Solution

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.

Related Question