Querying a Recordset 
Author Message
 Querying a Recordset

I need to query a recordset object returned by (preferably) DAO and/or ADO.
That is, I need to use DAO/ADO to query a table in a Jet database, return
the results in a recordset object, and then turn around and query that
recordset.

How can I do this?

Many thanks!
--
Wayne M. Syvinski

"There ain't a horse that can't be rode, and there ain't a rider that can't
be throwed."



Tue, 19 Mar 2002 03:00:00 GMT  
 Querying a Recordset

Quote:

> I need to query a recordset object returned by (preferably) DAO and/or ADO.
> That is, I need to use DAO/ADO to query a table in a Jet database, return
> the results in a recordset object, and then turn around and query that
> recordset.

> How can I do this?

  Using Access?  One approach is to get your first records by creating a
query (it can be a temporary query for this example) - you can retrieve
the records from the query if you need to do something with that first
set of data.  Then, open a recordset against the query for your second
subset of the data.  Something like:

Dim dbWayne As Database
Dim qdfTemp As DAO.QueryDef
Dim rs2 As DAO.Recordset
Set dbWayne = OpenDatabase("C:\SomePath\your.mdb")

'create the query
Set qdfTemp = dbWayne.CreateQueryDef("qryTemp", _
    "SELECT * FROM SomeTable WHERE SomeField = 'something'")
Set qdfTemp = Nothing
'at this point, you could open a recordset to get at what's in the
query,
' like:  Set rs1 = dbWayne.OpenRecordset("qryTemp")

'now open a recordset, further winnowing down what's in the query
Set rs2 = dbWayne.OpenRecordset("SELECT * FROM qryTemp WHERE " _
                                & " OtherField = 'whatever'")
'do what you will with this recordset

'clean up
rs2.Close
dbWayne.Execute ("DROP TABLE qryTemp")
dbWayne.Close

--
Jim in Cleveland
If you're writing to me, in my address
change "REAL_Address.see.below" to "worldnet.att.net"

"What's so funny 'bout peace, love & understanding?"
     - Nick Lowe



Wed, 20 Mar 2002 03:00:00 GMT  
 Querying a Recordset
Wayne

In both DAO and ADO you can set the Filter property of a recordset (which is
based on the WHERE part of an SQL statement without the WHERE) and then open a
new, filtered recordset from the original.  The syntax is different in the two
cases, but for once the VB6 MSDN help gives code examples.  Look up Filter.

John.........

Quote:

> I need to query a recordset object returned by (preferably) DAO and/or ADO.
> That is, I need to use DAO/ADO to query a table in a Jet database, return
> the results in a recordset object, and then turn around and query that
> recordset.

> How can I do this?

> Many thanks!
> --
> Wayne M. Syvinski

> "There ain't a horse that can't be rode, and there ain't a rider that can't
> be throwed."



Thu, 21 Mar 2002 03:00:00 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Querying a recordset

2. querying a recordset

3. Query Excel Recordset

4. Query on recordset (ACC97)

5. Problems Trying to Query a Recordset

6. Open Access 2.0 query as recordset

7. Using DAO to open pre-defined Access queries - returned recordset bad

8. query a recordset?

9. Querying a Recordset: Is this possible?

10. query an recordset

11. Can I query a recordset?

12. SQL Queries for recordset

 

 
Powered by phpBB® Forum Software