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

Posted on 2014-09-15
Medium Priority
Last Modified: 2014-09-17

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.

Question by:bthouin
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
LVL 11

Expert Comment

ID: 40324783
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.

Author Comment

ID: 40326754
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...
LVL 11

Accepted Solution

jkpieterse earned 2000 total points
ID: 40327400
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

Author Comment

ID: 40329042
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.

LVL 11

Expert Comment

ID: 40329604
:-) You're welcome!

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question