Creating a new recordset from an existing recordset

Creating a new recordset from an existing recordset

Post by Michael Schamberg » Sat, 30 Aug 1997 04:00:00



I swear I've done this, but I can't find the code, and the intuitive
approach isn't working.  I'm working with VB5 and DAO 3.5.

If I have a recordset call rsMain that is valid and contains x
records and was properly created using a SQL statement:

strSQL = "SELECT * FROM Invoice Where ((Invoice.Date) = #" & _
txtCheckDate & "# ORDER BY InvoiceNo;"

set rsMain = dbData.OpenRecordset(strSQL)

then I want to create a new recordset based on rsMain, using a new
strSQL I would think that:

set rsNew = rsMain.OpenRecordset(strSQL)

would do it.  I want to hit against the recordset because it's
infinitely smaller then the original database,  so I'm trying to avoid
simply adding my 2 SQL criterias together and going against my
original database.

Any help would be appreciated.

 
 
 

Creating a new recordset from an existing recordset

Post by abclearn.. » Sat, 30 Aug 1997 04:00:00


On Fri, 29 Aug 1997 14:30:58 -0400, Srinivas Kanchibhotla >

Quote:>   Welcome to the group of VB 3.0 mourners. In VB3.0, your problem can
>be solved by using the clone method. i.e.
>    Set rsNew = rsMain.clone()
>and By using the filter property with the where criterion, you can have
>the desired recordset without hitting the database again. i.e.
>    rsNew.filter = "[column1] = blah and [column2] = blah"
>    Set rsNew = rsNew.createsnapshot[createdynaset]()
>But with VB5, the Clone method can be applied on to ODBC-direct
>databases. If yours is one, then U are lucky. You can use the same
>method described above. But with RD0 2.0, you have no alternative but
>creating another resultset with the modified query.

He's not using RDO, he's using DAO 3.5, and the Clone method works on
a Jet recordset.

Stu


 
 
 

Creating a new recordset from an existing recordset

Post by Rick » Mon, 01 Sep 1997 04:00:00



>I swear I've done this, but I can't find the code, and the intuitive
>approach isn't working.  I'm working with VB5 and DAO 3.5.

>If I have a recordset call rsMain that is valid and contains x
>records and was properly created using a SQL statement:

>strSQL = "SELECT * FROM Invoice Where ((Invoice.Date) = #" & _
>txtCheckDate & "# ORDER BY InvoiceNo;"

>set rsMain = dbData.OpenRecordset(strSQL)
>then I want to create a new recordset based on rsMain, using a new
>strSQL I would think that:

>set rsNew = rsMain.OpenRecordset(strSQL)

>would do it.  I want to hit against the recordset because it's
>infinitely smaller then the original database,  so I'm trying to avoid
>simply adding my 2 SQL criterias together and going against my
>original database.

>Any help would be appreciated.

I don't understand the answers to this.Can this be done in 4.0 using
DAO 3.0 or not? How? TIA


 
 
 

Creating a new recordset from an existing recordset

Post by abclearn.. » Mon, 01 Sep 1997 04:00:00



>I don't understand the answers to this.Can this be done in 4.0 using
>DAO 3.0 or not? How? TIA

No, not directly.

There are two workarounds, both of which can be done in 3.0, 4.0, or
5.0 using any compatible version of DAO:

(1) Create a clone of the first recordset, using the Clone method of
the Recordset object. Then apply the Filter and/or Sort methods of the
Recordset object on the clone to select the records you want to see
and order them the way you want them ordered.

(2) Instead of creating the first recordset, use a Make Table query
"SELECT field1[, field2[, ...]] INTO newtable FROM sourcetable") to
create a new, temporary table. You can then create as many recordsets
as you want from the temporary table. When you're done with the
temporary table, use a DROP TABLE SQL statement to blow it away.

Stu

 
 
 

Creating a new recordset from an existing recordset

Post by Wesley Davi » Thu, 11 Sep 1997 04:00:00


Michael Schamberger:

Quote:>then I want to create a new recordset based on rsMain, using a new
>strSQL I would think that:

>set rsNew = rsMain.OpenRecordset(strSQL)

The documentation on recordsets, in particular the .Filter property.
Settings and Return Values

The setting or return value is a String data type that contains the WHERE
clause of an SQL statement without the reserved word WHERE.

Remarks

Use the Filter property to apply a filter to a dynaset-, snapshot-, or
forward-onlytype Recordset object.
You can use the Filter property to restrict the records returned from an
existing object when a new Recordset object is opened based on an existing
Recordset object.
In many cases, it's faster to open a new Recordset object by using an SQL
statement that includes a WHERE clause.
Use the U.S. date format (month-day-year) when you filter fields containing
dates, even if you're not using the U.S. version of the Microsoft Jet
database engine (in which case you must assemble any dates by concatenating
strings, for example, strMonth & "-" & strDay & "-" & strYear). Otherwise,
the data may not be filtered as you expect.

