Using one table in multiple databases

[lschuh]

I have many databases in different places. There is one main table that is
used in most of these databases. That means I have to update the table each
time I want to add a record to any one of my databases. I have a mess. What
I would like to do is use the one table and link to all of these other mdb's.
I don't know if I export or I import or I link and provide paths to each one
the multiple databases. Please advise the best way to approach this task.
Thanks

 

[BruceM]

It's the other way around. You link to the table from a database, not to a
database from the table.

You should have your databases split into front end and back end, especially
if they are used by several people. More at these links:
http://www.members.shaw.ca/AlbertKallal/Articles/split/index.htm
http://allenbrowne.com/ser-01.html
http://www.granite.ab.ca/access/splitapp/

In any case, you link from a database to a table in another database. Do
not import or you will have an unmanageable mess on your hands.

What is this table that needs updating whenever you add a record to any
database?

 

[lschuh]

I have a table called employeeadd. This table has all the employee records,
addresses, ssn, etc. Because I need to have maintain medical records I have
several databases which all require the use of the "employeeadd" table.
Currently I am going into each database and adding the same data every time.
I have about 8 or 9 databases and alot more tables requiring the same info.
I know I have a crappy design but there are so many records, at least 5 years
worth that I just go with the flow. No one else has access to my databases
so I don't know anything about front or back ends. I would like to attempt
to straighten out the mess but you have to start someplace. The one table
that is a common link to all the other databases is the "employeeadd" table.
I hope I have answered your question.

 

[BruceM]

First, back up everything. Really. Be sure to do this.

Open one of the databases (I will call it DB1) that need a link to the
Employee table. Click File >> Get External Data >> Link Tables. Navigate
to the table containing the "real" Employee table. Select the table, and
click OK. DB1 will show it in the list of tables, with an arrow next to it
to show it is a linked table. Remove any copied or duplicate Employee
tables.

Note that you cannot enforce referential integrity with linked tables, but
you can establish the same links you may have had with the original Employee
table.