I have two pass through queries coming from two different databases. The data structure of the databases are identical and the layout of both queries are similar. How do I combine the results of the two queries into one table?
I do understand that this should be some form of a UNION. However, in MS Access I only know how to union two local tables. So a potential solution would be to first convert the result of the respective pass through queries as local tables using a macro and then doing an union from there. However, this being my first time working with pass through queries, I am not even sure how to convert the result of a pass through query into a local table. I am more used to working with standard linked tables. I am also not sure if this solution will be the most elegant.
Any assistance will be greatly appreciated.
AFAIK, once you saved your 2 PTQ, you can write a union just like if they were local tables. However the performance will probably be terrible, just like with any heterogeneous data sources.
Depending on the use case (specially if need to read that union many times), you might rather:
1. build (or empty) a local table, or create it using a 'make table query'
2. append the data from your first PTQ into the local table
3. append the data from second first PTQ into the local table