If you set the property to a string concatenated with a non-integer value,
and the system parameters specify a non-U.S. decimal character such as a
comma (for example, strFilter = "PRICE > " & lngPrice, and lngPrice =
125,50), an error occurs when you try to open the next Recordset. This is
because during concatenation, the number will be converted to a string using
your system's default decimal character, and Microsoft Jet SQL only accepts
U.S. decimal characters.
=========
So, it gets set, but only takes affect when you create a new recordset based
on the old one:

rsMain.Filter = strNewSQLWhereClauseOnly
set rsNew = rsMain

Or, the Help file's example is:

This example uses the Filter property to create a new Recordset from an
existing Recordset based on a specified condition. The FilterField function
is required for this procedure to run.

Sub FilterX()

 Dim dbsNorthwind As Database
 Dim rstOrders As Recordset
 Dim intOrders As Integer
 Dim strCountry As String
 Dim rstOrdersCountry As Recordset
 Dim strMessage As String

 Set dbsNorthwind = OpenDatabase("Northwind.mdb")
 Set rstOrders = dbsNorthwind.OpenRecordset("Orders", _
  dbOpenSnapshot)

 ' Populate the Recordset.
 rstOrders.MoveLast
 intOrders = rstOrders.RecordCount

 ' Get user input.
 strCountry = Trim(InputBox( _

"Enter a country to filter on:"))

 If strCountry <> "" Then
  ' Open a filtered Recordset object.
  Set rstOrdersCountry = _
   FilterField(rstOrders, "ShipCountry", strCountry)

  With rstOrdersCountry
   ' Check RecordCount before populating Recordset;
   ' otherwise, error may result.
   If .RecordCount <> 0 Then .MoveLast
   ' Print number of records for the original
   ' Recordset object and the filtered Recordset
   ' object.

strMessage = "Orders in original recordset: " & _
    vbCr & intOrders & vbCr & _
    "Orders in filtered recordset (Country = '" & _
    strCountry & "'): " & vbCr & .RecordCount
   MsgBox strMessage
   .Close
  End With

 End If

 rstOrders.Close

 dbsNorthwind.Close

End Sub

Function FilterField(rstTemp As Recordset, _
 strField As String, strFilter As String) As Recordset

 ' Set a filter on the specified Recordset object and then

' open a new Recordset object.
 rstTemp.Filter = strField & " = '" & strFilter & "'"
 Set FilterField = rstTemp.OpenRecordset

End Function

Note   To see the effects of filtering rstOrders, you must set its Filter
property, and then open a second Recordset object based on rstOrders.

Note   When you know the data you want to select, it's usually more
efficient to create a Recordset with an SQL statement. This example shows
how you can create just one Recordset and obtain records from a particular
country.

Sub FilterX2()

 Dim dbsNorthwind As Database
 Dim rstOrders As Recordset

 Set dbsNorthwind = OpenDatabase("Northwind.mdb")

 ' Open a Recordset object that selects records from a
 ' table based on the shipping country.
 Set rstOrders = _
  dbsNorthwind.OpenRecordset("SELECT * " & _
  "FROM Orders WHERE ShipCountry = 'USA'", _
  dbOpenSnapshot)

 rstOrders.Close
 dbsNorthwind.Close

End Sub

 
 
 

1. Creating a new recordset by joining 2 existing recordsets

Maybe I am going about this the wrong way, but my issue is that I need
to process data from a DBaseII table into an SQL database. To do this, I
have created a recordset from the DBase table and another recordset(s)
from the SQL table(s). Not all of the records in the DB table are in the
SQL table but only the matching records are to be updated. OK, simple
enough so far.

I need to find matching records and do some processing on each record
before updating the SQL tables. Since I only have about 5000 records in
the DBase table, and only about 4000 will have a match in the SQL
tables, therefore I need to use:

rsSQL.Find "sqlItem= '" & rsDB!dbItem & "'"

If rsSQL doesn't have the rsDB item, then find takes excessive amounts
of time to complete (statistically around 5 seconds per find).

It would be better to create another instance of the recordset that is a
subset of the initial recordsets to ensure that only matching items are
inclusive.

Can I:
1. Create another recordset by joining 2 existing recordsets?
2. Create an initial recordset by joining a local DBase table with an
SQL table on an enterprise server?

Since you have read this entire, excessively lengthy, message, I welcome
your input and suggestions.

Excellence is achieved through 1% inspiration and 99% perspiration.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

2. Post installation problem on oracle 8.15 in solaris 2.6

3. Creating New SHAPE Recordset from Existing Recordset Object??

4. VB3 and Access 2.0 incompatibility error...

5. making new recordset from existing recordset

6. help xp_cmdshell won't work

7. How to create a Recordset from existing Recordset?

8. PdoxDOS 4.5 cpu utilization

9. How do I create a recordset based on an existing recordset

10. Create a recordset from an existing recordset

11. How do I create a recordset from and existing recordset

12. How do I create a recordset based on an existing recordset

13. Creating a new recordset from an existing one