Hi AG,
Access 2000 is not supported by Microsoft now. Could you please first
check
if this issue can be reproduced in Access 2003 or later version? If so, I
recommend that you mail me (changliw_at_microsoft_dot_com) a sample
database so that I can reproduce your issue at my side. Per my test, if I
created a linked table from an updatable view in SQL Server 2005, I could
not insert/update any row in Access 2007, though I could insert new rows
in
SSMS (SQL Server 2005 Management Studio).
My test script is as following:
============================================================================
==
CREATE TABLE A1
(ID int not null primary key,
NAME nvarchar(20) not null,
CreateDate datetime null)
CREATE TABLE A2
(ID int not null identity(1,1) primary key,
A1_ID int not null,
Qty int not null,
A3_ID int not null,
CreateDate datetime)
CREATE TABLE A3
(ID int not null primary key,
NAME nvarchar(20) not null
)
ALTER VIEW dbo.v_A1A2A3
WITH SCHEMABINDING
AS
SELECT A1.ID AS A1ID, A1.[NAME] AS A1Name,A1.[CreateDate],A2.ID As A2ID,
A2.Qty,A3.ID As A3ID, A3.Name as A3Name
FROM dbo.A1 JOIN dbo.A2 ON A1.ID=A2.A1_ID JOIN dbo.A3 ON A2.A3_ID=A3.ID
CREATE UNIQUE CLUSTERED INDEX IDX_vA1A2A3 ON dbo.v_A1A2A3(A2ID)
create trigger trg_v_A1A2A3
ON v_A1A2A3
INSTEAD OF INSERT, UPDATE
AS
DECLARE @rc as INT
SET @rc = @@rowcount
IF @rc = 0
RETURN;
IF EXISTS(SELECT * FROM inserted)
BEGIN
IF EXISTS(SELECT * FROM deleted)
BEGIN
UPDATE A1 SET [NAME]=deleted.A1NAME FROM deleted WHERE A1.ID=deleted.A1ID
UPDATE A2 SET Qty = deleted.Qty,CreateDate=getdate() FROM deleted WHERE
A2.ID=deleted.A2ID
UPDATE A3 SET [NAME]= deleted.[A3NAME] FROM deleted WHERE
A3.ID=deleted.A3ID
END
ELSE
BEGIN
IF NOT EXISTS(SELECT * FROM A1, inserted WHERE A1.ID=inserted.A1ID)
BEGIN
INSERT INTO A1(ID,[Name],CREATEDATE) SELECT A1ID,A1NAME,getdate() FROM
inserted;
END
IF NOT EXISTS(SELECT * FROM A3, inserted WHERE A3.ID=inserted.A3ID)
BEGIN
INSERT INTO A3(ID,[Name]) SELECT A3ID,A3NAME FROM inserted;
END
INSERT INTO A2(A1_ID,Qty,A3_ID,CreateDate) SELECT A1ID,QTY,A3ID,GETDATE()
FROM inserted
END
END
INSERT INTO v_A1A2A3 VALUES(2,'H2',getdate(),2,20,1,'A3.1')
===============================================================
Look forward to your response. If you have any other questions or
concerns,
please feel free to let me know.
Best regards,
Charles Wang
Microsoft Online Community Support
===========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: (e-mail address removed).
===========================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for
non-urgent issues where an initial response from the community
or a Microsoft Support Engineer within 1 business day is acceptable.
Please note that each follow up response may take approximately
2 business days as the support professional working with you may
need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
============================================================
This posting is provided "AS IS" with no warranties, and confers no
rights.
=========================================================
