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