Changing Oracle connection from DSN to DSN-Less

[Red]

Hi,

I've written an addin that gets data from an Oracle Db and creates a
query table. All works well, except that I can only get it to work by
setting up a DSN connection first. I need to change it so it does not
rely on a DSN, and so will run on any PC (as long as Oracle is
installed). Heres the bit of code that connects to the database. I've
included my attempt of DSN-less, but this throws up an error 1004.

If anyone can point me in the right direction, id be hugely greatful!


Public Sub getData(sSql As String)

Sheets("Data").Select

'DSN Connection that works
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=WMS;UID=user;PWD=password;SERVER=server_name.world;",
Destination:= _
Range("A1"))

'DSN-Less connection that doesn't work
'With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;Driver={Oracle ODBC Driver};" & _
"Dbq=server_name.world;" & _
"Uid=user;" & _
"Pwd=password", Destination:= _
Range("A1"))

.CommandText = sSql
.Name = "Query from WCS"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With

End Sub

 

[Sean Connolly]

Hi Red,

Does it *have* to be ODBC? Assuming you (and users) have the relevant OLE DB
provider(s) installed/registered (MSDAORA.1 and/or OraOLEDB.Oracle.1), why
not something like below ...

Public Sub getData(sSql As String)
Dim Cnn As String
strCnn = "OLEDB;Provider=MSDAORA.1;Password=<pwd>;User ID=<userID>;Data
Source=<dbName>;Persist Security Info=True"
' Enter your own specific user and environment info between the angle
brackets
Sheets("Data").Activate
With ActiveSheet.QueryTables.Add(Connection:=strCnn,
Destination:=Range("A1"))
.CommandType = xlCmdDefault
.CommandText = sSql
.Name = "Query from WCS"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub

No 'fiddling about' with DSNs required.

HTH, and let me know how you get on.

Cheers, Sean.

 

[Sean Connolly]

Oops, small typo ...

Of course the first line should read ...

Dim strCnn As String

Enjoy! Sean.

 

[Red]

Works perfect, you're a star. Thanks Sean!