Problem with @@IDENTITY in replicated table with triggers

[Aidar]

The problem:

When I try to add a new record into a form or even directly into a table the
following error occurs: "The data was added to the database but the data
won't be displayed in the form because it doesn't satisfy the criteria in
the underlying record source." in my Access 2000 SP3.



The problem occurs at the Subscriber side of SQL Server 2000 SP3a
"Transactional Replication with Queued Update Subscribers". When the
subscription is created, SQL Server automatically adds triggers
[trg_MSsync_del_TABLE1], [trg_MSsync_ins_TABLE1] and
[trg_MSsync_upd_TABLE1].



[trg_MSsync_ins_TABLE1] inserts some records into another table that is used
by replication system of SQL Server 2000. When Access try to receive
@@IDENTITY value, it gets wrong result because that is an identity value
from the table, that is updated by trigger [trg_MSsync_ins_TABLE1], not the
table that I update though Access ADP.



In SQL Server 2000 there is a new function called SCOPE_IDENTITY() that can
be used to receive the value I need. But Access 2000-2002, if I am not
mistaken, also supports SQL Server 7.0, in which there was not such
function, so, for backward compatibility it uses @@IDENTITY.



Access 2003 works only with SQL Server 2000, so it seems, that
SCOPE_IDENTITY() is used because everything at first sight works at just
fine, without errors. But our corporate standard is MSOffice2000.



The "dumb" resolution, according to MSKB 275090, is to write:



Private Sub Form_AfterInsert()

' Refresh the form after records are added

Me.Refresh

' OPTIONAL: This code will place the cursor on

' the first record. Use the GoToRecord method

' to move the cursor to a new record.

DoCmd.GoToRecord , , acNewRec

End Sub



but in multi-user environment it seems not to be the best decision - user
just looses his new record among others after it was submitted into a form
or a table.



So, the question: Is there any way to force MS Access 2000 to use
SCOPE_IDENTITY() instead of @@IDENTITY when it inserts new value into a
table? Or maybe there is another way to solve this problem?



Thanks in advance,

Aidar

 

[Aidar]

Little correction:

That's table definition at subscriber side:

CREATE TABLE [Table1] (

[Table1_ID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL
,

[Info] [varchar] (50) NOT NULL ,

[msrepl_tran_version] [uniqueidentifier] NOT NULL CONSTRAINT
[DF__Table1__msrepl_t__2A4B4B5E] DEFAULT (newid()),

CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED

(

[Table1_ID]

) ON [PRIMARY] ,

CONSTRAINT [repl_identity_range_tran_1074102867] CHECK NOT FOR
REPLICATION ([Table1_ID] > 400 and [Table1_ID] < 500)

) ON [PRIMARY]



When I execute in Query Analyzer:

INSERT INTO Table1(Info) Values ('JustSimpleString')

PRINT @@IDENTITY -- Returns identity value from replication service table
that was updated by replication trigger

PRINT SCOPE_IDENTITY() -- Returns the new identity value I need from current
table



This problem was discussed earlier in "Acces Project- insert trigger
problem" node:



Subject: Re: Acces Project- insert trigger problem 11/8/2003
7:09 PM PST

By: Sylvain Lafontaine

...

Some of the following suggestions might seem to have no sense,
but they work

for me :

...

4- If the previous can't work, then try with a Bad Resync
command, that

is, any resync command that doesn't have the right number of
arguments. The

right number is the number of field in the primary key for the
unique table,

so you only have to take any stored procedure which has a
greater number of

arguments. This will force Access to use the ADODB default
resync command,

which is the same as simply displaying the values just entered.

Remember that a wrong Resync Command has not the same
effect than no

Resync command at all. In this last case, the default Acess
Resync Command,

not the ADODB one, is called.

In your specific case, I usually begin with the number #4.

S.L.



I have done the following things:

1. Created a simple Datasheet form

2. RecordSource: Table1

3. Unique Table: Table1

4. Resync Command: SELECT Table1_ID, Info FROM Table1 WHERE Table1_ID = ?
AND Info = ? AND msrepl_tran_version = ?

In this case there is no error message "The data was added to the database
but the data won't be displayed in the form because it doesn't satisfy the
criteria in the underlying record source." But wrong identity value is
displayed in the form - @@Identity instead of SCOPE_IDENTITY() if I would
execute the SQL Script mentioned earlier. This problem matters because I've
got some subforms that are linked to main form using identity field. And if
there is a wrong number displayed in this field, in subforms users will edit
linked tables records that actually are linked to main table records with
another ID.

And in previous post I was mistaken - the problem still exists in Access
2003.

So my end aim is still the same - how to make things work in such a way,
that Access use SCOPE_IDENTITY() instead of @@Identity if any insert
triggers that update other tables exist for the underlying table.



Aidar

 

[Oleg Panchenko]

The problem:

When I try to add a new record into a form or even directly into a table the
following error occurs: "The data was added to the database but the data
won't be displayed in the form because it doesn't satisfy the criteria in
the underlying record source." in my Access 2000 SP3.



The problem occurs at the Subscriber side of SQL Server 2000 SP3a
"Transactional Replication with Queued Update Subscribers". When the
subscription is created, SQL Server automatically adds triggers
[trg_MSsync_del_TABLE1], [trg_MSsync_ins_TABLE1] and
[trg_MSsync_upd_TABLE1].



[trg_MSsync_ins_TABLE1] inserts some records into another table that is used
by replication system of SQL Server 2000. When Access try to receive
@@IDENTITY value, it gets wrong result because that is an identity value
from the table, that is updated by trigger [trg_MSsync_ins_TABLE1], not the
table that I update though Access ADP.



In SQL Server 2000 there is a new function called SCOPE_IDENTITY() that can
be used to receive the value I need. But Access 2000-2002, if I am not
mistaken, also supports SQL Server 7.0, in which there was not such
function, so, for backward compatibility it uses @@IDENTITY.



Access 2003 works only with SQL Server 2000, so it seems, that
SCOPE_IDENTITY() is used because everything at first sight works at just
fine, without errors. But our corporate standard is MSOffice2000.



The "dumb" resolution, according to MSKB 275090, is to write:



Private Sub Form_AfterInsert()

' Refresh the form after records are added

Me.Refresh

' OPTIONAL: This code will place the cursor on

' the first record. Use the GoToRecord method

' to move the cursor to a new record.

DoCmd.GoToRecord , , acNewRec

End Sub



but in multi-user environment it seems not to be the best decision - user
just looses his new record among others after it was submitted into a form
or a table.



So, the question: Is there any way to force MS Access 2000 to use
SCOPE_IDENTITY() instead of @@IDENTITY when it inserts new value into a
table? Or maybe there is another way to solve this problem?



Thanks in advance,

Aidar