Completely confused by data connections in Excel to a SQL Server DB


I have developed an Excel 2013 template (which started its life originally as an Excel 2007 template based on an Access DB) which uses tables in a SQL Server DB, and using quite a bit of VBA code. In some cases, I need to use the tables data in data validation definitions of cells, so I need an area in a sheet which is the list source for the data validation.  I have a "Support" sheet which hosts the fields validation data. The thing is, it works perfectly in many cases, but not at all anymore in one case, and looking at the definitions, I just don't understand how it's supposed to work at all.

The working data connections store their data in some columns of my "Support" sheet. Already there, I'm lost: when I look at the connections definitions in Excel over the menus, I do not find anywhere any mentions of cell ranges where the data of the connection is supposed to be going. So how does a connection "know" where to put the data it retrieves from SQL Server ? The working connections seem to put their data always at the same place in the "Support" sheet, but why and how ?

I also find some strange definitions in the name manager (I'm a fan of names, and use them wherever I can, so each data validation source is just a name) , looking for example like this: "Table_PreviousAccessDB.accdb_3", where "PreviousAccessDB.accdb" is the name of the Access DB which I used previously for the data, now I moved to SQL Server, why are these "old" names still there ? And on top of that, these names cannot be deleted.

Also, the data connection definitions tell me, when I click on the link "Click here to see where the data connections are used", that they are used nowhere !

The strange thing is that, in all cases where I directly get data from the SQL Server DB (which I use mostly to validate inputs) using VBA code and one specific SQL Statement, that works absolutely flawlessly. So accessing SQL Server is not at all a problem. It's just the data connections that make problems.

I would gladly control all these data connections through VBA if possible, because there are too many things that escape my control currently, especially in the case of the data connection which doesn't work anymore, although its definition seems to be exactly like the others, but the corresponding data is nowhere to be found.

I'd be very thankful if somebody could shade some light on this data connection jungle.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

You can safele delete all connections that complain about not being used in the workbook, since they are orphaned connections that are, well, not in use.
All connections that are tied to a range you should leave untouched (except when you know you do not need them of course). If you delete a connection, its associated range name should disappear as well. The location of a connection is controlled by a range name, this is how Excel knows where to put the data.
bthouinAuthor Commented:
Thanks for your post, but you'll have to be more specific. What do you mean by "the location of a connection is controlled by a range name" ? Never was I asked, when I defined a new connection, which "location" or range name was going to be attached to it, so that's why I was so puzzled about the way it works. I must have been missing a step in the process. I just used the menu points data, connections, and defined a new connection. I was never asked for a range name, that's surely why it never worked. And, as usual, the MS Help is absolutely no help at all. As ever, it explains how to use the result, but doesn't explain at all why and exactl how are all the steps in the process. The help is done by people who know the answers, so they never refer to the question and to the orginal problem, which is infuriating.

In the meantime, seeing that my connections did not work properly, I replaced them all by a generic routine that accesses the proper table and field(s) in the database, stores the data in a given set of cells in a separate sheet, and then defines these cells as a range, which is then used by the data validation definitions as a dropdown for some corresponding cells in the main sheet. That might be totally reinventing the wheel, but at least I'm fully in control. I still don't know how data connections are supposed to work/help, but frankly, I don't care, 'cause I've got a brilliantly working solution...
In the last step of the external data wizard, Excel prompts for the location.
Last step of MSQuery wizardIt then creates a table in that location, which spans the entire area of returned data. The name of the table is automatically generated by Excel. The name of the connection can be changed by clicking the "Properties" button in the last step of the Query wizard:
Query propertiesAfter changing the connection name to "FooBar", this is what the connections list shows:
List of ConnectionsFinally, you can change the name Excel uses by changing the name of the table the data was dumped into:
Change the name of the table

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bthouinAuthor Commented:
Ah, there is the magic word: "External Data Wizard" ! This is what I NEVER used !! Hence my totally incomplete definitions. And I should have known better, because that's what I use day in day out with Access when linking to tables in backend tables ! Instaed, I idiot, I used directly "Data connections", which leads to nowhere.

You see, this is like the MS help. Do you think it would mention "external data" in connection with the data connection help ? NO, that would be way too easy. Instead it just tell you what data connection does, but not WHY and in what context it's normally being used. Extremely frustrating.

OK, so thanks a lot, if I ever decide to do it again through the menus, I will use External Data and NOT data connections. But as mentioned, for the moment, I'm completely covered with my won VBA solution.

You get the points, and thanks for your time and your wonderful documentation.

:-) You're welcome!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.