Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Turn Excels 'New Database Query' into VBA

Status
Not open for further replies.

sugarflux

Technical User
Aug 14, 2003
111
GB
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."

What problems? I do this every day.

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

Skip,

[glasses] [red][/red]
[tongue]
 
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
 



Is there a REASON for not using MS Query?
Code:
    sServer = "A010PROD"
    cnn.Open "Driver={Microsoft ODBC for Oracle};" & _
               "Server=" & sServer & ";" & _
               "Uid=/;" & _
               "Pwd="

Skip,

[glasses] [red][/red]
[tongue]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top