CODEWITHSUNDEEP
exceloledbconnectionoledbcommandvba
jimo3
jimo3

Reputation: 428

Share a database connection for multiple queries in Excel?

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

Upvotes: 2

Views: 5755

Answers (1)

ashleedawg
ashleedawg

Reputation: 21657

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:

Upvotes: 1

Related Questions