Open a recordset of another recordset 
Author Message
 Open a recordset of another recordset

Hello,
How do I open another recordset of another recordset?

I've tried this so far to no avail.

SQL = "SELECT Field1 FROM Table WHERE Field2 >" & 10
Set rs = db.OpenRecordset(SQL, dbOpenSnapshot)

for iNum = 0 to 100
        'I wonder what's the value for FROM    
        SQL = "SELECT Count(Field1) AS [FieldCount] WHERE Field1 =" &
iNum

        'jet =datatype error
        Set rs2 = rs.OpenRecordset(SQL, dbOpenSnapshot)

        Msgbox rs2!FieldCount
        rs2.close
next iNum    

Thanks,
Kahn



Wed, 30 Jan 2002 03:00:00 GMT  
 Open a recordset of another recordset

Quote:

>Hello,
>How do I open another recordset of another recordset?

>I've tried this so far to no avail.

>SQL = "SELECT Field1 FROM Table WHERE Field2 >" & 10
>Set rs = db.OpenRecordset(SQL, dbOpenSnapshot)

>for iNum = 0 to 100
>    'I wonder what's the value for FROM    
>    SQL = "SELECT Count(Field1) AS [FieldCount] WHERE Field1 =" &
>iNum

>    'jet =datatype error
>    Set rs2 = rs.OpenRecordset(SQL, dbOpenSnapshot)

>    Msgbox rs2!FieldCount
>    rs2.close
>next iNum    

>Thanks,
>Kahn

If you are just trying to get a field count it is far easier to use a
count with a group by.

SELECT Count(field1) as [field count] group by field 1

If you need to pull the values out, you can either add an order by or
ensure that your cursor is something other than forward only.

In your code, you are opening two connections to the access database;
this can be optimized by setting up a connection object first and
using the same connection object. Without seeing more of the code
(hopefully there is more) I cannot offer much more.

GB
MVP, MCSE

*******************************************************************************
Think outside of the box!
To reply: remove nospamm from both ends.
*******************************************************************************



Wed, 30 Jan 2002 03:00:00 GMT  
 Open a recordset of another recordset
Hello again,

Scenario:A table which includes a date/time field.
Task:Count records that in the same time frame

Table:
Field1=text
field2=date

I wonder is there any performance gain, by picking up a day portion
first, and then count in hours in the day just on the day portion
instead of the full table.
Something like this

SELECT * FROM TABLE WHERE field2 > #30/1/99# AND field2 <= #31/1/99#
'And then count on the small portion for every hour
SELECT COUNT(*) FROM "previous select" WHERE field2 > #1:00# AND
field2 <= #2:00#
'repeat for every hour

Or should I be better off counting on the full table for every hour.

SELECT Count(*) FROM Table WHERE field1 > #30/1/99 1:00# AND field2 <
#31/1/99 2:00#
'repeat for every hour

Is it possible to GROUP BY on the same time frame ie (1am to 2am),
instead of a value?

Thanks,
Kahn


Quote:


>>Hello,
>>How do I open another recordset of another recordset?

>>I've tried this so far to no avail.

>>SQL = "SELECT Field1 FROM Table WHERE Field2 >" & 10
>>Set rs = db.OpenRecordset(SQL, dbOpenSnapshot)

>>for iNum = 0 to 100
>>        'I wonder what's the value for FROM    
>>        SQL = "SELECT Count(Field1) AS [FieldCount] WHERE Field1 =" &
>>iNum

>>        'jet =datatype error
>>        Set rs2 = rs.OpenRecordset(SQL, dbOpenSnapshot)

>>        Msgbox rs2!FieldCount
>>        rs2.close
>>next iNum    

>>Thanks,
>>Kahn

>If you are just trying to get a field count it is far easier to use a
>count with a group by.

>SELECT Count(field1) as [field count] group by field 1

>If you need to pull the values out, you can either add an order by or
>ensure that your cursor is something other than forward only.

>In your code, you are opening two connections to the access database;
>this can be optimized by setting up a connection object first and
>using the same connection object. Without seeing more of the code
>(hopefully there is more) I cannot offer much more.

>GB
>MVP, MCSE

>*******************************************************************************
>Think outside of the box!
>To reply: remove nospamm from both ends.
>*******************************************************************************



Thu, 31 Jan 2002 03:00:00 GMT  
 Open a recordset of another recordset
Hello again,

Scenario:A table which includes a date/time field.
Task:Count records that in the same time frame

Table:
Field1=text
field2=date

I wonder is there any performance gain, by picking up a day portion
first, and then count in hours in the day just on the day portion
instead of the full table.
Something like this

SELECT * FROM TABLE WHERE field2 > #30/1/99# AND field2 <= #31/1/99#
'And then count on the small portion for every hour
SELECT COUNT(*) FROM "previous select" WHERE field2 > #1:00# AND
field2 <= #2:00#
'repeat for every hour

