Hi Guys
Having some problems with connecting to an oracle database with VBA.
Can run queries against the database using Excels 'New Database Query' without a problem.
Can also simulate this in VBA with the following:
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DRIVER={Microsoft ODBC for Oracle};UID=xxxxxxxx;PWD=xxxxxxxx;SERVER=xxxx_cap1;" _
, Destination:=Range("A1"))
.sql . ... etc.
This works no problems. What i would like to do is access the same database for use with a recordset giving me more control over the data. I'm used to using ODBC connecting to SQL Server / Access but fairly new to oracle. I think i might have to use an ADODB connection. Please can anyone throw me some code ?
Ideally as similar as possible to the format i'm used to with ODBC...
eg.
Dim wsp as workspace, dbs as database, rst as recordset
set wsp = workspaces(0)
set dbs = wsp.opendatabase("MyDatabase")
set rst = dbs.openrecordset("Some SQL Query")
Many thanks
sugarflux
Having some problems with connecting to an oracle database with VBA.
Can run queries against the database using Excels 'New Database Query' without a problem.
Can also simulate this in VBA with the following:
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DRIVER={Microsoft ODBC for Oracle};UID=xxxxxxxx;PWD=xxxxxxxx;SERVER=xxxx_cap1;" _
, Destination:=Range("A1"))
.sql . ... etc.
This works no problems. What i would like to do is access the same database for use with a recordset giving me more control over the data. I'm used to using ODBC connecting to SQL Server / Access but fairly new to oracle. I think i might have to use an ADODB connection. Please can anyone throw me some code ?
Ideally as similar as possible to the format i'm used to with ODBC...
eg.
Dim wsp as workspace, dbs as database, rst as recordset
set wsp = workspaces(0)
set dbs = wsp.opendatabase("MyDatabase")
set rst = dbs.openrecordset("Some SQL Query")
Many thanks
sugarflux