Couldn't update multitable form (3 tables)

[bchi49]

I'm having problem updating a form that queries from 3 tables. When I
update the foreign key it tries to update the PK on the foreign key
table which it shouldn't. I'm using MS Access 2003 connected to SQL
Server 2000.

Here's an example:

Tables: T1, T2, and T3.
T1 links to T2, and T2 links to T3.

When I update the foreign key on T1 that links to T2, MS Access is
trying to update the PK of T2, instead of the foreign key of T1 which
causes data integrity error "cannot update PK on T2". T1 are the main
contents of the form. When the foreign key changes the form should also
be updates with the foreign table information.

Thanks in advance,
Benjamin

[bchi49]

Note, All tables are 1:1 relationship. It is normalized.

[bchi49]

Here's an example:

select T1.*, T2.Field1, T3.Field1
from (T1 inner join T2 on T1.T2ID = T2.ID) inner join T3 on T2.T3ID =
T3.ID

I dont' understand why it would update the T2.ID, when I update T1.T2ID
field. Is this a bug in MS Access? or could I specify which table to
update somewhere?

Thanks,
Benjamin

[Jeremy Wallace]

Do you have cascade updates turned on in the relationship between the
two tables?

Jeremy
--
Jeremy Wallace
http://metrix.fcny.org

[bchi49]

Should the cascade update be implemented on the SQL Server side? I see
the option on MS Access, but the options are disabled. BTW, I'm using
MDB with linked-tables, not ADP.

But it's tring to update T2.ID instead of the foreign key field on
T1.T2ID.

Thanks,
Ben

[bchi49]

Let me clarify on the relationships:
T1 is 1:1 to T2, and T2 is *:1 to T3. There could be cases where T2 is
*:1 to other tables.

[bchi49]

When I take T3 out of the picture it still does the same thing.

[bchi49]

On the SQL Server side, I tried having the relationship:
* With and Without "Enforce relationship for INSERTs and UPDATEs"
* With and without "Cascade Update Related Fields"

Neither one works... MS Access is trying to update the identity column
of the T2, it should update T1.T2ID (foreign key column). It's weird
that it does that because in the select statement it doesn't have
T2.ID, it has the T1.ID (of the T2) which I named T1.T2ID in this
example. I wonder if that's the problem.

Hmm... Here's a more real example:
I have T1.T2ID (Foreign Key), and T2.T2ID (PK). I update T2ID on the
form, the form queries from T1.T2ID only, it doesn't selected from
T2.T2ID. When it updates, it updates T2.T2ID which is the problem. It
should update T1.T2ID. Hmmm... Maybe if I rename the foreign key column
on T1.T2ID to T1.fkT2ID it might work... I'll give that a shot.

[bchi49]

That didn't work... renaming column T1.T2ID to T1.fkT2ID didn't work...
It's still trying to update T2. Here's the error message:

ODBC--update on a linked table 'T2' failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot update
identity column 'T2ID'. (#8102)

It shouldn't update T2.T2ID.... It should update T1.fkT2ID ... that
didn't work....

[bchi49]

Am I missing something here? I created a ADP and connect directly to
the SQL Server, created a view with those queries, and I was able to
update the T1.T2ID without any problem. But... I cannot use ADP yet, I
need to develop under MDB...

[bchi49]

Yes! I got it working in a different way. I created a View, then create
a linked table to the view, choose the view as the datasource for the
form. And fortunately I was able to update the view (3 table join)
without additional triggers such as INSTEAD OF (Insert/Update/Delete).

Please share the information if you have solution to the problem
without using a view.

Thanks!
Benjamin

[David W. Fenton]

bc****@gmail.com wrote in
news:11********************@e56g2000cwe.googlegrou ps.com:

[]

Just a minor point:

Whenever I see a thread that is a cascade of a single person posting
and replying to himself repeatedly (as this thread stands currently
in my newsreader, there are 11 posts in the thread, only 1 of which
is from someone else), I ignore the thread, because it's just too
much trouble to take the cascade of messages and paste them together
into a coherent picture of the problem the question is about.

Don't post until you have all your ducks in a row and know what you
want to ask.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Jeremy Wallace]

Benjamin,

I have to agree with David here. It's _great_ that you're taking the
time to look into this yourself and not just sitting around waiting for
someone to solve your problem. But I will say that I dropped my jaw
when I checked back in here and saw the number of posts that came after
mine (in addition to the ones before it). I'm certainly not offended
(and I didn't get the impression David was), but I do think it's best
for all if you do your digging before posting to CDMA, as you can then
present what you've done in a more coherent way, making it easier for
people to see what your question is, and what avenues you've
explored--the whole goal here is to make it interesting for people to
have a go at your problem.

In the end, it looks like you got it worked out in a way that makes a
lot of sense, which is the real point, so cheers to you on that.

Jeremy
--
Jeremy Wallace
http://metrix.fcny.org

[David W. Fenton]

"Jeremy Wallace" <je************@gmail.com> wrote in
news:11**********************@v46g2000cwv.googlegr oups.com:

I do think it's best
for all if you do your digging before posting to CDMA, as you can
then present what you've done in a more coherent way, making it
easier for people to see what your question is, and what avenues
you've explored--the whole goal here is to make it interesting for
people to have a go at your problem.

I start a lot more questions to CDMA than actually end up being
posted. What usually happens is I encounter a problem that I can't
solve and decide to post about it. In the process of writing the
post explaining my problem, it occurs to me that I haven't tried X
and Y, so I try those, and if they don't work, I put that in the
post. WHile writing about that, it occurs to me that I could try Z,
too, and so I try that.

About half the time, by the time I've written the post and tried the
things that hadn't occurred to me before starting the post, I've
solved the problem. It's only in the cases where I do everything I
can think of and still haven't solved the problem that the question
actually ends up getting posted at all.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[bchi49]

Thanks for the comments. I'll keep that in mind. Actually, I have tried
a lot before I post. I'll keep that in mind next time before I post.
Sorry for the long posts :)

The reason I posted was because I had problems with updating views
before on many other forms that has multiple tables, and I didn't want
to create triggers INSTEAD OF update/insert/delete that would cause
maintenance overhead when table changes. So, I end up converting the
views to stored queries in MS Access and it works for most forms,
except for the problem I posted on this form, and I forgot to try
restoring back to SQL Server View, and I was glad it updated view fine.

But I still don't understand why MS Access is updating the PK instead
of the Foreign key. Is this a bug in MS Access? or lack of feature to
specify which table to update?

Thanks,
Benjamin