Solved

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

Posted on 2014-09-15
5
108 Views
Last Modified: 2014-09-17
Hi

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.

Thanks
Bernard
0
Comment
Question by:bthouin
  • 3
  • 2
5 Comments
 
LVL 11

Expert Comment

by:jkpieterse
Comment Utility
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.
0
 
LVL 1

Author Comment

by:bthouin
Comment Utility
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...
0
 
LVL 11

Accepted Solution

by:
jkpieterse earned 500 total points
Comment Utility
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
0
 
LVL 1

Author Comment

by:bthouin
Comment Utility
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.

Bernard
0
 
LVL 11

Expert Comment

by:jkpieterse
Comment Utility
:-) You're welcome!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

772 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now