Query to Aggregate Several Tables, & Capable of Updating the Queried Rows in those Tables

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,
etc.  

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.
Randall-BAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick MatthewsCommented:
What you are trying to do is impossible.  It is possible to have a query that returns the data into one recordset
that you could display on a form or in a report, but there is no way to make that recordset updatable.

An alternative: make a form that has a subform for each of the 12 items.  The subforms would allow for
updates, although I realize that your main form will look awfully cluttered.

Sorry,

Patrick
mpmccarthyCommented:
The following code will allow you to open a table as a recordset from a named database.

Dim db as Database
Dim rs as Recordset

    Set db = OpenDatabase("Database Path")
    Set rs = db.OpenRecordset("TableName")

Suggestions:

Create a table with a list of the Database paths as records.

Open the table as a recordset and running through the recordset in a loop pass the record to a function as a parameter.  This function would open the recordset and return a string or variant value.  This is the value in the table in each database.

This value could then be appended to another table in the database.
flavoCommented:
re: http:Q_22024743.html

Randall,

I have to agree with Patrick on this - There's not way to update a UNION query (which in essence is what you are after)

You *could* save the union query to a local temp table, then caputre the Form_BeforeUpdate event, ensure the changes are valid, then run an UPDATE query on the table in which that record originally came from.

Why do you have this all split up to begin with?

Dave
OWASP: Threats Fundamentals

Learn the top ten threats that are present in modern web-application development and how to protect your business from them.

Mike EghtebasDatabase and Application DeveloperCommented:
Randall-B,

I have developed a solution for you.  I tested it and it works.  There is some room to refine it. brb

mike
Mike EghtebasDatabase and Application DeveloperCommented:
In my test, I made two databases named John.mdb and Jeff.mdb each with a table named tbl_John and tbl_Jeff, repectively.  These tables have one table called [Name].

From a third mdb file I have code to run these queries:

SELECT "John" As DatabaseName, Name FROM tbl_John UNION SELECT "Jeff" As DatabaseName, Name FROM  tbl_Jeff;

which produces:

DatabaseName         Name
-----------------        ----------
John                         John
Jeff                          Jeff

There is a second query you can run from the fron-end database to make a temp table:

SELECT qUnion.DatabaseName, qUnion.Name INTO tTemp FROM qUnion;

Now, open table tTemp for edit.

Lastly, run:

Sub UpdateForTheChanges()

CurrentDb.Execute "Update tbl_John, tTemp Set tbl_John.Name = tTemp.Name Where tTemp.DatabaseName ='John'"

CurrentDb.Execute "Update tbl_Jeff, tTemp Set tbl_Jeff.Name = tTemp.Name Where tTemp.DatabaseName ='Jeff'"

End Sub

There is quite bit refinement due.  But first test it mork for you as it did for me.  Btw, we can change the SQLs such that you don't need to likk tbl_John etc. to the front end. If needed could discuss it later.

Mike

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Randall-BAuthor Commented:
 * Mike:  I am looking forward to seeing your solution.  Please be so kind as to post an explanation of how to use it.
  * Dave: the reason these tables are all split up is because each user has a customized version of the program (each user has a .mdb file that is the same except for this one table that contains personalized information about the user). The one-row table (which the user never sees) is what gives each user's version the personlized information.  When a new employee starts, we just make a copy of the .mdb file, input their personalized info into the hidden one-row table, and they're ready to go.  But I need a way to edit users' personalization tables from one administrative screen.  
    * All:  Maybe what I want is nearly impossible to do directly, but I appreciate al the substitutes you have suggested, which might achieve the same end result.  So far, it sounds like Patrick's subform concept might be the easiest substitute.
     But I'm curious to see Mike's solution. Thanks,
Randall
Randall-BAuthor Commented:
Mike: Sorry, I didn't see your 2nd post when I wrote my comments above.  I'll see if I can get your solution to work for me. Thanks.
Randall-BAuthor Commented:
Mike:  Your proposal looks like it would work, but I'm not clear on exactly how to implement all of the SQL and VB code.  Is there any chance you could temporarily post your frontend .mbd file to a web site so I could download it and adapt it?  Thanks.
Mike EghtebasDatabase and Application DeveloperCommented:
Sorry for the delay.

Please open Fronend.mdb and relink the tbl_John and tbl_Jeff from John.mdb and Jeff.mdb, respectively.  Then open Form1 which has one button to start the edit and the other to save after you close the edited table.

Please see:  https://filedb.experts-exchange.com/incoming/ee-stuff/1102-Randall-B.zip

Mike
Randall-BAuthor Commented:
Mike: Thanks for posting the files. Unfortunately, I can't open them in Access 2000. Is there anyway you can save them to 2000 format? Thanks.
Mike EghtebasDatabase and Application DeveloperCommented:
Randall-BAuthor Commented:
Mike:  Thanks. I was able to open and modify your sample Access2000 files to work with my .mdb files. It works nicely and achieves the needed result.

All:  I appreciate all the experts who contributed suggestions.  I believe Mike Eghtebas's solution is the closest to what I was looking for, as well as being easy to implement. Thanks again,
Randall
Mike EghtebasDatabase and Application DeveloperCommented:
Suggestions:

1. Post another question to have following done with one query only:

Sub UpdateForTheChanges()

CurrentDb.Execute "Update tbl_John, tTemp Set tbl_John.Name = tTemp.Name Where tTemp.DatabaseName ='John'"

CurrentDb.Execute "Update tbl_Jeff, tTemp Set tbl_Jeff.Name = tTemp.Name Where tTemp.DatabaseName ='Jeff'"

End Sub
-------------

2. Add a table to your ftontend.mdb:

tblUserName
-----------------
UsertID       DatabaseName          TableName
--------      -----------------           --------------
1                 John.mdb                 tbl_John
.
.
.

Now, using this table, onther yet another question:

Compose the union query and use it in step 1 above.

Mike
Mike EghtebasDatabase and Application DeveloperCommented:
Btw, I may not be able to handle the new quetions.

Otherwise, you need to have the following done 26 times:

CurrentDb.Execute "Update tbl_John, tTemp Set tbl_John.Name = tTemp.Name Where tTemp.DatabaseName ='John'"

But using tblUserName suggested above, this could be done with ease by some experts who are pretty good with SQL coding.

Mike
Randall-BAuthor Commented:
Mike,
   Thanks for the latest suggestions for a couple of new questions.  When I have more time I may pose those questions for use in a future project.  But so far, your earlier solution is workable. Thanks,
Randall
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.