2

I have a workbook (Excel 2016) with a few dozen "Connections" defined. Each connection has different "Command Text" but the exact same Connection String. Because this connection string will be changing often (monthly password changes or the occasional database move), I would like to share a single connection-string amongst many different Commands.

I saw a Microsoft article that said I could "Export Connection" as an .odc file, which I did--but I'm not used to Excel--where I come from "Connection" is one thing, "Command" is another--and this .odc file contains both. So when I set all of my Connections to use the single .odc "Connection" file, all of my "Commands" were identical, so that was a disaster.

I have to trust that there really is a way to share a single connection among many different commands that all come from the exact same database. (edit)--I really don't want a Macro or more code to plop in a connection string--I'm hoping for something very similar to the "Connection File" concept--but one that really is a "Connection" only. Thanks

Improve this question
3
  • Could you give an example of your connection-related code so your question is more clear? (There are a number of ways connections can be made.) Commented Apr 4, 2018 at 1:01
  • ashleedawg, I don't have any code per-se, it's just a Connections in the "Queries & Connections" area. Each one has a connection string at the top and a bit of SQL in Command. They each refer to a named-range of cells. I didn't build this, it's inherited and I'm trying to simplify the upkeep. Commented Apr 4, 2018 at 1:20
  • fair enough... I'll put some information in an answer in a minute Commented Apr 4, 2018 at 1:52

1 Answer 1

Reset to default
1

The short answer is Yes - you can re-use connections from Excel's Queries & Connections window, and are encouraged to do so for the same reasons you mentioned, and more.

How to do it in your individual case depends on a number of factors that aren't included in your question such as where the data is coming from and what it's being used for.

Hopefully some of these resources will help shed some light...

More Information:

Improve this answer
Sign up to request clarification or add additional context in comments.

11 Comments

I really appreciate the help, but honestly I looked at every one of those links and they all inextricably tie a view or table to a connection. What I need is just a connection. I'm frustrated with Microsoft because it just shouldn't be this difficult. This is SQL-server btw. The connection is a sql-auth user, not Trusted Connection.
You want to be able to use an existing connection for other queries in Excel, correct? Or, are you talking about using the MySQL ODBC Data Source connection in other applications and other workbooks?
It's SQL-Server, not Mysql, But all of this is in the same workbook. There's dozens of ranges/tables in one giant sheet. Each of them has a Connection defined. Due to increasing security concerns, the pwd needs to change every month. I was light in my original estimate--there's over 200 connections. Changing them manually is beyond the pale. The end-state I'd like is to go to the Excel "Existing Connections" menu item, and choose one connection, and change the pwd there. Then I would like all 200 queries--each a different view/table/sql-statement, simply refer to that single connection
Sorry I meant SQL Server (but it's the same anyway)
Thank you, I think this is getting close...So the connection type is Oledb query. I'm thinking it should be ODBC. I will look into changing them--if I have to change them all once that's no big deal, as long as all 200 or so "Connections/Commands" point to the single DSN. And no--this is never emailed...I really wish Excel could encrypt it. I'm used to doing web apps or Winforms apps and I'd always have a single connection string in the .config file, always encrypted. So that's where my head was at when this was dropped on my desk. I'll know more in the morning, thanks!!
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.