Fields from multiple tables

[needmoremoney]

Good morning,

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.

 

[KenReay]

Hi

If the number of Dbs had been small eg say 10 I would have suggested a UNION query might do the trick eg

SELECT CProcessDates.co, CProcessDates.processDate, CProcessDates.status
FROM \\server1\systems\9872.CProcessDates
WHERE (((CProcessDates.status)="Processed")) OR (((CProcessDates.status)="Processed"))
UNION
SELECT CProcessDates.co, CProcessDates.processDate, CProcessDates.status
FROM \\server1\systems\9872.CProcessDates1
WHERE (((CProcessDates.status)="Processed")) OR (((CProcessDates.status)="Processed"))
UNION ....etc
ORDER BY CProcessDates.processDate DESC;

but for a 1000, the statement would be too long I suspect

how about writing some vba Code which first builds a table consisting of the 1000 tables, difficult to give example exactly, since I do not know how the Db names are structured

but something like

Dim l as Long
For l = 1 to 1000
strSQL ="INSERT Into MyTable " & _
"SELECT CProcessDates.co, CProcessDates.processDate, CProcessDates.status
FROM \\server1\systems\9872.CProcessDates" & l & " " & _
"WHERE (((CProcessDates.status)="Processed")) OR (((CProcessDates.status)="Processed"))"
DoCmd.RunSQl strSQL
Next l

then you can run a simple query against MyTable




Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[needmoremoney]

For the VB code above, where can I insert that into and what can I use to run it? I don't know anything about VBA. Can you help explain that to me? Thanks much.

 

[KenReay]

Hi

You could create an empty db, decalare a table (MyTable), with the three fields you want to extract

Create a form, put a button on it, in the onclick event of the button, put the code

If you know nothing of VBA you may strucggle, the code I gave was an outline idea, it may (will) need to be amended to handle the name(S) of you databases, and /or the folder names in which they are stored

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK