Union All Tables that exist If a table is missing Union All that are there

[Arnold]

Hello all.



Currently I have a process that calls for 3 excel files that need to be merged and then used in a query.

I have them linked in access and create a union all query to combine and work their values as needed.

All this is fine until I am missing 1 or 2 of these excel files (they are generated automatically by a scheduled process).

Here the Union query doesn't run since it doesn't find the file (table).

How would I be able to write the sql in the Union query to use only those tables that exist?

[CJ_London]

by building it dynamically in vba

check file exists - if it does add to union query

[Arnold]

Thank you.

May I ask you to kindly guide me as the exact vba

This is my sql for the union.

SELECT output1.ID, output1.[order-id], output1.OrderNumber, output1.qnty, output1.[recipient-name], output1.[ship-address-1], output1.[ship-address-2], output1.[ship-city], output1.[ship-state], output1.[ship-postal-code], output1.phone, output1.SubTotal, output1.Shipping, output1.Tax,
FROM output1
union all
SELECT output2.ID, output2.[order-id], output2.OrderNumber, output2.qnty, output2.[recipient-name], output2.[ship-address-1], output2.[ship-address-2], output2.[ship-city], output2.[ship-state], output2.[ship-postal-code], output2.phone, output2.SubTotal, output2.Shipping, output2.Tax,
FROM output2
UNION ALL
SELECT output3.ID, output3.[order-id], output3.OrderNumber, output3.qnty, output3.[recipient-name], output3.[ship-address-1], output3.[ship-address-2], output3.[ship-city], output3.[ship-state], output3.[ship-postal-code], output3.phone, output3.SubTotal, output3.Shipping, output3.Tax,
FROM output3;

[CJ_London]

Sorry, no can do but look to the bottom of this thread and you will find links to threads which may help

Also google 'vba refreshlink' - it will generate an error if the underlying file is not there. And there is plenty out there about how to write vba code

I can tell you your union query won't work as written even if the three tables are properly linked - this makes me think that there is more to this than you are suggesting so any help will suffer from 'mission creep' which I don't have time for.

Good luck with your project

[June7]

One reason the query won't work is the comma preceding FROM in each SELECT statement. Suggest you calculate a field in each SELECT statement that identifies the source:

SELECT "WS1" AS Source
...
SELECT "WS2"
...
SELECT "WS3"

For verifying if file exists, explore the Dir() function.

Many examples of code that modify a query object, explore QueryDefs.