Confused by Excel ODC connection files

0 votes
asked Jul 13, 2015 by timbram

So I was making a new connection to an MS SQL server using the wizard and realized I don't know REALLY why it has me save these ODC connection files.

So, what are they and why does Excel create them?

Further, what happens to these files once when I change the connection from just selecting a table, to a custom SQL statement I have created?

Does anything change in these ODC files? I know sometimes it complains that if I make a change it will no longer match what is stored in the file.

edit:

I decided to look into one of my ODC files and it is HTML combined with some XML tags and then even a little JavaScript. So that is interesting. Still not sure how Excel uses these files instead of just storing that data itself. I guess to provide connections to multiple workbooks.

1 Answer

+1 vote
answered Nov 8 by dick-kusleika

I think the idea is that you can share these files. You could put an odc on a network share or on SharePoint, have multiple workbooks use the odc file, and if any updates were necessary (like the database moved), then you only have to change it in on place.

Also, if you double click the odc file, it will create a new workbook with that connection and a table. I guess that could be a lean way of sending a workbook that consists of only an external data table to someone with access to the same connection. I haven't used that myself, but, you know, theoretically...

Why did they choose this as the default? I don't know. I wouldn't have. I would have chosen to have the data stored in Excel, but still have the ability to Export an odc for those that want to go that route. I'll bet the vast majority of external data tables have an odc sitting in My Documents and the user isn't even aware of it.

But pre-tablet MS Office seemed to base their design decisions on what their biggest clients wanted. And since there really isn't a cost to the average user (other than warnings they may not understand), why not.

Welcome to Q&A, where you can ask questions and receive answers from other members of the community.
...