Linking Databases

[anamque]

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

 

[Tom van Stiphout]

On Sat, 27 Jun 2009 08:54:01 -0700, anamque

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

 

[Gina Whipp]

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

 

[Clifford Bass]

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