Pass password for an ODBC link to an Oracle DB.

Hi

Try recording the code and if your using MSQuery then you will find the code will just after the DSN part house the password thus no need to enter it

Does that help ??

Jack

this i found from my ODBC link NOT your system thou should be kinda the same i hope

With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=Kopen3 Live;UID=JACK;;SERVER=ufsvr20;DBNAME=KO3LIVE;LUID=JACK;", _
Destination:=Range("A1"))
.Sql = Array( _

On 2002-10-17 14:55, jag108 wrote:
I have multiple sheets extracting varied amounts of data, I need to update these sheets regularly through the day. Is it possible to pass the password to the prompt that appears upon every sheets login to the DB.

I am using an Oracle73 set up in the database administrator for win NT 4(ha ha win NT 4 you say)
Please say yes

Click to expand...

Have you checked the "Save password" Data Range Property?

I have tried most of the suggestions with out success.

Recording a macro yeilded,
Sub actions()
'
' actions Macro
' Macro recorded 18/10/02 by Jag108
'

'
End Sub

The next suggestion yeilded,
a greyed out save password option.

As for the codeded version I have yet to figure out all of the parameters. I will let you all know.

Thanks heaps people for the response to my problem.

Coded version unable to be figured out, I am using this code to do the extract.
Sub GetOracleDataAll()
Dim ws As Worksheet
Dim Msg
For Each ws In Worksheets
If ws.Name = "Over View" Or ws.Name = "Sheet3" Or ws.Name = "Sheet4" Then
Else
ws.Select
cells(1, 1).Select
Msg = MsgBox("Refreshing worksheet " & ws.Name & " .", vbYesNo + vbInformation)
If Msg = vbNo Then
If ws.Name = "Open Calls" Then
Else
Worksheets("Open Calls").Select
End
End If
End If
Selection.QueryTable.Refresh BackgroundQuery:=False
Msg = MsgBox("Data refresh for worksheet " & ws.Name & ", completed successfully!", vbOKOnly + vbInformation)
End If
Next
Worksheets("Open Calls").Select
UserForm2.VantiveOption.Enabled = False
UserForm2.HeadingsOption = True
UserForm2.CommandButton1.Enabled = True
UserForm2.CommandButton1.Caption = "Refresh headings"
UserForm2.Show
End Sub

Hi

Your code looks like it only refreshes and reset existing MSQuesy ODBC connection not a NEW query

I would try recording from scrach, you say recorded and recv blank module, did you try to record NEW??

This i recorded a wile back and is as i used to use, it will not work on your system as is linked to my system but this is what you should get::

As you see it is clearly recorded!
Ok my Irish friend recorded this on his system, but thats not the issue see the sub name is monkey time..

Sub JACKintheUKisstayingforever()
‘ Attribute JACKintheUKisstayingforever.VB_Description = "Macro recorded ‘ ‘15/09/00 by ejm"
‘Attribute JACKintheUKisstayingforever.VB_ProcData.VB_Invoke_Func = " n14"
'
' JACKintheUKisstayingforever Macro
' Macro recorded 15/09/00 by ejm
'

'
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=Kopen3 Live;UID=emc;;SERVER=ufsvr20;DBNAME=KO3LIVE;LUID=emc;", _
Destination:=Range("A1"))
.Sql = Array( _
"SELECT ""Sales Ledger Customer File Co 01"".""Address 01"", ""Sales Ledger Customer File Co 01"".""Address 02"", ""Sales Ledger Customer File Co 01"".""Address 03"", ""Sales Ledger Customer File Co 01"".""Address 04" _
, _
"""" & Chr(13) & "" & Chr(10) & "FROM ""Sales Ledger Customer File Co 01"" ""Sales Ledger Customer File Co 01""" _
)
.FieldNames = True
.RefreshStyle = xlInsertDeleteCells
.RowNumbers = False
.FillAdjacentFormulas = False
.RefreshOnFileOpen = False
.HasAutoFormat = True
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SavePassword = False
.SaveData = True
End With
End Sub

You should add the password to the connection string in the same way you add the user id (UID) but use password=xxxx

It's best this way and not saved with the query as a secure system would insist the user changes their password often, your query with the saved password would only work for the period your password was 'live'

This way is still safe as you could use an input box to prompt for the password.

If you want ti to be totally run in the backgropund with no user input then add it to the script or save it as previously mentioned - but make sure your sys admin keeps one account open for you which does not require password changes - and NEVER let on to an auditor !!!

This is looking good.
But still not able to get pas the first password input
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=FQHN;UID=MY_UID;PASSWORD=MYPASSWORD;DBQ=My.server;ASY=OFF;", _
Destination:=Range("A1"))
.Sql = Array(
Still asks for the first password, I have set to True the remember password later on in the code, if that matters.