Or should I be better off counting on the full table for every hour.

SELECT Count(*) FROM Table WHERE field1 > #30/1/99 1:00# AND field2 <
#31/1/99 2:00#
'repeat for every hour

Is it possible to GROUP BY on the same time frame ie (1am to 2am),
instead of a value?

Thanks,
Kahn


Quote:


>>Hello,
>>How do I open another recordset of another recordset?

>>I've tried this so far to no avail.

>>SQL = "SELECT Field1 FROM Table WHERE Field2 >" & 10
>>Set rs = db.OpenRecordset(SQL, dbOpenSnapshot)

>>for iNum = 0 to 100
>>        'I wonder what's the value for FROM    
>>        SQL = "SELECT Count(Field1) AS [FieldCount] WHERE Field1 =" &
>>iNum

>>        'jet =datatype error
>>        Set rs2 = rs.OpenRecordset(SQL, dbOpenSnapshot)

>>        Msgbox rs2!FieldCount
>>        rs2.close
>>next iNum    

>>Thanks,
>>Kahn

>If you are just trying to get a field count it is far easier to use a
>count with a group by.

>SELECT Count(field1) as [field count] group by field 1

>If you need to pull the values out, you can either add an order by or
>ensure that your cursor is something other than forward only.

>In your code, you are opening two connections to the access database;
>this can be optimized by setting up a connection object first and
>using the same connection object. Without seeing more of the code
>(hopefully there is more) I cannot offer much more.

>GB
>MVP, MCSE

>*******************************************************************************
>Think outside of the box!
>To reply: remove nospamm from both ends.
>*******************************************************************************



Fri, 01 Feb 2002 03:00:00 GMT  
 Open a recordset of another recordset


Fri, 19 Jun 1992 00:00:00 GMT  
 Open a recordset of another recordset
Hello again,

Scenario:A table which includes a date/time field.
Task:Count records that in the same time frame

Table:
Field1=text
field2=date

I wonder is there any performance gain, by picking up a day portion
first, and then count in hours in the day just on the day portion
instead of the full table.
Something like this

SELECT * FROM TABLE WHERE field2 > #30/1/99# AND field2 <= #31/1/99#
'And then count on the small portion for every hour
SELECT COUNT(*) FROM "previous select" WHERE field2 > #1:00# AND
field2 <= #2:00#
'repeat for every hour

Or should I be better off counting on the full table for every hour.

SELECT Count(*) FROM Table WHERE field1 > #30/1/99 1:00# AND field2 <
#31/1/99 2:00#
'repeat for every hour

Is it possible to GROUP BY on the same time frame ie (1am to 2am),
instead of a value?

Thanks,
Kahn


Quote:


>>Hello,
>>How do I open another recordset of another recordset?

>>I've tried this so far to no avail.

>>SQL = "SELECT Field1 FROM Table WHERE Field2 >" & 10
>>Set rs = db.OpenRecordset(SQL, dbOpenSnapshot)

>>for iNum = 0 to 100
>>        'I wonder what's the value for FROM    
>>        SQL = "SELECT Count(Field1) AS [FieldCount] WHERE Field1 =" &
>>iNum

>>        'jet =datatype error
>>        Set rs2 = rs.OpenRecordset(SQL, dbOpenSnapshot)

>>        Msgbox rs2!FieldCount
>>        rs2.close
>>next iNum    

>>Thanks,
>>Kahn

>If you are just trying to get a field count it is far easier to use a
>count with a group by.

>SELECT Count(field1) as [field count] group by field 1

>If you need to pull the values out, you can either add an order by or
>ensure that your cursor is something other than forward only.

>In your code, you are opening two connections to the access database;
>this can be optimized by setting up a connection object first and
>using the same connection object. Without seeing more of the code
>(hopefully there is more) I cannot offer much more.

>GB
>MVP, MCSE

>*******************************************************************************
>Think outside of the box!
>To reply: remove nospamm from both ends.
>*******************************************************************************



Fri, 01 Feb 2002 03:00:00 GMT  
 
 [ 6 post ] 

 Relevant Pages 

1. Opening recordsets(OpenRecordset vs. Recordset.Open)

2. Opened Recordsets don't show up in Recordsets Collection

3. Recordset Experts...SQL statement on an open recordset?

4. Open ADO recordset on another ADO recordset - possible?

5. open recordset, change connection, update recordset?

6. ADO fails to release Recordset cursor after opening disconnected Recordset

7. database connection remain open if opened via recordset

8. Better to open recordsets and leave them open?

9. Opening an empty recordset even though the opened table has data

10. Connection.Execute vs. Recordset.Open or Command.Open

11. Opening an ADOX Catalog Whilst ADO Recordset is Open

12. Update recordset from another recordset

 

 
Powered by phpBB® Forum Software