Join 2 recordsets (append 2nd to 1st)

[smandoli9]

Access 97. DAO. I have two large snapshot recordsets based on complex SQL queries. They are fairly large {and poorly indexed, LittleSmudge and Steve101!) -- say, six fields by 10,000 records.

They have the same approximate data structure; one is from the current datastore, the other from an archive.

I want one recordset out of the two. I have a way to do it: Make one recordset based on a UNION SQL query. However this is (1) dense and ugly to work, and (2) slow to run.

Can I open the two recordsets using DAO (or ADO, I'm somewhat open for that) and then "UNIONize" them in VBA to make one recordset of 20,000 records?

In DOS commands for text files, ha ha, this would be COPY [rs1]+[rs2] [rs3]. Just to belabor the point.

 

[RottPaws]

My first thought is just a brainstorm thought.....never actually tried it . . . .

Could you do an append query with the 2nd recordset as the source and the 1st as the destination???

My other thought (which is very ugly and will probably take come time w/ 10k records) is to run a loop through the 2nd recordset and add a new record to the 1st recordset in each pass . . .

Hopefully someone will have a better suggestion that than, but if all else fails . . . . _________
Rott Paws

...It's not a bug. It's an undocumented feature.

 

[smandoli9]

Rott Paws:
Idea 1: No, you can't treat DAO recordsets as tables to be manipulated in SQL strings. At least I don't thing so.
Idea 2: Simplicity itself. In an awful way. I'd stick with the Union query ...
Thanks for your input.

 

[RottPaws]

That's what I figured . . . .

Hey!!!

Here's another idea......

Instead of recordsets, make your first query a make table query and your 2nd an append query. Then you will have the data all in one table local to your access db and you could open that up in a recordset.

You could delete the temp table at the end, or when you run the same procedure in the future, the make table query will automatically delete the old one . . . .

Will that work for you? It's still not very pretty, but it might be effective . . . . _________
Rott Paws

...It's not a bug. It's an undocumented feature.