converging mdb files

[98donem]

We have an application that the computer is creating a new mdb file
everyday. All the mdb files have the same tables. I would like to
converge these files to a master db file for data analysis. My plan
was to link the tables, use the union query and then export the data
to exel. But there are over 100 tables is there a way to automate
this? Maybe a batch table link program or code or a macro. I am
using access 2000. Thanks in advance.

Best regards

Zeki

 

[John W. Vinson]

We have an application that the computer is creating a new mdb file
everyday.

owwwww.....

That's painful.

All the mdb files have the same tables. I would like to
converge these files to a master db file for data analysis. My plan
was to link the tables, use the union query and then export the data
to exel. But there are over 100 tables is there a way to automate
this? Maybe a batch table link program or code or a macro. I am
using access 2000. Thanks in advance.

One could write VBA code to do this; the code would be fairly easy if
tedious, but would depend on the structure of the tables, etc. A UNION
query with over 100 tables is not going to work (it'll exceed the 64K
"Query Too Complex" error limit unless the queries are tiny). Some
sort of loop, perhaps using the Dir() function to find all the
databases, would seem to be more promising.

It can be done, in other words, but will probably be a bit of work.

John W. Vinson [MVP]