Ooooo - a challenge. Well, then you'll have to use VBA to build the query. The query itself will look as above, but the method for building it will be something like this...
First, you'll need to get a list of the tables. This pages has some explanation about that
http://www.ehow.com/how_6680641_acce...-database.html
You'll end up with something like this:
Code:
SELECT MSysObjects.Name FROM MSysObjects
WHERE MSysObjects.Type = 1 AND MSysObjects.Name Like "SSWPD*";
Then you'll have to pull the column names of the table. That's described pretty fully on this next reference, as a matter of fact, the code above would slip right into the code on this page - http://stackoverflow.com/questions/4...e-in-ms-access
Then you'll have to build your UNION. You'll need to validate that each one of the tables does, in fact, return the same columns in the same order. Might have to sort them by name, or just save the original name order and use them over and over again.
Honestly, if I was trying to do any real work with this Union table, and if it wasn't too huge, I'd probably just create a temp table and append all those items, rather than make a UNION. But, your mileage may vary.