Create Recordset from another Recordset... 1

[xinyin]

I want to know if this is the right way to create a recordset base on the data from another recordset:

I have a recordset (RsetMain), its source is from a table of an Access database (.mdb) - I have no problem in creating this.

Now I want to make another recordset (RsetNew) which picks data from RsetMain. Here is the code I did which works for making RsetMain but not RsetNew:

Public WithEvents Cnet As ADODB.Connection
Public RsetNew As ADODB.Recordset
Public SearchLine as String
SearchLine = "SELECT ItemCode FROM RsetMain WHERE ...(some rules)"

With Cnet
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = _
"Data Source=" & RsetMain & ";" & _
"Persist Security Info=False"
.CursorLocation = adUseClient
.Open
End With

Set RsetNew = New ADODB.Recordset
With RsetNew
.CursorLocation = adUseClient
.ActiveConnection = Cnet
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Open SearchLine
End With

Is the only way to do this is by ADO Command, not Recordset?

 

[HarleyQuinn]

Hi,

Apart from wanting to use it as a basis for your new recordset do you have any other need for RsetMain?? E.G. populating textboxes etc.?

If not how about just combining the two queries into one and having one recordset?

Or even creating a temp table in Access from the first recordset and querying that for your second recordset?

Hope this helps

HarleyQuinn
---------------------------------
Help us to help you,
read FAQ222-2244 before posting.

 

[ChewDoggie]

You can create an exact duplicate of RsetMain with the following code snippet:

Set RsetNew = RsetMain.Clone (I have a question in this forum about this methodology)

You can use the filter property to eliminate data from RsetNew, like this:

RsetNew.Filter = "fname<>'Fritz'"
do while not RsetNew.EOF
RsetNew!fname = ""
loop
RsetNew.Filter = ""

OR you can loop thru the entire RsetMain recordset and manipulate the values in the RsetNew recordset.

Either way.

HTH


ciao for niao!

AMACycle

American Motorcyclist Association

http://www.ama-cycle.org

 

[xinyin]

After several more testing I think I have figured out what is going wrong:

If I creat a recordset from an Access TABLE (tblMain), the ADO syntax is simple -
RsetMain.Open "SELECT * FROM tblMain"
This works.

But next when I try to create a new recordset (RsetNew)from another RECORDSET (RsetMain), the following syntax
RsetNew.Open "SELECT [some fields] from RsetMain" results in run-time error "-2147217865 (80040e37)" (Microsoft Jet Engine cannot find table or query "RsetMain"...)

 

[xinyin]

Sorry I post the last reply by accident before I finished.
Continues here:

... and if I change arround the syntax to be
RsetNew.Open "SELECT [some fields] from '" & RsetMain & "'"
then the error becomes "Type mismatch"

I wonder if ADO only allows you to create recordset from database tables as source but not other recordsets? Or you have to use Commands or "Stored Procedures".

Thanks AMACycle for your clone solution, but this method is not suitable for my case... I remember clone only generates a second cursor in a recordset but it is still the same recordset. But here I want to make a new recordset from the original recordset.

 

[JerryKlmns]

xinyin

The Open method of the recordset object opens a table or a query directly or with any valid SQL statement. You can't make use of an opened recordset to create a new one (except using the Clone method). But why dont you use an SQL statement using the WHERE clause to query the table from which the first recordset retrieves records?

 

[xinyin]

