Turn Excels 'New Database Query' into VBA

[sugarflux]

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

 

[SkipVought]

"Having some problems with connecting to an oracle database with VBA."

What problems? I do this every day.

However, you only ADD a querytable ONE TIME. From then on it's a simple REFRESH.

Skip,

_[red][/red]

 

[sugarflux]

Hi Skip

Sorry - i don't think i was clear...

I don't want to use a query table.. I would like to set up the connection as an object and then loop through my recordset -

set rst = dbs.openrecordset("SELECT Field1 FROM Table1")
do while not rst.eof
Msgbox rst("Field1")
rst.movenext
loop

(as a primitive example!)

I'm used to doing this for Access databases and SQL server but can't seem to figure out how to set the 'dbs' object to the orcale database...

Thanks

sugarflux

 

[SkipVought]

Is there a REASON for not using MS Query?

    sServer = "A010PROD"
    cnn.Open "Driver={Microsoft ODBC for Oracle};" & _
               "Server=" & sServer & ";" & _
               "Uid=/;" & _
               "Pwd="

Skip,

_[red][/red]