Populate a table in one database from a table in another database

[dhstein]

We have a database that includes a product table. This has all information
about the products including costs and the database has many functions. We'd
like to provide a way of allowing the customer service people to keep some
product notes about the products without being able to see all the other
fields. In addition, they don't need - and shouldn't have - all the other
functions. I was thinking about providing a totally separate database with
just the product information and a comment field. But I'd like the ability
to keep the database in synch with the master so that when new products are
added the customer service people will have those items also. So two
questions:

1) Is my approach the right one or is there a better way.

2) If this is the right approach, how could I set it up so the products are
in synch?

Thanks for any help on this

 

[Ken Sheridan]

Having two separate products tables is not a very satisfactory solution as,
while you can include code to update the reduced table from the main products
table in the module of a form bound to the products table and vice versa,
this does not preclude the possibility of the tables being updated in some
other way and thus getting out of sync.

A better solution would be to split the products table into two related
tables, one to hold the data you want to allow the customer services staff to
access, the other holding the rest of the data. Referential integrity should
be enforced of course. If the customer services staff need to add multiple
notes per product then the relationship would be one-to-many from the latter
to the former; if they can add only one note per product the relationship
would be one-to-one from the latter to the former, i.e. the primary key of
the reduced table would also be a foreign key referencing the primary key of
the main products table. In the latter scenario if the primary key of the
main table is an autonumber, that of the reduced table must not be of course.

The customer services staff could then be provided with a front end which
links to the reduced table only, thus enabling them to view and edit rows in
it, but not insert new products.

The other staff would add or edit products data via a form based on a query
which joins the two tables.

That's the simplest scenario, but assuming the customer services staff do
need to add multiple notes per product you'll really need to split the
products table into three tables: one to hold the data not available to the
customer services staff, the second, related one-to-one to the first, to hold
the distinct product data available to the customer services staff (product
name, etc); the third, related many-to-one to the second, to hold the
comments data. The customer services staff's front end would then include
links to the second and third tables, but not the first.

If you are using a pre-2007 version of Access and have implemented user and
group security, you could of course control the degree of access available to
the customer services staff by giving them access to the data only via a
query WITH OWNER ACCESS OPTION while not allowing them any permissions to the
base table itself.

Ken Sheridan
Stafford, England

 

[dhstein]

Ken,

Thanks for your very thorough response. Your solution is absolutely
correct. However, the company is most interested in a fast solution with
minimum development and just a basic tool for customer service. So splitting
the table may not be the fastest solution, although I agree it is the
"correct" solution. In the "quick and dirty" category, I would still want to
use a separate database. At this point, I will probably just export a
current version of the product table and reduce it to the few fields I need.
If necessary, once a month I can bring in new products.

 

[BruceM]

Although splitting is the best solution, you can link to a table in an
unsplit database. That may be a better use of time than to make a new table
once a month. You can create a query based on the linked table.

 

[Ken Sheridan]

'Splitting' in this context refers to decomposing the products table, not to
splitting the database into front and back ends, which I imagine is already
the case. Creating a link to the products table in a separate front end for
use by the customer services staff would not serve the OP's purpose as they
need them to have access to a subset of the columns from the products table
only. Linking to the table as its currently set up would expose all columns
to those users so would be no advantage over the current situation.

Ken Sheridan
Stafford, England

 

[BruceM]

My mistake, then. I often link to tables in other databases, but give the
user an interface that limits what can be done. However, I have user-level
security in place for these applications, which limits permissions to the
various objects, and probably creates a very different scenario than without
ULS. I would have thought a locked main form based on the Products table,
with an unlocked subform for entering data into a related Comments table,
would accomplish what the OP needs, but it seems my reasoning has left out
something.

 

[dhstein]

Ken and Bruce,

Thanks for the information and advice. Not sure how we'll proceed yet, but
you've given me some good information.