Queries using various databases

[roemerle]

Hello together,

I am very new to access (opened my first db only this week), learned the basic things and now I am struggling with my first problem I cannot solve with the help of google

Following situation:

I have roughly 60 databases from monthly accounting closings of the last 5 years. They have all the same name and structure, and are simply in different folders.

I have created a query which I use in one of those databases. What I would need is to apply this query automatically on all the 60 databases, one after the other. The results should be ideally all together in one table in the end (in a main database), but split by database, i.e. split by closing month. An additional challenge is that within each database I do not have the date of the corresponding month, but this is data I need in the final table.

So ideally I would create a table with the paths to the different databases, in a second column I have the corresponding date which I would like to use as a parameter for the query.

I found things like pass through queries, but this seems to be quite some effort if one has that many databases. And I would like to be a bit flexible here, if that is possible.
I thought maybe such a access macro could help, but for me it doesn't look like that

So my question would be if someone could help me a bit in order to solve this problem? This would be really great!!



Many thanks in advance!

roemerle

[June7]

Why is there a separate db for each month? Why not one db?

What is the query? What do you mean by 'apply' the query?

Can have links to tables and use those links like regular tables.

Combining records from multiple tables can be done with UNION query. However, there is a limit of 50 lines in a UNION. So you would have to do two UNIONs then UNION the two UNIONs. Never tried that.

Otherwise, need to copy records from the multiple tables into one table. This can be done with INSERT SELECT sql action. VBA code can automate the repetitive process.

[roemerle]

Wow.. that was quick

- there is one db for every month because this is only a data extraction from a big administration system. Each db has already 1.5 GB, so it would not be possible to combine this in one db
- it is a simple selection query, and by "apply" I simply mean "run" the query
- using links is also something I have seen, but I hoped that I can avoid to create 60 links
- I have seen the UNION query option as well, but I guess then I won't have the information which month belongs to which database
- copying together everything is not an option as the databases are too big. My colleagues told me that 2 GB is more or less the maximum size

[June7]

Select queries do not need to be 'run'. Just use them as RecordSource for form or report or within another query.

Why the aversion to creating 60 links? It's so simple. I know it's 60 actions but once done, it's done.

The year and month values can be constructed fields in UNION. For some reason I cannot type UNION in the example query. Replace ______ with UNION.

SELECT field1, field2, 2000 AS Yr, "Jan" AS Mo FROM 2000JanTable
______ SELECT field1, field2, 2000, "Feb" FROM 2000FebTable
....
______ SELECT field1, field2, 2014, "Dec" FROM 2014DecTable;

True, 2GB is max for Access database. Sometimes running Compact & Repair can reduce the file size, but probably not enough in this case.

[roemerle]

Again your answer is so fast!!!

Thanks a lot for the explanations. I will try this tomorrow morning.

Probably I simply did not find the right words when I said "run the query". I guess we mean in the end the same

Thanks again!