Re: Stand Alone Recordsets

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: List CursorTypes vs LockTypes for SQLOLEDB
    ... but they introduce inefficiencies into the process that a server ... Recordsets, especially editable recordsets, consume resources ... However, if you must use a recordset, use the cheapest cursor type that will ... adOpenKeyset ...
    (microsoft.public.scripting.vbscript)
  • Re: RS.open ist schnell, folgendes RS.? lahm
    ... >> Dann solltest du es lieber mit adUseClient versuchen, ... Eigenschaften dieses Recordsets geprüft? ... Öffnen der Connection und zum Öffnen des Recordsets sieht. ... Code "auf der Arbeit" verwende. ...
    (microsoft.public.de.vb.datenbank)
  • Re: SQL Query return 2 recordsets
    ... :i have a query that returns 2 recordsets. ... : Dim oDBConn ... : oRS1.CursorLocation = adUseClient ...
    (microsoft.public.scripting.vbscript)
  • recordset connection network failure
    ... my Access app has many recordsets open. ... all recordsets use adUseClient, ... having read some posts i understand that the _recommended_ method is to ... disconnect it after use. ...
    (microsoft.public.data.ado)
  • disconnected recordset form
    ... my Access app has many forms backed by recordsets open. ... all recordsets use adUseClient, ... disconnect it after use. ...
    (microsoft.public.access.forms)