M
Malcolm Cook
Symptoms:
ADP, ACC2002 SP3, SQLServer 2000
1) "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 "
2) You're sure that the data DOES in fact match the underlying record source
(for instance, the underlying recordsource is the whole table).
3) You've already made sure that you trigger performs a 'SET NOCOUNT ON' (or
you do it globally on the current connection), becuase you know already that
ACC2002 will be confused if you don't.
4) You come to the point that you realize that Access's dependency on
@@IDENTITY is being thwarted by, say, a trigger on your table which does
inserts on other tables
5) The workarounds offered in
http://support.microsoft.com/default.aspx?scid=kb;en-us;275090 prove
unsatisfactory
Why? Because the workarounds refresh the recordset and change the
current record.
So, for example, if Access writes the current record to the database as
a result of, say, tabbing into a subform, or the user selecting "Records >
Save Record" there is
NO WAY to navigate to the newly entered record, and even if you COULD,
you're going to rerun your Form_Current which may not be what your
application expects
(nor should it).
WHAT TO DO?
First, get MS Access developers to look into using
IDENT_CURRENT('table_name') instead of @@IDENTITY (assuming access can
figure out which table to ask for, which is not TOO hard, and could use a
hint if needed such as might be supplied in the forms unique table
property). (This assumes IDENT_CURRENT works, which I can't first hand
vouch for). How do I do this? Are you listening?
Second, In the mean time, hack/recode the triggers on your tables with
triggers to call a new stored procedure as the final thing they do which
will reset the @@IDENTITY to that appropriate to the table holding the
trigger.
For example, from a trigger of mine I had on a table name 'plate' , the last
line was:
execute sp_reset_IDENTITY_from_IDENT_CURRENT 'plate'
And, after my sig, is the definition of the stored procedure.
Questions? Complaints? Better workarounds?
Cheers,
--
Malcolm Cook - (e-mail address removed)
Database Applications Manager - Bioinformatics
Stowers Institute for Medical Research - Kansas City, MO USA
CREATE PROCEDURE [dbo].[sp_reset_IDENTITY_from_IDENT_CURRENT]
(@tablename as varchar(80))
/**
Pupose: reset global @@IDENTITY to be the appropriate for the database
table @tablename.
Usage: As the last thing done in a trigger, if you think that some client
application is expecting @@IDENTITY to reflect the last inserted row for
the triggers base table (hellooooo MS Access developers!!!!)
instead of, perhaps, using IDENT_INSERT.
Author: Malcolm Cook ([email protected])
Kudos: to Nik Sestrin ([email protected]) who described the
workaround in
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&selm=O1BcqJTQBHA.1788@tkmsftngp05
**/
AS
DECLARE @strSQL varchar(50)
set nocount on --not doing this can confuse Access ADPs as well
SET @strSQL=N'SELECT Identity (Int, ' + Cast(IDENT_CURRENT(@tablename) As
Varchar(10)) + ',
1) AS id INTO #Tmp'
EXECUTE (@strSQL)
GO
ADP, ACC2002 SP3, SQLServer 2000
1) "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 "
2) You're sure that the data DOES in fact match the underlying record source
(for instance, the underlying recordsource is the whole table).
3) You've already made sure that you trigger performs a 'SET NOCOUNT ON' (or
you do it globally on the current connection), becuase you know already that
ACC2002 will be confused if you don't.
4) You come to the point that you realize that Access's dependency on
@@IDENTITY is being thwarted by, say, a trigger on your table which does
inserts on other tables
5) The workarounds offered in
http://support.microsoft.com/default.aspx?scid=kb;en-us;275090 prove
unsatisfactory
Why? Because the workarounds refresh the recordset and change the
current record.
So, for example, if Access writes the current record to the database as
a result of, say, tabbing into a subform, or the user selecting "Records >
Save Record" there is
NO WAY to navigate to the newly entered record, and even if you COULD,
you're going to rerun your Form_Current which may not be what your
application expects
(nor should it).
WHAT TO DO?
First, get MS Access developers to look into using
IDENT_CURRENT('table_name') instead of @@IDENTITY (assuming access can
figure out which table to ask for, which is not TOO hard, and could use a
hint if needed such as might be supplied in the forms unique table
property). (This assumes IDENT_CURRENT works, which I can't first hand
vouch for). How do I do this? Are you listening?
Second, In the mean time, hack/recode the triggers on your tables with
triggers to call a new stored procedure as the final thing they do which
will reset the @@IDENTITY to that appropriate to the table holding the
trigger.
For example, from a trigger of mine I had on a table name 'plate' , the last
line was:
execute sp_reset_IDENTITY_from_IDENT_CURRENT 'plate'
And, after my sig, is the definition of the stored procedure.
Questions? Complaints? Better workarounds?
Cheers,
--
Malcolm Cook - (e-mail address removed)
Database Applications Manager - Bioinformatics
Stowers Institute for Medical Research - Kansas City, MO USA
CREATE PROCEDURE [dbo].[sp_reset_IDENTITY_from_IDENT_CURRENT]
(@tablename as varchar(80))
/**
Pupose: reset global @@IDENTITY to be the appropriate for the database
table @tablename.
Usage: As the last thing done in a trigger, if you think that some client
application is expecting @@IDENTITY to reflect the last inserted row for
the triggers base table (hellooooo MS Access developers!!!!)
instead of, perhaps, using IDENT_INSERT.
Author: Malcolm Cook ([email protected])
Kudos: to Nik Sestrin ([email protected]) who described the
workaround in
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&selm=O1BcqJTQBHA.1788@tkmsftngp05
**/
AS
DECLARE @strSQL varchar(50)
set nocount on --not doing this can confuse Access ADPs as well
SET @strSQL=N'SELECT Identity (Int, ' + Cast(IDENT_CURRENT(@tablename) As
Varchar(10)) + ',
1) AS id INTO #Tmp'
EXECUTE (@strSQL)
GO
I think it is