Hello,
I’m experimenting with the Data Connection Wizard in Excel and trying to understand how it works.
What I’m doing is this:
Select the Data menu>Import External Data>Import Data.
Then select +New SQL Server Connection.odc, click the Open button.
Enter the Server name and accepting the default to Use Windows Authentication.
Then I am selecting my database and a view I have created and then click Finish.
After the data is retrieved, I see that I can right click in the data and select Edit Query. There I can see what appears to be a connection string, a command type, and command text.
I can also right click in the data and select Data Range Properties and see various options.
I see that the Data Connection Wizard created a .odc file in C:Documents and SettingsmyusernameMy DocumentsMy Data Sources.
I ran a little experiment by saving the Excel file with the data that was just imported. I deleted the .odc file from My Data Sources. Then I re-opened the Excel file and was able to refresh the data. I guess I was expecting it to not be able to refresh after the .odc file was deleted. Also, if I saved the Excel file to a shared location on our network, other users were able to open and refresh the data.
So it appears that once the data range is created in Excel, the .odc file is no longer necessary for access to the data. Is this correct? Is everything needed to access the data stored somewhere inside the Excel file? If so, where?
Since I selected to use Windows Authentication in the Data Connection Wizard, is it my permissions that are used each time to access the data or is it the permissions of the user who is logged into the network?
Is the .odc file only for use in creating new data ranges to the same data using the same connection properties?
I’ve searched around looking for explanations but haven’t found too much.
Thanks in advance,
Kevin
