Re: Access Bug with SQL Server identity columns

Yes, this is a problem with -all- microsoft database access libraries for MS
SQL Server. Even though SQL Server since version 2000 has had a reliable
method for getting the last identity value in the current context, no driver
uses it.

The work-around I've always used is to make sure that no trigger inserts into
a table that contains an IDENTITY column, and use some other unique identifier
instead. I just saw a better reloy on another newsgroup recently, though.

It turns out that, within the trigger, you can select @@INENTITY to a local
variable, then select it back into @@IDENTITY at the end of the trigger
procedure, thus putting @@IDENTITY back to the value Access will need to see
afterward.

On 5 Aug 2005 03:25:29 -0700, "Trevor Best" <googlegroups@xxxxxxxxxxxx> wrote:

>I don't know if this has been reported before but it appears to be a
>bug with Access.
>
>If I create two tables both with an identity column then create an
>insert trigger on table1 that inserts a related record into table2, now
>create a form on table1 with a subform on table2. Insert records into
>the main form to your heart's content and everything's fine, each main
>record automatically gets a child record and so far the identity
>columns are nice and sequential with no gaps, which is good for testing
>the next bit (gaping holes in the sequentialness might not show up the
>problem easily)
>
>Now truncate table2 (note: use truncate table so that the identity seed
>is reset)
>
>Now enter a new record into the form, when it saves, it will jump back
>to record 1, well actually it thinks (according to the nav bar) it's on
>the last record but the data is from the record with the id of 1. do it
>again it will jump to record 2, etc.
>
>What's happening here is that Access appears to be retrieving
>@@IDENTITY from the server and that contains the ID of table2 that was
>inserted by the trigger. If the id of table2 doesn't exist in table1,
>then the main form stays at the record just inserted (strange, I
>predicted it would go all #deleted on me).
>
>The problem only arises if the trigger inserts an ID into the sub table
>that also exists as an ID in the main table so you can see you might
>never come across the problem.
>
>I can't say what Access does internally but it appears it's using
>@@IDENTITY when a fix for this would be for it to use SCOPE_IDENTITY().
>
>So I have a bug, probable cause and a fix, who do I send that to that
>doesn't want to charge me £70 for the privilege?

.