Re: Stand Alone Recordsets

Ok, so the answer is: "not possible to create a new stand alone recordset,
using a SQL query on two opened stand alone recordsets"...

The method of loop in data of recordsets is very expensive for a large
number of rows...

Anyway, thanks.

Ilanio

"Bob Barrows [MVP]" wrote:

Ilanio wrote:
Note the sample code I made in VBScript:

'--------------------------------------------------
Set rst = CreateObject("ADODB.Recordset")
rst.CursorLocation = 3 ' adUseClient
rst.CursorType = 1 ' adOpenKeyset
rst.LockType = 3 ' adLockOptimistic


I believe these settings are ignored for ad hoc recordsets.

<snip>

'Now I have another recordset
Set rts = CreateObject("ADODB.Recordset")
<snip>
'Now I want to create a new recordset with data in the recordset 1
(rst) 'equals in the recordset 2 (rts)

'I try something like the code below withou success:

Set res = CreateObject("ADODB.Recordset")
res.CursorLocation = 3 ' adUseClient
res.CursorType = 1 ' adOpenKeyset
res.LockType = 3 ' adLockOptimistic

res.Open "SELECT rst.* FROM rst INNER JOIN rts ON rst.Name = rts.Name"
'The above line produces error


Of course it does. ADO is not a database engine. you need a database
engine to run sql statements ... as I said in my initial reply:


No. You need a database engine to execute sql statements. None
exists in this situation. You will need to run nested loops.

IOW, you are going to need to create a third ad hoc recordset and
populate it by looping through one of the first two recordsets you
created. something like this:

Set res = CreateObject("ADODB.Recordset")
res.Fields.Append "Name", 200, 255 ' adVarChar
res.Open
rst.MoveFirst
Dim sName
Do Until rst.EOF
sName = rst(0).value
rts.Filter = "Name='" & sName & "'"
if not rts.EOF then
res.AddNew "Name", sName
End If
rts.Filter = ""
rst.MoveNext
Loop


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.



.

Relevant Pages

  • Re: Emailing a Report
    ... the reason Debug.Print was done was to test the SQL of what is being generated. ... If you will add and change data here, you need to set this up as a main/subform -- or just a main form that DISPLAYS data from Users and allows modifications to Agreements. ... If you are just going to send Agreements, then AgrID should be added to the form RecordSet so you can capture it. ... maybe also some kind of category for this followup type ...
    (microsoft.public.access.modulesdaovba)
  • Re: What is the advantage of Event programming?
    ... >> dice what lumps of data you need. ... which wouldn't work if the recordset has more than ... >My knowledge on using SQL statements is somewhat limited. ... Requesting a limited number of records at a time, was useful in the old days ...
    (microsoft.public.vb.general.discussion)
  • Re: REPOST: One Web Service updates SQL, the other cant
    ... insert is executed a reference to rs.eof is invalid and the program bombs. ... > get recordset back and ADO could generate error here. ... Another instance of the same Web Service code, ... >> Watching both the debugger and the trace, SQL is receiving what I send ...
    (microsoft.public.vb.database.ado)
  • Re: using Command to set Parameters and Recordset to retrive the Query
    ... doesn't the rsData will be interpretate as an input parameter in the SP? ... >> Query and retrive the Recordset so I can use the Paging property ... > Even if this technique of using the parameters in the ORDER BY does work for> you, I suspect that this will defeat your objective of preventing sql> injection. ... See below for a more efficient solution> using a stored procedure. ...
    (microsoft.public.inetserver.asp.general)
  • Re: Help please on Record sets
    ... I tried running it from the query builder after deleting all the ... Just because your "original SQL string was generated in a working ... I had to add a quote to your first Formated Date and Change the quotes to ... recordset or would that lead to more problems? ...
    (microsoft.public.access.modulesdaovba)