pull fields from multiple db

[needmoremoney]

Hello, I'm posting this again because I think I posted it in the wrong forum earlier. This should have been a SQL question.

I need some help with this.

I have over 1,000 access 97 databases. I need to to pull only data from three field in one table from each database.
How can I do this without writing a sql statement for each db?

What I have now:
SELECT CProcessDates.co, CProcessDates.processDate, CProcessDates.status
FROM \\server1\systems\9872.CProcessDates
WHERE (((CProcessDates.status)="Processed")) OR (((CProcessDates.status)="Processed"))
ORDER BY CProcessDates.processDate DESC;

Any ideas, let me know. Thanks much.

 

[druer]

If there is some type of pattern to the naming convention you can use dynamic sql that you can construct inside of a loop.
However if your file names are all "randomish" then I think you would need to construct a table that contains the filenames and you'll have to manually input them all, and then do a select from that table to pull the filenames out to a variable and then use it in your dynamic sql statement.

exec ('SELECT fields,you,want FROM \\server1\systems\'+@dbName+'.CProcessDates
WHERE criteria')

* I have only abbreviated what you would need to do to demonstrate the dynamic sql. Inside your script you wouldn't be able to touch the results from the EXEC command. You would need to have the results inserted into some kind of table that you could access outside the EXEC itself. The key is looking up EXEC in T-SQL online to understand what you can do with dynamic sql.