If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Linking Databases
I have mulitple databases (seperate folders on a network) that all collect
the same data - the same database was copied 3x for 3 seperate site locations (due to multiple people entering data and because of the seperate site location - i was told this was better because of the lag time and could interfer with the integrity of the data) I would now like to create a new table that links all 3 databases to one but I continue to read that you cannont link a table to another table that is not within the same database? Is this true (this could explain my difficulty in the linking) And if so- is there a way around it? I am using MS'07 |
#2
|
|||
|
|||
Linking Databases
On Sat, 27 Jun 2009 08:54:01 -0700, anamque
wrote: You cannot link a table to a table period. Rather tables are linked to a database. You can certainly create a new MDB, create a linked table to each of the 3 databases, and then write a UNION query that puts all the records from those 3 tables together as one. select * from table1 union select * from table2 union select * from table3 -Tom. Microsoft Access MVP I have mulitple databases (seperate folders on a network) that all collect the same data - the same database was copied 3x for 3 seperate site locations (due to multiple people entering data and because of the seperate site location - i was told this was better because of the lag time and could interfer with the integrity of the data) I would now like to create a new table that links all 3 databases to one but I continue to read that you cannont link a table to another table that is not within the same database? Is this true (this could explain my difficulty in the linking) And if so- is there a way around it? I am using MS'07 |
#3
|
|||
|
|||
Linking Databases
anamque,
You can't link to a table in the same database but you most certainly and link to tables in another database. You would have a seperate database containing the table you want to link to and then link the other three databases to it. That is standard. I don't know about this lag time slowing data entry down, none of my Clients have ever complained and that is the only way I do it. Have a look at... http://www.members.shaw.ca/AlbertKallal/Articles/split/ http://allenbrowne.com/ser-01.html -- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II http://www.regina-whipp.com/index_files/TipList.htm "anamque" wrote in message ... I have mulitple databases (seperate folders on a network) that all collect the same data - the same database was copied 3x for 3 seperate site locations (due to multiple people entering data and because of the seperate site location - i was told this was better because of the lag time and could interfer with the integrity of the data) I would now like to create a new table that links all 3 databases to one but I continue to read that you cannont link a table to another table that is not within the same database? Is this true (this could explain my difficulty in the linking) And if so- is there a way around it? I am using MS'07 |
#4
|
|||
|
|||
Linking Databases
Hi,
What exactly do you mean by "link"? Are you desiring to establish some kind of referential integrity? If so, you indeed cannot do that between databases. If you are looking merely to combine the data that is of the same type all into one "table" that shows all of the data from all three databases' tables, that you can do, sort of. It would involve linking (see next sentence) the tables of each of the different databases into one database and then writing a union query to combine the records. In Access, when you talk about linking tables, it means that you create a connection in one database to a table in a second database (or file), through which you can access that data in that second database as if it was in the first database. So, if you have a table named "tblData" in each of the three databases, you could create table links to all three, maybe giving the links the names "tblData_DB1", "tblData_DB2", and "tblData_DB3". Then your union query would look like this: select * from tblData_DB1 union all select * from tblData_DB2 union all select * from tblData_DB3; You would then use that query instead of a table name. Note that you most likely will not be able to modify data through that view. Now, if you need to identify the database for each record you could add a constant in each select: select "DB1" as Database_Name, * from tblData_DB1 union all select "DB2" as Database_Name, * from tblData_DB2 union all select "DB3" as Database_Name, * from tblData_DB3; Hope that helps, Clifford Bass "anamque" wrote: I have mulitple databases (seperate folders on a network) that all collect the same data - the same database was copied 3x for 3 seperate site locations (due to multiple people entering data and because of the seperate site location - i was told this was better because of the lag time and could interfer with the integrity of the data) I would now like to create a new table that links all 3 databases to one but I continue to read that you cannont link a table to another table that is not within the same database? Is this true (this could explain my difficulty in the linking) And if so- is there a way around it? I am using MS'07 |
Thread Tools | |
Display Modes | |
|
|