Form Bound to SQL Indexed View

Using Access 2000.

I have a form that opens in datasheet view and is bound to a SQL Server 2005
indexed view.
The view contains several tables and utilizes an 'Instead Of' trigger to
handle inserts and updates in order to apply the data to the correct tables.

The trigger works fine for updates and inserts.

The form works fine for updates.

However, after userting a new record, instead of the new row displaying the
data, it shows #deleted.
In order to show the new record, I need to requery the form.

I am guessing that Access has no way to identify the new row.

Is there a way to avoid the requery?

--

AG
Email: discussATadhdataDOTcom

I don't know SQL Server but you can requery with

AfterUpdate
me.requery in an event procedure

or run a macro that has the requery command.

Bonnie

http://www.dataplus-svc.com

AG wrote:
Using Access 2000.

I have a form that opens in datasheet view and is bound to a SQL Server 2005
indexed view.
The view contains several tables and utilizes an 'Instead Of' trigger to
handle inserts and updates in order to apply the data to the correct tables.

The trigger works fine for updates and inserts.

The form works fine for updates.

However, after userting a new record, instead of the new row displaying the
data, it shows #deleted.
In order to show the new record, I need to requery the form.

I am guessing that Access has no way to identify the new row.

Is there a way to avoid the requery?


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200806/1

Thanks for the reply, but the question is how to avoid a requery. Requering
the form after every insert is not acceptable.

--

AG
Email: discussATadhdataDOTcom
"bhicks11 via AccessMonster.com" u44327@uwe wrote in message
news:861089fcba77f@uwe...
I don't know SQL Server but you can requery with

AfterUpdate
me.requery in an event procedure

or run a macro that has the requery command.

Bonnie

http://www.dataplus-svc.com

AG wrote:
Using Access 2000.

I have a form that opens in datasheet view and is bound to a SQL Server
2005
indexed view.
The view contains several tables and utilizes an 'Instead Of' trigger to
handle inserts and updates in order to apply the data to the correct
tables.

The trigger works fine for updates and inserts.

The form works fine for updates.

However, after userting a new record, instead of the new row displaying
the
data, it shows #deleted.
In order to show the new record, I need to requery the form.

I am guessing that Access has no way to identify the new row.

Is there a way to avoid the requery?


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200806/1

"AG" wrote in message
...
Using Access 2000.

I have a form that opens in datasheet view and is bound to a SQL Server
2005 indexed view.
The view contains several tables and utilizes an 'Instead Of' trigger to
handle inserts and updates in order to apply the data to the correct
tables.

The trigger works fine for updates and inserts.

The form works fine for updates.

However, after userting a new record, instead of the new row displaying
the data, it shows #deleted.
In order to show the new record, I need to requery the form.

I am guessing that Access has no way to identify the new row.

Is there a way to avoid the requery?


I don't know if this will help or not, but does the table have a timestamp
field? That's been known to help Access identify the specific record, when
dealing with SQL Server tables.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Thanks for the reply Dirk.

The view consists of about 8 tables, only two of which get updates or
inserts. The rest are lookup tables that are included in the view mainly so
that the form can be sorted on their values. However, if the new record
requires a new record entered in a lookup table, the trigger handles that
also.

Both of the main tables have timestamps and both timestamps are included in
the view. Without the timestamps, Access raised the error of 'someone else
changed the record...'.

If the insert only involves the two main tables, there is no problem. Access
displays the new record.
The problem occurs is the insert requires an addition to one of the lookup
tables.

--

AG
Email: discussATadhdataDOTcom
"Dirk Goldgar" wrote in message
...
"AG" wrote in message
...
Using Access 2000.

I have a form that opens in datasheet view and is bound to a SQL Server
2005 indexed view.
The view contains several tables and utilizes an 'Instead Of' trigger to
handle inserts and updates in order to apply the data to the correct
tables.

The trigger works fine for updates and inserts.

The form works fine for updates.

However, after userting a new record, instead of the new row displaying
the data, it shows #deleted.
In order to show the new record, I need to requery the form.

I am guessing that Access has no way to identify the new row.

Is there a way to avoid the requery?


