M
Mark Mesarch
Here is a simple example of how I have some databases set
up.
testmain.mdb is the main database with the Master
Personnel table.
Testfirstlink.mdb is the database that includes a faculty
table and a linked table of the Master Personnel table
from testmain.mdb The faculty table uses the Master
Personnel table via a query qrysecondfirstname to pick
faculty members.
We created separate databases here because we thought we
wanted to have some security for the Master Personnel
table. And also that there will be other groups needing
to use the Master Personnel table information like the
computer support staff and the undergraduate committee.
The faculty table contains information about their type of
appointments and interests.
Now another person wants to use our list of faculty to
track information about what classes they teach. We are
not interested in this information and do not want it in
our database. So is there a way for this new request to
use our faculty table as the basis for their list of
faculty?
It is easy to do this if the faculty list is considered
static. The new person can Import the faculty table and
go from there.
But we want to make it dynamic. SO if we add a new
faculty member to our faculty table, the other user will
see this name added to their table. Their information
might not be added for the new person, but at least their
table will contain a new record with the new name.
My attempt at this is database testseconduse.mdb First
after reading the ACCESS manual, I thought I could link to
the faculty table in the third database. In his linked
faculty table I would delete the fields I did not need and
add new ones that were needed. But I get a message that
says I cannot save the changes on a linked file. (Another
odd thing is I tried just to UNLINK the faculty table in
testseconduse by pressing the delete key. I get the
expect message saying that the link will just be removed,
but the table will remain. BUT the table is also
deleted. ANY thoughts on this?)
MY second try was to start by importing the faculty table
into the testseconduse database and modifying it to meet
my needs. I also linked the faculty table into the
testseconduse database. Then I created a query
(qrycompare) between the two faculty tables with the join
forced to show all the values from the LINKED faculty
table and the common records from the local faculty
table. This actually works, partially. If I add a new
faculty name from the mastertable into the faculty table
in testfirstlink database, the new name shows up in the
faculty table in testseconduse with empty fields for the
new fields. When I try to add data to those empty fields
the person's name goes blank. I can reselect it and add
the new fields data. This information gets entered into
the faculty table in the testseconduse link I had hoped.
However, I do not like losing the name when I start typing
in the data. Also when I delete a faculty name from the
testfirstlink faculty table, that name will not show up in
the qrycompare, BUT it will still be in the faculty table
in testseconduse which is not terrible, but not really
right either.
Basically I am want to let some use the information in one
of my tables to the basis of their table for all the
records, but they will have their own fields. If I delete
or add a record to my table I want their table to reflect
this too.
Any thoughts?
I can email you the examples of the 3 databases if this
helps.
up.
testmain.mdb is the main database with the Master
Personnel table.
Testfirstlink.mdb is the database that includes a faculty
table and a linked table of the Master Personnel table
from testmain.mdb The faculty table uses the Master
Personnel table via a query qrysecondfirstname to pick
faculty members.
We created separate databases here because we thought we
wanted to have some security for the Master Personnel
table. And also that there will be other groups needing
to use the Master Personnel table information like the
computer support staff and the undergraduate committee.
The faculty table contains information about their type of
appointments and interests.
Now another person wants to use our list of faculty to
track information about what classes they teach. We are
not interested in this information and do not want it in
our database. So is there a way for this new request to
use our faculty table as the basis for their list of
faculty?
It is easy to do this if the faculty list is considered
static. The new person can Import the faculty table and
go from there.
But we want to make it dynamic. SO if we add a new
faculty member to our faculty table, the other user will
see this name added to their table. Their information
might not be added for the new person, but at least their
table will contain a new record with the new name.
My attempt at this is database testseconduse.mdb First
after reading the ACCESS manual, I thought I could link to
the faculty table in the third database. In his linked
faculty table I would delete the fields I did not need and
add new ones that were needed. But I get a message that
says I cannot save the changes on a linked file. (Another
odd thing is I tried just to UNLINK the faculty table in
testseconduse by pressing the delete key. I get the
expect message saying that the link will just be removed,
but the table will remain. BUT the table is also
deleted. ANY thoughts on this?)
MY second try was to start by importing the faculty table
into the testseconduse database and modifying it to meet
my needs. I also linked the faculty table into the
testseconduse database. Then I created a query
(qrycompare) between the two faculty tables with the join
forced to show all the values from the LINKED faculty
table and the common records from the local faculty
table. This actually works, partially. If I add a new
faculty name from the mastertable into the faculty table
in testfirstlink database, the new name shows up in the
faculty table in testseconduse with empty fields for the
new fields. When I try to add data to those empty fields
the person's name goes blank. I can reselect it and add
the new fields data. This information gets entered into
the faculty table in the testseconduse link I had hoped.
However, I do not like losing the name when I start typing
in the data. Also when I delete a faculty name from the
testfirstlink faculty table, that name will not show up in
the qrycompare, BUT it will still be in the faculty table
in testseconduse which is not terrible, but not really
right either.
Basically I am want to let some use the information in one
of my tables to the basis of their table for all the
records, but they will have their own fields. If I delete
or add a record to my table I want their table to reflect
this too.
Any thoughts?
I can email you the examples of the 3 databases if this
helps.