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
  • 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

609 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