Results 1 to 2 of 2
  1. #1
    Join Date
    May 2004
    Thanked 0 Times in 0 Posts

    Understanding Data Connection Wizard (2003 SP2)


    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 Cocuments 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,


  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Weert, Limburg, Netherlands
    Thanked 0 Times in 0 Posts

    Re: Understanding Data Connection Wizard (2003 SP2)

    Your assumption is correct, the odc file is just created for convenience, so you can quickly repro the connection in another workbook.
    External data properties are saved with the workbook.
    I'm not too good in security, but I would expect if you are using Win authentication, that the refresh will only work for users with the same permissions as you.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts