Changing Oracle connection from DSN to DSN-Less

Changing Oracle connection from DSN to DSN-Less

Post by Red » Sat, 08 Oct 2005 00:56:26


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
 
 
 

Changing Oracle connection from DSN to DSN-Less

Post by U2VhbiBDb2 » Sat, 08 Oct 2005 02:21:04

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.

 
 
 

Changing Oracle connection from DSN to DSN-Less

Post by U2VhbiBDb2 » Sat, 08 Oct 2005 02:27:10

ops, small typo ...

Of course the first line should read ...

Dim strCnn As String

Enjoy! Sean.

"Sean Connolly" wrote:

 
 
 

Changing Oracle connection from DSN to DSN-Less

Post by Red » Sat, 08 Oct 2005 18:42:10

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