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