open 2 recordsets at once?

[lmawler]

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("concatena tedField") = rst2("concatena tedField") & 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

[pks00]

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.openr ecordset("selec t * from table1")
set rst2 = currentdb.openr ecordset("selec t * 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("concatena tedField") = rst2("concatena tedField") & rst1("Field1")
&...
rst2.update
rst2.movenext
loop
end if

rst1.movenext
loop

rst1.close
rst2.close
set rst1=nothing
set rst2=nothing

[NeoPa]

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.

[Andrew Thackray]

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("concatena tedField") = rst2("concatena tedField") & 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

[NeoPa]

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.

[pks00]

I personally dont believe u should be recreating recordsets based on the same table but different filters. Using the find method should be sufficient

[MMcCarthy]

try this:

 
Dim db As Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim concat As String
	Set db = CurrentDb
	Set rs1 = db.OpenRecordset("table1")
	Set rs2 = db.OpenRecordset("table2")
	
	rs2.MoveFirst
	Do Until rs2.EOF
		concat = rs2!concatenatedField

		rs1.MoveFirst
		Do Until rs1.EOF
			If rs2![KeyField] = rs1![KeyField] Then
				concat = concat & rs1!Field1
			End If
			rs1.MoveNext
		Loop
		
		rs2.Edit
		rs2!concatenatedField = concat
		rs2.Update
		
		rs2.MoveNext
	Loop

	rs1.Close
	rs2.Close
	Set rs1 = Nothing
	Set rs2 = Nothing

[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.OpenRecordse t("table2")

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

concat = rs2!concatenate dField

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

rs2.MoveNext
Loop

rs2.Close
Set rs2 = Nothing

:)

[MMcCarthy]

Hi everybody I suggest that the filter be in the recordset that is open Coz it's more quickly :)

:)

Good suggestion!!

[MMcCarthy]

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.