Query Table keeps prompting for password

[berlinhammer]

Hello,

Something is puzzling me with a VBA macro I am running. I have a connection string that uses a DSN to connect to an external Oracle database and refresh a query table in excel. All the login info is in the connection string so I am confused as to why a prompt asking for my login details is coming up everytime the query is run. It is annoying and interrupting the macro. Would anybody know how I can amend the code to get rid of it?

Many thanks

Jon

Sub RefreshOdbcQt(strSql$, strShName$, sh)

Dim qt As QueryTable
Dim strConn As String
Dim rngDest As Range

strConn = "ODBC;DSN=intra;UID=reporter;PWD=XXXXX;DBQ=INTRA ;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=O;"
Set rngDest = Range(strShName & "_" & "report_header")

If sh.QueryTables.Count = 1 Then
    rngDest.CurrentRegion.ClearContents
    Set qt = sh.QueryTables(1)
ElseIf sh.QueryTables.Count = 0 Then
    rngDest.CurrentRegion.ClearContents
    Set qt = sh.QueryTables.Add(Connection:=strConn, Destination:=rngDest)
Else:
    MsgBox "Multiple Query Tables detected"
    Exit Sub
End If
    
    With qt
        .CommandText = strSql
        '.Name = "Query from intra"
        '.FieldNames = True
        '.RowNumbers = False
        '.FillAdjacentFormulas = False
        '.PreserveFormatting = True
        '.RefreshOnFileOpen = False
        '.BackgroundQuery = True
        .RefreshStyle = xlOverwriteCells
        '.SavePassword = True
        '.SaveData = True
        '.AdjustColumnWidth = False
        '.RefreshPeriod = 0
        '.PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
    
Set qt = Nothing
   
End Sub

 

[RoryA]

You may simply need to uncomment the:

'.SavePassword = True

line.

 

[berlinhammer]

Thanks Rory,

It looks like there's no point including it in the connection string then. Any idea why it works that way or is it just VBA and an Oracle driver not being all that compatible?

It's quite a big query I have to run four times, so I guess I'll just have to change the code, run it all through once (providing the password four times) and thereafter it shouldn't prompt me any more.

Unless I can assign the password via code without having to refresh?

Cheers,

Jon

 

[RoryA]

Did you try uncommenting the line?

 

[berlinhammer]

Yes I did rory. It seems to need the password in the first place though so I've got my 'Refesh All' macro chuntering on in the background asking me for the password every few minutes when it moves onto another query table. Hopefully once I've done that and saved it won't need the password for future refreshes.