Linked Tables - Adding new fields in Linked Table

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.
 
T

Tim Ferguson

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.

Quite right: you have to open the file that actually contains the table.
BUT the table is also
deleted. ANY thoughts on this?)

How do you know: have you looked in the other file to see if the table is
still there? If it has been deleted, then something very bad is happening;
but I very much doubt that is the case.
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.

What you have here is the need to make some design decisions. What is your
database meant to do? What are the parameters, in terms of numbers of
users, security requirements, support and maintenance resources, and so on?
You really have to manage the changes to the project; simply chipping in
the odd field here and there is likely to lead to disaster even if you have
superlative documentation, and it is inevitable if you don't. Everthing you
have mentioned in your post is do-able, but the best way depends on a load
of other question you have not answered yet.

Best wishes


Tim F







If I delete
 
M

Mark Mesarch

Tim,
I find you answer pretty useless and with out any
substance. And I have found when people make a reply to a
question then it rarely is looked at by anyone else.
Please from now on if you want to be helpful, actually
give some answers instead of pontificating.
m.
 
A

Adrian Jansen

You will find my comment even less useful then.

I thought Tim's reply was useful and to the point.

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control
 
T

TC

Er, that is not a good way to get future help!

If you think that an answer is wrong or misguided, just ignore it & move on.

In any case, he *did* answer your question :)


TC
 
T

Tim Ferguson

I find you answer pretty useless and with out any
substance. And I have found when people make a reply to a
question then it rarely is looked at by anyone else.
Please from now on if you want to be helpful, actually
give some answers instead of pontificating.

You have to consider what kind of questions are best asked and answered in
these newsgroups. There are very specific ones -"how do I address a control
by having its name in a variable?"- and very general ones -"how do I
normalise this data model?". Experience suggests that eighty line questions
have not been well thought out before, and that the poster has stuffed in
many unneeded details rather than attempting to solve it first.

That said, I did recognise the question about deleting tables and tabledef
links, and offered a very focussed and specific response. Have you checked
out the advice?

On top of that, I also recognised that the scenario you described is
heading rapidly for a disaster, and suggested that you pull back now while
you may still have a manageable mess and before it turns into an
unmanageable one. It's often easier to spot a tangle from outside than
inside, and we have all been involved in projects that have proliferated
out of control without realising it. You are not the first and you will not
be the last. You are of course quite entitled to shut your eyes and pretend
it is not happening. But you do not have to come back here and snap at us.

I hope things work out for your system.

Tim F
 
T

TC

Tim Ferguson said:
You have to consider what kind of questions are best asked and answered in
these newsgroups. There are very specific ones -"how do I address a control
by having its name in a variable?"- and very general ones -"how do I
normalise this data model?". Experience suggests that eighty line questions
have not been well thought out before, and that the poster has stuffed in
many unneeded details rather than attempting to solve it first.

That said, I did recognise the question about deleting tables and tabledef
links, and offered a very focussed and specific response. Have you checked
out the advice?

On top of that, I also recognised that the scenario you described is
heading rapidly for a disaster, and suggested that you pull back now while
you may still have a manageable mess and before it turns into an
unmanageable one. It's often easier to spot a tangle from outside than
inside, and we have all been involved in projects that have proliferated
out of control without realising it. You are not the first and you will not
be the last. You are of course quite entitled to shut your eyes and pretend
it is not happening. But you do not have to come back here and snap at us.

I hope things work out for your system.

Tim F


mesarch:
mzärk, ms-, mzärk, -särk
"Originating in a moderately moist habitat"
mes(o)– + Greek arkh, beginning.

HTH,
TC
 
M

Mark Mesarch

I address the one response that actually address a
specific point. DId I check to see if the table that I
tried to link to disappeared from the original database
when I tried to remove the link from the second database.

I was having the table removed from the secondbase instead
of only the link being broken.

You asked it was being removed from the original database.

I did check that and it still there.
I however thought that question was too simple of a
question and well rather silly. If the original table in
the original database had disappeared that would have been
a disaster. And since I keep trying the linking example
several times, it never occured to me that it would
disappear. If it did there would have been a major flaw
in ACCESSS.

As for the other stuff....well lets just say that your
concerns are noted.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top