Thanks Jerry,
"But why dont you use an SQL statement using the WHERE clause to query the table from which the first recordset retrieves records?"
This is because I want my program to allow "advanced searching" / "Pyramid shaped searching". For example, if the user is not satisfied with the result of the first recordset (which was created directly from the table), he can input some searching requirements, then the program use these requirements to do a WHERE clause searching from the data of the FIRST RECORDSET, resulting in a SECOND recordset; if he is still not satisfied, he can add new requirements to do searching again according to the result he gets from the SECOND RECORDSET... he can search again and again, reducing the number of resulting records until he is satisfied. Although this can be done by creating recordset from the original table everytime (just change the SQL statement according to the user's requirement everytime), but the SQL statement will be very complicated if the user do the advanced search for more than 10 times. Thus I really need a method in "creating recordset from another recordset".
Is there really no way to do this?

 

[johnwm]

I would go with Harleyquin's suggestion from 31 August, and create a local temp table for each successive query. You will need to ensure a careful clean-up routine!

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[xinyin]

If possible I don't want to create a local temp table in Access because my Access database is only used as a container of data. All the operations are done on the VB side.
Or is there a way to let ADO to command Access to do this?

 

[johnwm]

As I said above <a local temp table> - nothing to do with the original Access stuff!

Have a look in jetsql40.chm and check the index tab for SELECT...INTO usage and syntax

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[HarleyQuinn]

Or is there a way to let ADO to command Access to do this?

Do you mean use ADO to create and drop the temp tables?

If so yes, you are able to do this with ADO.

Using this route would enable you to keep the Access DB as a data container as (ensuring you take John's advice and clean up carefully)the tables will only be there when the VB program requires them i.e. you create them programatically as you need them and then delete (drop) the tables when they are no longer needed by your program. Using this method when you close your app the DB structure will be the same as when you started. If you use SQL through VB code to query the temp tables this will allow you to create new recordsets without generating queries in Access.

Does this sound like a feasible solution for you?

Hope this helps

HarleyQuinn
---------------------------------
Help us to help you,
read FAQ222-2244 before posting.

 

[xinyin]

Thanks I think this method is a possible solution, I will try it. Can you please tell me where I can find "elementary chapters" about it? I search in Google but they only have very "high level" articles, I also tried

http://www.jetsql40.chm

as johnwm suggested but this site does not exist now.

 

[johnwm]

It isn't a website, it's a file on your computer (or should be). See

http://support.microsoft.com/kb/249065

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[strongm]

Oh, I think I'd use a recordset's persistance to solve this one ...

 

[strongm]

(which does indeed give us the ability to create a new recordset from an old one in a much more effective way than the somewhat limited clone method ...)

 

[strongm]

Nobody biting, eh?

 

[bjd4jc]

Well, here is my attempt (not really pertaining to the case problem but an attempt at what strongm was talking about):

    Dim rs1 As ADODB.Recordset
    Dim rs2 As ADODB.Recordset
    Dim LCV As Long
    
    Set rs1 = New ADODB.Recordset
    rs1.Fields.Append "Field1", adInteger
    rs1.Open
    
    For LCV = 1 To 100 Step 1
        rs1.AddNew
        rs1("Field1") = LCV
    Next
    
    rs1.Save "C:\test.fil", adPersistADTG
    
    Set rs2 = New ADODB.Recordset
    rs2.Open "C:\test.fil"
    Do While Not rs2.EOF
        rs2.Update
        rs2("Field1") = rs2("Field1") * 2
    
        rs2.MoveNext
    Loop
    
    rs2.MoveFirst
    rs1.MoveFirst
    
    For LCV = 1 To 100 Step 1
        Debug.Print rs1("Field1"); " : "; rs2("Field1")
        rs1.MoveNext
        rs2.MoveNext
    Next

 

[strongm]

Conceptually close to what I was thinking. Here's my version. You'll need a form with three hierarchical flexgrids (just there to display the results, not part of the 'trick') and a command button:

[blue]Option Explicit

Private Sub Command1_Click()
    Dim myRS1 As Recordset
    Dim myRS2 As Recordset
    Dim myRS3 As Recordset
    
    ' Get our source recordset
    Set myRS1 = GetDisconnectedRecordset("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Microsoft Visual Studio\VB98\NWIND.MDB;Persist Security Info=False", "select * from employees")
    ' Progressively filter to generate two more recordsets derived
    ' directly from the source recordset
    Set myRS2 = GetFilteredRecordset(myRS1, "Country='UK'")
    Set myRS3 = GetFilteredRecordset(myRS2, "TitleOfCourtesy='Mr.'")
    
    
    
    ' All the work has been done. The following is just a
    ' poorish way to show each recordsets is actually different
    Set MSHFlexGrid1.Recordset = myRS1
    Debug.Print myRS1.Filter
    Set MSHFlexGrid2.Recordset = myRS2
    Debug.Print myRS2.Filter
    Set MSHFlexGrid3.Recordset = myRS3
    Debug.Print myRS3.Filter
End Sub

Private Function GetDisconnectedRecordset(strConnect As String, strSQL As String) As Recordset
    Dim rs As Recordset
    Dim cn As Connection
       
    Set cn = New Connection
    cn.Open strConnect
    Set rs = New Recordset
    rs.CursorLocation = adUseClient

    rs.Open strSQL, cn, adOpenStatic
    Set rs.ActiveConnection = Nothing ' disconnect recordset
    Set GetDisconnectedRecordset = rs
    
End Function

Private Function GetFilteredRecordset(rs As Recordset, strAdditionalWhereClause) As Recordset
    Dim myTempStream As Stream
    
    ' similar trick to the following can be done using a property bag if yo insist on using
    ' an older version of ADO that does not support streams
    Set myTempStream = New Stream
    rs.Filter = strAdditionalWhereClause
    rs.Save myTempStream, adPersistXML
    rs.Filter = ""
    
    Set GetFilteredRecordset = New Recordset
    GetFilteredRecordset.Open myTempStream
End Function
[/blue]

 

[bjd4jc]

I never thought about using a stream... also, I thought the documentation stated that you could only use the adPersistADTG format, but I read it pretty quick and probably missed something and I don't have it on this computer to look it over right now...

 

[JerryKlmns]

strongm

A * for sharing tricks....