query against ADO recordset

[joel009]

Is there any way I can run SELECT query against an open ADODB recordset? I need to do sum's and group by's and can't simply use a filter. Using VB6 SP4.

Thanks,

Joel

 

[lovell811]

When you open you ado in your after your select statement use the "ORDER BY " ----------------
Joe

 

[johnwm]

here is a typical SQL statement to fill a DataControl.
This should get you started

Private Sub Command1_Click()
Dim mySQL As String
Dim tblName As String
mySQL = "SELECT tblClients.ClientName, tblClients.Add1, tblClients.Add2, "
mySQL = mySQL & "tblClients.Contract, tblClients.Sold, tblClients.Date, "
mySQL = mySQL & "tblClients.Miles, tblClients.PostCode "
mySQL = mySQL & "From tblClients WHERE (((tblClients.Add2) Like '*" & Combo1.List(Combo1.ListIndex) & "*'));"
Data1.RecordSource = mySQL
Data1.Refresh
End Sub

It shows use of SQL, and use of variables from other controls.
You can use any standard SQL statements (GROUP BY, ORDER BY, SUM, COUNT etc) as required.

There is some good starting point help on SQL queries in VBHelp Let me know if this helps
________________________________________________________________
If you are worried about how to post, please check out FAQ222-2244 first

'There are 10 kinds of people in the world: those who understand binary, and those who don't.'

 

[CCLINT]

Hey guys, am I reading this wrong or you:

Is there any way I can run SELECT query against an open ADODB recordset?
I think the thought is:

rs1 as new recordset
rs2 as new recordset

Set rs1 = Etc, etc.

set rs2 = select * from rs1

Try again.

*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!

 

[joel009]

CCLINT has got the idea. I've already got a detail recordset and want to run sums against the details without having to go all the way back to oracle. All the infor is in the first recordset and I'd like to be able to treat it as a table.

Joel

 

[CajunCenturion]

Why can't you use the Filter? Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[johnwm]

That will teach me to read the question! CCLINT has the answer! Let me know if this helps
________________________________________________________________
If you are worried about how to post, please check out FAQ222-2244 first

'There are 10 kinds of people in the world: those who understand binary, and those who don't.'

 

[joel009]

The filter won't allow the use of sum and so far I haven't been able to run even a simple "select * from RS1" and get it to work.

Joel

 

[CCLINT]

>CCLINT has the answer
No, I didn't show the answer in my post. I was only trying to put into words for you what the questioner was asking.
So "select * from RS1" of course doesn't work - it was just a wording.

CajunCenturion answer is one of the good possible answers. If I may suggest using a recordset Clone, if that method is supported, and then using the filter.

But, now quess I wasn't reading the question - desired are a recordset with Totals. And that cannot be done with a Filter.

>>without having to go all the way back to oracle<<
It may be faster to do so....
*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!

 

[Paladyr]

You could have a local Access database that you use for temporary tables.... just include it as part of the install if you are distributing this program.

 

[CCLINT]

Why don't you get the sums along with the original query? *******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!

 

[CCLINT]

Sorry, I forgot you have a master/detail form *******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!

 

[RiverGuy]

Can't you do something like:

Dim dblTotal
rst.MoveFirst

Do Until rst.EOF
dblTotal = dblTotal + rst![Field]
rst.MoveNext
Loop

 

[CCLINT]

Sure, if that is all that is being done with the totals and a recordset object isn't needed later....I wasn't assumming this, but maybe I was wrong for doing so.

*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!

 

[RiverGuy]

How would that hamper future usage of the recordset or total? He could put all that in a sub procedure, and call it every time he has to get the total.

 

[joel009]

I need 24 totals from a recordset with about 3000+ records and growing. The SUM function works fast so I was hoping I could use it. Seems the best way is to go back to the oracle tables.

Joel

 

[RiverGuy]

That's probably the best way, but I was thinking--what if you retreived your recordset, and have your fields or whatever. Then you go back, requery those same fields, just to get the total, and a new record was added, changed or deleted? Just a thought.

 

[vb5prgrmr]

just a thought...

select *, count(*) as TheCount, sum(*) as TheSum from table1
where
group by
order by

This will be a bit redundant in the fact that each record will contain the same &quot;TheCount&quot; and &quot;TheSum&quot; information but this will not help you if you are looking aggregate sums based upon each group.

It is a bit unclear what exactly you are trying to do.

Hope this helps

 

[joel009]

Basically trying to query a recordset to create another recordset. I need to be able to use SQL so the filter won't work.

SELECT SUM<whatever> from ADODC Recordset WHERE ???? ORDERBY??? GROUP BY ????.
The filter won't allow me to do it. I tried cloning too and it didn't help.

Joel

 

[CCLINT]

joel009, you're off on a wrong mis-understanding, I think. You cannot do it that way.

Best is to open another recordset object and retrieve the key name fields and the sums using a Group By query.

It would be pretty fast if you just use, for instance, the primary key, the secondary key, and for each of the fields that you want sums on, include those fields as in:

SELECT ThePrimKey,TheSecondaryKey,Sum(TheField) As TotalOfTheField,Sum(TheNextField) As TotalOfTheNextField......
From TheTable
Groupy By ThePrimKey, TheSecondaryKey

(If the SecondaryKey is not needed then remove it) *******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!