Completely confused by data connections in Excel to a SQL Server DB
Posted on 2014-09-15
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.