Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADODB.RecordSet
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!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADODB.RecordSet
Sounds like the SQL is the problem. What is in sSql?
-- --- HTH Bob (change the xxxx to gmail if mailing direct) "Dan Thorman" wrote in message ... 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!! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADODB.RecordSet
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" "Bob Phillips" wrote: Sounds like the SQL is the problem. What is in sSql? -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Dan Thorman" wrote in message ... 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!! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADODB.RecordSet
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" "Dan Thorman" wrote: 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" "Bob Phillips" wrote: Sounds like the SQL is the problem. What is in sSql? -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Dan Thorman" wrote in message ... 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!! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADODB.RecordSet
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) "Dan Thorman" wrote in message ... 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" "Bob Phillips" wrote: Sounds like the SQL is the problem. What is in sSql? -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Dan Thorman" wrote in message ... 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!! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADODB.RecordSet
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/de...cadoobjmod.asp Hope this helps, Rosemary "Dan Thorman" wrote: 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!! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADODB.RecordSet
And you may have to use RecSet(i).Value in your loop!
"RW" wrote: 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/de...cadoobjmod.asp Hope this helps, Rosemary "Dan Thorman" wrote: 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!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete ADODB Recordset | Excel Programming | |||
ADODB Recordset problem | Excel Programming | |||
0 with ADODB Recordset | Excel Programming | |||
adodb.recordset with excel | Excel Programming | |||
ADODB Recordset | Excel Programming |