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

Community
  • 1
  • 1
jimo3
  • 408
  • 1
  • 5
  • 17
  • 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.) – ashleedawg Apr 04 '18 at 01: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. – jimo3 Apr 04 '18 at 01:20
  • fair enough... I'll put some information in an answer in a minute – ashleedawg Apr 04 '18 at 01:52

1 Answers1

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:

ashleedawg
  • 20,365
  • 9
  • 72
  • 105
  • 1
    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. – jimo3 Apr 04 '18 at 03:09
  • 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? – ashleedawg Apr 04 '18 at 03:18
  • 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 – jimo3 Apr 04 '18 at 03:35
  • Sorry I meant SQL Server (but it's the same anyway) – ashleedawg Apr 04 '18 at 03:57
  • It's tough to picture how these connections are setup without seeing... but it sounds like you're not using an ODBC DSN (Data Source) to the server, but you should be. That would be the place where you enter the server information and password **once** and everything (Excel *and* other applications) all connect through there. You would set that up by pressing the Windows key and typing `ODBC` and adding a new system connection (or it might even be setup there already). – ashleedawg Apr 04 '18 at 04:00
  • ...once the ODBC **System** DSN is setup (with password, and tests properly with the "Test Connection" button), you can add the connection to an Excel document by clicking `Existing Connections` on the `Data` tab, and the connection should be listed as a data source. Then you'd change all of you queries etc to the new data source. If there are tons, there may be a quick way to do it. Once it's changed, you'll only have to set the password in one place monthly. – ashleedawg Apr 04 '18 at 04:04
  • Incidentally, specifying the password multiple times in multiple workbooks is indeed *very*insecure since the worksheet can easily be emailed somewhere (has it been?!), and it has the connection information including password attached to it. That's exactly what monthly-password-changes are implemented to prevent. – ashleedawg Apr 04 '18 at 04:07
  • 1
    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!! – jimo3 Apr 04 '18 at 04:12
  • Alternatively you can setup and ODBC **File DSN** (instead of System) if you'd prefer. As for Excel encrypting, ***Nope!*** MS Office is **not** secure. Don't even trust the password protection, I can get into any password-protected Office document in under 3 minutes ([and now you can too!](https://stackoverflow.com/a/48722078/8112776)) – ashleedawg Apr 04 '18 at 04:13
  • Here are various sets of instructions on [setting up an ODBC File DSN to SQL Server](https://www.google.ca/search?q=create+odbc+file+dsn+sql+server). You *may* need to download a driver but it's often installed already. 80% of the time the process is a breeze. (It took me weeks to get a connection to a personal web server/SQL Server to work properly because of 32bt/64bit issues.) Here's more about [changing your connections](https://support.office.com/en-us/article/create-edit-and-manage-connections-to-external-data-89d44137-f18d-49cf-953d-d22a2eea2d46) (for after it's setup)... **Good Luck!** – ashleedawg Apr 04 '18 at 04:21
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/168169/discussion-between-jimo3-and-ashleedawg). – jimo3 Apr 04 '18 at 05:18