Identity Crisis

[Kipp Woodard]

I have upsized my Access2007 application to link to SqlSever2008 tables.

I use identity based columns as the keys to all of my tables. My tables
have triggers to record all Inserts, Updates, and Deletes to audit tables
that also have Identity columns.

In my access application I am having bizarr behavior. When a form creates a
new record and writes it to the backend, a different record is returned.

I am thinking that the interface between Access and SQL Server, the SQL
driver???, is returning the Identity of the audit table record that is
inserted by the Insert trigger.

Does this make sense? Do I have to give up my identity comlumns in my audit
tables?

[Sylvain Lafontaine]

Yeah, Access has the bad idea of using @@identity instead of
scope_identity().

Solution: remove the Identity columns from your audit tables (do you really
need this for an audit table?) or store/reset the value of @@identity at the
beginning/end of your triggers:

create trigger mytable_insert_trigger on mytable for insert as

declare @identity int
declare @strsql varchar(128)

set @identity=@@identity
--your code
--insert into second table ...
--your code
set @strsql='select identity (int, ' + cast(@identity as varchar(10)) + ',1)
as id into #tmp'
execute (@strsql)

or a little more simple for the last instruction:
EXECUTE (N'SELECT Identity (Int, ' + Cast(@myid As NVarchar(10)) + ',1) AS
id INTO #Tmp'
--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

Post by Kipp Woodard
I have upsized my Access2007 application to link to SqlSever2008 tables.
I use identity based columns as the keys to all of my tables. My tables
have triggers to record all Inserts, Updates, and Deletes to audit tables
that also have Identity columns.
In my access application I am having bizarr behavior. When a form creates a
new record and writes it to the backend, a different record is returned.
I am thinking that the interface between Access and SQL Server, the SQL
driver???, is returning the Identity of the audit table record that is
inserted by the Insert trigger.
Does this make sense? Do I have to give up my identity comlumns in my audit
tables?

[Kipp Woodard]

Thanks. I had concluded that had to be the case. I dropped the identities
on the audit tables Thursday night and had a wonderful Friday as all my
issues disappeared.

I do like the identities on the audit tables, as gaps in their sequence can
reveal when there are missing records, which would indicate a problem.

It is very useful to know that I can capture and manipulate @@Identity.

This seems like a bug in Access. If scope_identity would work, then it
should be used. Perhaps it is more complicated than that???

Thanks again Sylvain!

Post by Sylvain Lafontaine
scope_identity().
Solution: remove the Identity columns from your audit tables (do you really
create trigger mytable_insert_trigger on mytable for insert as
--your code
--insert into second table ...
--your code
as id into #tmp'
id INTO #Tmp'
--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

Post by Kipp Woodard
I have upsized my Access2007 application to link to SqlSever2008 tables.
I use identity based columns as the keys to all of my tables. My tables
have triggers to record all Inserts, Updates, and Deletes to audit tables
that also have Identity columns.
In my access application I am having bizarr behavior. When a form creates a
new record and writes it to the backend, a different record is returned.
I am thinking that the interface between Access and SQL Server, the SQL
driver???, is returning the Identity of the audit table record that is
inserted by the Insert trigger.
Does this make sense? Do I have to give up my identity comlumns in my audit
tables?

[Sylvain Lafontaine]

Access doesn't use scope_identity() because the ODBC linked tables have been
designed before the creation of scope_identity() and practically no change
has been made to JET since something like 10 years or more.

For the rest, it would be easy for you to cook your own sequence but
spotting missing records in the audit tables would be a problem. However,
you should be able to spot any trouble by looking at the data tables
themselves; as they should too show any missing records because of a
rollback.
--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

Post by Kipp Woodard
Thanks. I had concluded that had to be the case. I dropped the identities
on the audit tables Thursday night and had a wonderful Friday as all my
issues disappeared.
I do like the identities on the audit tables, as gaps in their sequence can
reveal when there are missing records, which would indicate a problem.
This seems like a bug in Access. If scope_identity would work, then it
should be used. Perhaps it is more complicated than that???
Thanks again Sylvain!

Post by Sylvain Lafontaine
scope_identity().
Solution: remove the Identity columns from your audit tables (do you really
create trigger mytable_insert_trigger on mytable for insert as
--your code
--insert into second table ...
--your code
as id into #tmp'
id INTO #Tmp'
--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

Post by Kipp Woodard
I have upsized my Access2007 application to link to SqlSever2008 tables.
I use identity based columns as the keys to all of my tables. My tables
have triggers to record all Inserts, Updates, and Deletes to audit tables
that also have Identity columns.
In my access application I am having bizarr behavior. When a form
creates
a
new record and writes it to the backend, a different record is returned.
I am thinking that the interface between Access and SQL Server, the SQL
driver???, is returning the Identity of the audit table record that is
inserted by the Insert trigger.
Does this make sense? Do I have to give up my identity comlumns in my audit
tables?