Read-only share/link to single table?

[aemAndy]

I see a lot of threads out there about how to split a database, or installing
user-level security....

What I'd like to do is the following -

There's a functional area that keeps contact information up to date for a
certain class of customers, let's call them "Hydraulic Manufacturer Contacts".

That's information that isn't primary to another area, but they've kept that
as additional information on their own table in their own database.
Obviously, this is not ideal and creates data integrity issues as two
separate areas try to maintain the same information on different tables.

What I'd like to do is get rid of "Group 2's" table on their database, and
link it to "Group 1's" table. However, "Group 1", the owners of this data,
are uneasy about someone else being able to mess with their table.

Is there a way to link to that table, and use it as a read-only table in
"Group 2's" database? How should I set up or tweak that link to make this
happen?

Sorry if this is something that's obvious.... I did try and find something
similar out there.

 

[Tim Ferguson]

Is there a way to link to that table, and use it as a read-only table
in "Group 2's" database? How should I set up or tweak that link to
make this happen?

If Group 2 is able to delete records from the table, then there is no way
to protect the Group 1 mdb records from being orphaned. You can always
run exception reports on, for example, Group 1 records without Group 2
parents and enable some kind of error recovery, but it'll always be a
step behind.

If you can put up with that, then there are ways of keeping Group 1 not
too far behind. The simplest is simply importing a copy of the relevant
Group 2 table into the Group 1 database on a regular basis -- weekly,
daily, hourly etc as required. This means that the Group 2 data would be
completely isolated from any changes made "down stream". It is probably
possible to create a "snapshot" type of querydef that reads the data in
the Group 2 database and exposes that to the Group 1 database but which
would not allow any updating of the records.

The final solution would be to install user level security and protect
all the data behind RWOP queries (Google for that if you need further
information). You can give the Group 1 people access to a query that only
exposes the fields and records that they are entitled to. The main
difficulties here are actually the human ones about who controls the data
and who will be allowed the privilege of accessing them. That's much
harder than database design!!

Hope that helps


Tim F

 

[Pat Hartman\(MVP\)]

This is a typical problem in a large company. The end result is dozens of
conflicting copies of what should be the exact same data. The correct
solution is to either modify group1's table to add the additional attributes
for group2 or to add a new table to group1's database to hold group2's data
but be related to the group1 table in a 1-1 or 1-m relationship so that
referential integrity can be enforced. The separate table solution will
probably be the most palatable but it is imperative that the two tables be
in the group1 database so that RI can be enforced. Then group2's database
can link to the table with their data and you can lookup data from the
shared table via DLookup()s which will keep the group1 data from being
updated via the group2 database.

However, if group2 ever needs to update the common data, you will need to
escalate the problem so that hegemony of the data can be shared.