Union query from a changing number of databases

[Whizbang]

We have an SQL server here at work that is used to hold customer transactions. Each customer has their own database. I had nothing to do with the design of the databases/server. It is an application provided by a vendor. Each new customer gets a new database with the same structure.

My task is to simply generate some better reports. There are reports for each customer, but there is nothing that totals all the customer transactions up to do company-wide reports. I know how to do a UNION query to combine two similar tables, but I don't know how to do that when the number of tables may change over time, and the tables themselves come from different databases on the same server.

So, get a list of databases, then append the various tables into a single table to be totaled up. Any ideas or resources?

[June7]

You might be able to use VBA to modify the QueryDef of the saved query object. However, the only time I tried to use VBA to work with UNION query failed. If UNION query was anywhere in a sequence of queries, VBA would not open recordset. I had to modify procdure to eliminate UNION query. Modifying QueryDef might be different, maybe VBA won't care that the SQL statement has UNION.

[Whizbang]

Here is a small sql statement I have worked out.

SELECT  'Customer1' as [CustomerName], [Field1], [Field2], [Field3], [Field4], [Field5], [Field6]
FROM [Customer1].[dbo].[TransTable]
UNION ALL
SELECT  'Customer2' as [CustomerName], [Field1], [Field2], [Field3], [Field4], [Field5], [Field6]
FROM [Customer2].[dbo].[TransTable];

This is a passthrough query using the ODBC Connect Str of:

ODBC;Description=MYSERVER;DRIVER=SQL Server;SERVER=MYSERVER;Trusted_Connection=Yes;

I can manually add to this SQL statement for each of the current customers. This will work just fine, except that as new customers come on and old customers leave or go inactive, I will need to update this string. Is there a way I can somehow have the DB name be variable, and somehow use a list of customers to generate the results?

[June7]

Not in a saved Access query object. Again, would need VBA code to modify the QueryDef. The code could refer to control on form as variable for the db name, construct the SQL string, modify the QueryDef.

[orange]

Since all Customer databases have the same structure, why not (create some vba to) import the data to a "TempTable", and design query/queries to do what ever manipulations/ recordsource for Report(s) as necessary?

You say a varyiing number of Cistomers -- how does that work?