Changing Oracle connection from DSN to DSN-Less

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

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.

"Red" wrote:

> 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
>
>

Oops, small typo ...

Of course the first line should read ...

Dim strCnn As String

Enjoy! Sean.

"Sean Connolly" wrote:

> 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.
>
> "Red" wrote:
>
> > 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
> >
> >

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