By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,264 Members | 1,271 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,264 IT Pros & Developers. It's quick & easy.

open 2 recordsets at once?

P: 14
Hi,

I'm trying to open two recordsets (each from a different table) so that I can take the data from one and put it in the other, when it matches.

Essentially, I'm doing an outer join ... why you might ask? I need to take multiple records out of one table and concatenate fields from them into one field (one record) in the other table.

So what I want to do is this:

rst1.open select * from table1

while (not (rst1.eof)
stSql = "select * from table2 where keyField = '" & rst1("keyField") & "'"
rst2.open stSql
while (not (rst2.eof))
rst2("concatenatedField") = rst2("concatenatedField") & rst1("Field1") &...
rst2.movenext
wend
rst1.movenext
wend

rst1.close
rst2.close

unfortunately, "rst2.open stSql" is giving me trouble, because it says my object is already open.

thoughts?

thanks!

Lea Ann
Oct 26 '06 #1
Share this Question
Share on Google+
9 Replies


pks00
Expert 100+
P: 280
how about this? ok, its dao but this kinda coding, its better than ado and in my personal opinion, using DAO is better, more suited with access that ADO
U may need to add DAO Object library as a reference



dim rst1 as dao.recordset
dim rst2 as dao.recordset

set rst1 = currentdb.openrecordset("select * from table1")
set rst2 = currentdb.openrecordset("select * from table2")
do while rst1.eof = false
rst2.findfirst "keyfield = '" & rst1("keyfield") & "'"
if rst2.nomatch = false then
do while rst2.eof = false
rst2.edit
rst2("concatenatedField") = rst2("concatenatedField") & rst1("Field1")
&...
rst2.update
rst2.movenext
loop
end if

rst1.movenext
loop

rst1.close
rst2.close
set rst1=nothing
set rst2=nothing
Oct 26 '06 #2

NeoPa
Expert Mod 15k+
P: 31,494
That's certainly curious Lea Ann.
I can't see why rst2 would already be open.
pks00's response might help though - I believe MS want to push DAO also and put ADO to bed.
Oct 26 '06 #3

Andrew Thackray
P: 76
Hi,

I'm trying to open two recordsets (each from a different table) so that I can take the data from one and put it in the other, when it matches.

Essentially, I'm doing an outer join ... why you might ask? I need to take multiple records out of one table and concatenate fields from them into one field (one record) in the other table.

So what I want to do is this:

rst1.open select * from table1

while (not (rst1.eof)
stSql = "select * from table2 where keyField = '" & rst1("keyField") & "'"
rst2.open stSql
while (not (rst2.eof))
rst2("concatenatedField") = rst2("concatenatedField") & rst1("Field1") &...
rst2.movenext
wend
rst1.movenext
wend

rst1.close
rst2.close

unfortunately, "rst2.open stSql" is giving me trouble, because it says my object is already open.

thoughts?

thanks!

Lea Ann
Your problem is that you are not closing rst2 before moving to the next rst1 record. Hence when you try to execute the secornd record in rst1 the rs2recordset is still open from the first rst1 record processing.

where you have

wend
rst1.movenext

insert

Wend
Rs2.close
rs1.movenext
Oct 26 '06 #4

NeoPa
Expert Mod 15k+
P: 31,494
Thank you Andrew.
You're absolutely right and you saw what I missed.
Now I can relax.

BTW I think that's also true in pks00's code.
Oct 26 '06 #5

pks00
Expert 100+
P: 280
I personally dont believe u should be recreating recordsets based on the same table but different filters. Using the find method should be sufficient
Oct 27 '06 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
try this:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim db As Database
  3. Dim rs1 As DAO.Recordset
  4. Dim rs2 As DAO.Recordset
  5. Dim concat As String
  6.     Set db = CurrentDb
  7.     Set rs1 = db.OpenRecordset("table1")
  8.     Set rs2 = db.OpenRecordset("table2")
  9.  
  10.     rs2.MoveFirst
  11.     Do Until rs2.EOF
  12.         concat = rs2!concatenatedField
  13.  
  14.         rs1.MoveFirst
  15.         Do Until rs1.EOF
  16.             If rs2![KeyField] = rs1![KeyField] Then
  17.                 concat = concat & rs1!Field1
  18.             End If
  19.             rs1.MoveNext
  20.         Loop
  21.  
  22.         rs2.Edit
  23.         rs2!concatenatedField = concat
  24.         rs2.Update
  25.  
  26.         rs2.MoveNext
  27.     Loop
  28.  
  29.     rs1.Close
  30.     rs2.Close
  31.     Set rs1 = Nothing
  32.     Set rs2 = Nothing
  33.  
  34.  
Oct 27 '06 #7

PEB
Expert 100+
P: 1,418
PEB
Hi everybody I suggest that the filter be in the recordset that is open Coz it's more quickly :)

Just like:

Dim db As Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim concat As String
Set db = CurrentDb
Set rs2 = db.OpenRecordset("table2")

rs2.MoveFirst
Do Until rs2.EOF
Set rs1 = db.OpenRecordset("SELECT * FROM table1 WHERE KeyField='"+rs2![KeyField]+"';")

concat = rs2!concatenatedField

rs1.MoveFirst
Do Until rs1.EOF
concat = concat & rs1!Field1
rs1.MoveNext
Loop
rs1.close
rs2.Edit
rs2!concatenatedField = concat
rs2.Update

rs2.MoveNext
Loop

rs2.Close
Set rs2 = Nothing

:)
Oct 28 '06 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
Hi everybody I suggest that the filter be in the recordset that is open Coz it's more quickly :)

:)
Good suggestion!!
Oct 28 '06 #9

MMcCarthy
Expert Mod 10K+
P: 14,534
Hi everybody I suggest that the filter be in the recordset that is open Coz it's more quickly :)


:)
BTW PEB

have you seen the announcement I posted at top of forum page. I'd welcome your input.
Oct 28 '06 #10

Post your reply

Sign in to post your reply or Sign up for a free account.