Randall-B
asked on
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.
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.
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("TableNam
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.
Dim db as Database
Dim rs as Recordset
Set db = OpenDatabase("Database Path")
Set rs = db.OpenRecordset("TableNam
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.
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
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
Randall-B,
I have developed a solution for you. I tested it and it works. There is some room to refine it. brb
mike
I have developed a solution for you. I tested it and it works. There is some room to refine it. brb
mike
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
* 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
* 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
ASKER
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.
ASKER
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.
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
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
ASKER
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.
ASKER
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
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
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
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
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
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
ASKER
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
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
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