How to provide SQL credentials once for multiple MS Query calls?

Question

I have a spreadsheet that gathers external data from an Oracle database using Microsoft Query; I'm using Excel for its chart-building capabilities.   

Two separate data sets are pulled, which requires two different queries (the number of queries could increase in the future).  The problem I am trying to resolve is that I am prompted for my SQL credentials twice, even though both calls go to the same database.  Is there a way I can arrange the external data calls so my credentials are cached, so I can input my credentials once, and have all other calls to the database use those credentials?  I've tried using OLE, but I'm still prompted twice as well.

I'm unable to save username/passwords because other people will likely use the spreadsheet, so they will need to use their own credentials.  Also, the passwords appear to be stored in plain text on the spreadsheet...

Any ideas how to use credentials once for all subsequent database calls?

Thanks.

• J

Answer 1

Nothing simple here... You can use macros to control the queries but it is a bit of work to make it all happen. You will need to create a userform where the user inputs their user name and password. You can then use those as inputs to refresh the query. IIRC that info is passed in the command text. I think you can modify the text prior to it bieng executed. I personally use ADODB with an ODBC connection to execute SQL that I store directly in the macro.

Answer 2

Mission accomplished.

Here's a general map of how I figured it out:

ADO VB Code:

http://hoopercharles.wordpress.com/2009/12/02/retrieve-data-to-excel-with-a-macro-using-ado/

Creating a user form:

http://www.contextures.com/xluserform01.html

One macro shows the user form, and another macro contains the ADO code that brings in userForm.UserName.Value and userForm.UserPassword.Value.

Answer 3

Nothing simple here... You can use macros to control the queries but it is a bit of work to make it all happen. You will need to create a userform where the user inputs their user name and password. You can then use those as inputs to refresh the query. IIRC that info is passed in the command text. I think you can modify the text prior to it bieng executed. I personally use ADODB with an ODBC connection to execute SQL that I store directly in the macro.

Thanks, ADODB in a macro looks promising and doable.