Help Creating Query To Ignore Statement If File Doesn't Exist

[curtb461]

Hello all,
I am working with a rather large database that references 10 other Access files. These files are referenced through linked tables in my database, and I am trying to create a union query with SQL that will select certain columns from all of the linked tables. I have the following code that works if all of the files the tables link to are present:

SELECT StepNo, FailReason, Traceability, TestTeam, Date
FROM CALTR4Fails
UNION ALL
SELECT StepNo, FailReason, Traceability, TestTeam, Date
FROM CALTR6Fails
UNION ALL
SELECT StepNo, FailReason, Traceability, TestTeam, Date
FROM DIAGTR4SysFails
...etc

but if all of the files that link those tables aren't present in the source folder, my query won't run. Sometimes not all of the those files are needed because they will just take up valuable folder space.
Is there a way to check if the file that links to a certain table exists in the source folder and, if not, ignore the SELECT statement and move to the next one? I'm afraid I'm overthinking this problem.
Thank you in advance, and let me know if anymore information is need.

 

[spikepl]

So make the query on the fly, depending on what data you have available.

 

[curtb461]

So make the query on the fly, depending on what data you have available.

For as much as I would like to, I'm trying to make this database as automatic as possible so all someone has to do is open the file and see the info they want.

 

[spikepl]

Aha. And in what way does making the query on the fly contravene your intentions?

 

[curtb461]

I guess over the grand scale of my project it doesn't. Thank you for the new idea.

 

[plog]

How do those files get there and get removed? Do you have control over this source directory? If so, force those files and tables to be there by having empty tables with the appropriate names be there.

If you don't have control over that directory and can't force them, I'd eliminate the the Union query and create a temporary table that accomplishes the same thing. Essentially you would create an APPEND query for every table in the UNION query and move the data from the table to the temporary table. You could use VBA to check to make sure the table exists before running the APPEND. Once done appending all your tables the temporary table now holds all the data the UNION query would and you can use that instead.

 

[curtb461]

create an APPEND query for every table in the UNION query

So you're suggesting completely eliminating the UNION query and creating separate APPEND queries for each of these tables, right?
I didn't consider that. I'll give that a shot, thank you.

 

[plog]

Correct. You need to get your data into an object that you can rely on. Once that's done, you can build the rest of your database on that reliable object; rebuilding that reliable object when necessary.

 

[curtb461]

That seems to work great. Still running a few different scenarios but it seems to be doing the job. Thank you.