I have 12 Access .mdb files, each containing a one-row table with unique information about the user of that particular copy of the database front-end (this one-record table has the effect of customizing that user's copy of the application).
I want to make an administrative .mdb that would show all the data from the linked tables in those user files, and aggregate it into one query that looks like a datasheet and would be editable/updateable (any edits made in the datasheet--or continuous form--based on this query, would pass the updates to the original linked tables in those outside .mdb files).
For example, in JohnDoe.mdb, a UserInfo table says:
Name: John Doe EmployeeID: 1 Position: President
The UserInfo table in MaryJones.mdb says:
Name: Mary Jones EmployeeID: 2 Position: Vice-President,
The UserInfo table in BillSmith.mdb says:
Name: Bill Smith EmployeeID: 3 Position: Secretary/Treasurer,
Now I need a query that can combine all of those tables from different .mdb files into one updateable datasheet, like this:
Name EmployeeID Position
John Doe 1 President
Mary Jones 2 Vice-President
Bill Smith 3 Secretary/Treasurer
I have linked the UserInfo tables from all those files. But when I used the query wizard and tried to select all the fields from all the linked tables, an error says I can't connect those sources. Now, how do I make a query to accomplish this? Thanks.