I don't know if this will help or not, but does the table have a timestamp
field? That's been known to help Access identify the specific record,
when dealing with SQL Server tables.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Perhaps you could use the following properties of the recordset object:
ADO: recordset.bookmark?
DAO: recordset.bookmark OR recordset.lastmodified
I don't know if this will work out with a backend SQL server. Only have
experience with Access backend, where I use the combination (DAO)

rs.addnew
and
rs.lastmodified

to find the autonumber (primary key) of the new record in this rs to insert
into a child table in the same transaction.

Regards,

M.


"AG" wrote:

Using Access 2000.

I have a form that opens in datasheet view and is bound to a SQL Server 2005
indexed view.
The view contains several tables and utilizes an 'Instead Of' trigger to
handle inserts and updates in order to apply the data to the correct tables.

The trigger works fine for updates and inserts.

The form works fine for updates.

However, after userting a new record, instead of the new row displaying the
data, it shows #deleted.
In order to show the new record, I need to requery the form.

I am guessing that Access has no way to identify the new row.

Is there a way to avoid the requery?

--

AG
Email: discussATadhdataDOTcom

Thanks for the reply, but I don't understand. It is a bound datasheet form,
not code adding the record via code.

--

AG
Email: discussATadhdataDOTcom
"M." wrote in message
...
Perhaps you could use the following properties of the recordset object:
ADO: recordset.bookmark?
DAO: recordset.bookmark OR recordset.lastmodified
I don't know if this will work out with a backend SQL server. Only have
experience with Access backend, where I use the combination (DAO)

rs.addnew
and
rs.lastmodified

to find the autonumber (primary key) of the new record in this rs to
insert
into a child table in the same transaction.

Regards,

M.


"AG" wrote:

Using Access 2000.

I have a form that opens in datasheet view and is bound to a SQL Server
2005
indexed view.
The view contains several tables and utilizes an 'Instead Of' trigger to
handle inserts and updates in order to apply the data to the correct
tables.

The trigger works fine for updates and inserts.

The form works fine for updates.

However, after userting a new record, instead of the new row displaying
the
data, it shows #deleted.
In order to show the new record, I need to requery the form.

I am guessing that Access has no way to identify the new row.

Is there a way to avoid the requery?

--

AG
Email: discussATadhdataDOTcom

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: .
================================================== =========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.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/subscripti...t/default.aspx.
================================================== ==========
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======

Thanks Charles.
It works fine in Access 2003. I was considering having client upgrade
anyway, so now that clinches it.
I did, however, find out what is going on. The problem is in Access
identifying the new data row in order to repopulate the row on screen.
http://support.microsoft.com/default...b;en-us;128809
I guess 2003 handles it better.

My actual project is converting a complex application with an mdb front end
and back end to a SQL Server back end.
Would there be any additional advantages/features (regarding SQL Server) if
I were to go to 2007?

This does raise another question.
It takes Access two or three tries to get the new data row because it does
not have the new PK value.
Initially, it queries for a null PK, which of course, will return nothing,
so it then queries again using other field data, which returns the new data
row.

When the row is inserted, there must be some communication from SQL Server
to Access to let Access know that the insert was successful. Isn't there
some way to return the new PK value so that Access can get the data on the
first try?

--

AG
Email: discussATadhdataDOTcom
""Charles Wang [MSFT]"" wrote in message
...
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: .
================================================== =========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.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/subscripti...t/default.aspx.
================================================== ==========
This posting is provided "AS IS" with no warranties, and confers no
rights.
================================================== =======

Hi AG,
Regarding your question, "Would there be any additional advantages/features
(regarding SQL Server) if I were to go to 2007?", though few documents
clearly talk about this. Per my use experiences, I do feel that Access 2007
has better performance and more effective communications with SQL Server.
In addition, you cannot utilize the SQL Server 2005 features from older
version ADP project. I recommend that you upgrade your Access 2000 to 2007
so that you can get more benefits from Access 2007. You can refer to the
articles:
Microsoft Office Access 2007 top 10 benefits
http://office.microsoft.com/en-us/ac...650211033.aspx
Database specifications
http://office.microsoft.com/en-us/ac...307391033.aspx

Regarding new PK value, I think that you need not worry about this in
Access 2007. Assume that your SQL table has an identity column, you can
conveniently add new rows as well as in SQL Server Management Studio. The
new PK value will be returned immediately after you add a new record.

Hope this helps. Please feel free to let me know if you have any other
questions or concerns.


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: .
================================================== =======
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======