Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
link ODBC connection to an Oracle database | Excel Discussion (Misc queries) | |||
XL - Oracle connection Error | Excel Discussion (Misc queries) | |||
XL - Oracle Connection error | Excel Programming | |||
ADO Connection to ORACLE 9.2 through EXCEL fails | Excel Programming | |||
connection to oracle with excel | Excel Programming |