ADODB.RecordSet

[Dan Thorman]

Hi. I can't seem to find a good page defining all the methods available to
the ADODB RecordSet and how to use them. So, i'm working blind (as a newbie
without much VBA experience). I'm using Excel 2003, and an ADO connection to
an Oracle Database.

I tried this bit of code (with sSql as a previously defined SQL string, and
ActDB as a previously opened Database connection):

Dim recSet As ADODB.Recordset
Set recSet = New ADODB.Recordset

recSet.Execute (sSql, ActDB)

For i = 1 To iterator
Application.Worksheets("HOTPortfolio").Cells(j, 1) = recSet(i)
Next i

but i get an error that the line "recSet.Execute(sSql,ActDB)" is expecting
an =
What am I doing wrong? Can anyone point me in the direction of a good set of
definitions? (preferably in the style of the online Java Library)

Thanks!!

 

[Bob Phillips]

Sounds like the SQL is the problem. What is in sSql?

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[Dan Thorman]

sSql = "select property.property_name " & vbCrLf
sSql = sSql & "from property, deal " & vbCrLf
sSql = sSql & "where deal.prop_id = property.prop_id and " & vbCrLf
sSql = sSql & "deal.deal_status_id = 9 and " & vbCrLf
sSql = sSql & "deal.accnt_id = " & accountID & vbCrLf
sSql = sSql & "order by property.property_name"

 

[Dan Thorman]

with the same definition of sSql as above, i've changed the code to be this:

Dim recSet As ADODB.Recordset

Set recSet = ActDB.Execute(sSql)

For i = 1 To iterator
Application.Worksheets("HOTPortfolio").Cells(j, 1) = recSet(i)
Next i

and am now getting an error that:
"Compile Error: Expected Function or Variable"

 

[Bob Phillips]

Have you run that query directly on the database? I don't do Oracle, but
wouldn't think you need to define deal in the from, but join into it.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[RW]

Dan,

As far as I know there is no Execute method for the Recordset object.
Try recSet.Open sSql, ActDB instead

Also, the following URL will give you documentation on ADO, but you will
have to dig around:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmscadoobjmod.asp

Hope this helps,
Rosemary

 

[RW]

And you may have to use RecSet(i).Value in your loop!