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