Joining Two Recordsets

[DBAMJA]

Not enough coffee or something this morning but I just can't wrap my head around this today.

I have two recordsets rsTraArt and rsTraArtHdr that are created using:
set rsTraArt = db.OpenRecordset("....")

Both recordsets have the fields TraSys, TraLvl and TraSeq in common.

How do I join these two large recordsets into one using VBA?

Should be easy but I can't figure it out this morning.

Thanks in advance for any help you can give me.

[flush]

DBAMJA

It is said that God will give you no more than you can handle. I just wish God didn't have so much faith in me.

 

[kjv1611]

Are you wanting to combine the data, or wanting to have one large recordset?

Also, are you needing to edit the recordset?

If you're wanting to join the data in any way, two whole tables, the best way is with a query - whether in Query design, or in VBA code, via SQL..

 

[DBAMJA]

I need to join the two recordsets to make one recordset with all of the info from both but don't need to edit the recordset. Just need to figure out how to programmaticly join the two.

[flush]

DBAMJA

It is said that God will give you no more than you can handle. I just wish God didn't have so much faith in me.

 

[MajP]

Why would you not write a query that joins the two, and then open that as your recordset?

 

[DBAMJA]

MajP:

The original tables that the two recordsets come from all have several million records in them and I am making much smaller recordsets to work with. I am trying to get around performance issues and thought if I used smaller recordsets then trying to join all of the tables together to make the one recordset, that it might be faster. I could be totally wrong but wanted to give it a shot.

[flush]

DBAMJA

It is said that God will give you no more than you can handle. I just wish God didn't have so much faith in me.

 

[MajP]

No way could you get a performance benefit, unles there is a lot more to this than you are explaining.
The query joining the two tables would also have a lot less information and avoid lots of steps.

AFAIK if you were going to really do this there is no easy way. You would have to use ADO where you could build a 3rd disconnected recordset. I do not think DAO supports that. Then you would have to manually populate it. That would be real inefficient, but possible.

 

[DBAMJA]

Thanks MajP.

Maybe I will just have to keep it the way it is.


[flush]

DBAMJA

It is said that God will give you no more than you can handle. I just wish God didn't have so much faith in me.