Query several databases to UPDATE one

[Kelrizzo]

I've been searching the forums but haven't found a specific answer to my question. It could just be ignorance on my part.

I have 14 databases, each with tables that have identical fields. All the tables have a unique employed identification number acting as the primary key, there will be no duplicate key found between any of the databases.

It was necessary to have separate databases so that workers on the same LAN could update their own particular databases at one time.

I want to bring all this data together into one database so I can extract meaningful data about the project as a whole. I've been trying to figure out how to do this for several days, and I'm just spinning my wheels.



Mike

[pbaldy]

It was necessary to have separate databases so that workers on the same LAN could update their own particular databases at one time.

The problem you're having illustrates why this likely shouldn't have been done. There are ways to let users update their own data in a single database. At this point you can create a database that links to all the other databases, and create a UNION query to draw the information from all of them.

[Kelrizzo]

Aye, I played the back-end/front-end game for a while, but it just wasn't working. People could not save while another person was in the database.

I did try to link the databases, but when I executed it to append everything into one database, the thing ended up being over 500 MB in size and it was still compiling. Obviously I did something wrong lol

[Kelrizzo]

This is getting nuts.

I've linked all the tables under one database. The append query is what I want to do, but using that I can only make use of one table, when I try to do more than one Access wants a relationship, but there are no relationships.

Every database represents a unit with military members at it and their status in a project. There are 14 units. Each record has a number of fields, but the only ones I care about are the Department Name, ID number, Name, and Status.

Adding all 14 units to the Query in Design View and moving the same 4 fields from each linked table into the query results in a infinite loop of some kind where every table repeats it's data ad naseum.

I can append one table into my newly created table (Appended_Tables), but of course now I have 13 other tables which I can't do anything with.

I've tried to code a INSERT INTO SQL statement in SQL view, but that's not working. I even attempted a code module, which was fun, but hooray for macro disabling, I can't determine if it will work or not becaue my workstation is disabling macros.

It seems that it should be a simple process to take identical tables (even if they are linked tables) and append the data from each table, one on top of the other. I'm not that smart though, I just steal what other people have done.

Anyone have any advice for the frazzled?

[pbaldy]

Like I said, a UNION query:

SELECT Field1, Field2
FROM Table1
UNION ALL
SELECT Field1, Field2
FROM Table2
UNION ALL
...

A UNION query can only be created/maintained in SQL view by the way. If this is a one-time thing, you should also be able to run an append query for each table into a single table, providing the keys are different, which you said they are.