refresh Query table using own password for each username

K

kuh

Please help me. I have many query table using data source : ODBC and
driver : Oracle. I want to refresh the table using own password for
each username. How to add my macro for doing that?
It's my macro :

Code:
--------------------
Sub refresh1()
With Sheets("Data").Range("B5")
.QueryTable.Refresh BackgroundQuery:=False
End With
End Sub
 
K

K Dales

If you set the querytable properties (right-click on the query data range and
choose "Properties...") you can tell it to remember the password. Or, you
can have your macro edit the QueryTable.Connection property, which has the
connection string; this normally contains the user ID (UID) and password
(PWD); however be advised that with either of these approaches anyone with a
knowledge of VBA could potentially discover your user id and password by
examining the connection string. If security is an issue you should perhaps
set up a different account on the database giving access to only the queries
you need your users to run and use that account rather than your own!
 
R

Rob van Gelder

Instead of configuring an ODBC connection on the Machine tab try configuring
an ODBC connection on the User tab.

If you are using Oracle integrated security, then the username is a
forward-slash.

While experimenting, you may find my Query Editor add-in useful - available
on my website.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top