Stop Access from Using Wrong Identity When Appending to Linked Table on SQL Server

Stop Access from using wrong identity when appending to linked table on SQL server

An ODBC trace reveals that Access is indeed calling SELECT @@IDENTITY (as opposed to SCOPE_IDENTITY()) after inserting the row into the SQL Server linked table:

Database1       e00-1490    EXIT  SQLExecDirectW  with return code 0 (SQL_SUCCESS)
        HSTMT               0x00000000004D6990
        WCHAR *             0x000000000F314F28 [      -3] "INSERT INTO  "dbo"."Table1"  ("txt") VALUES (?)\ 0"
        SDWORD                    -3

...

Database1       e00-1490    EXIT  SQLExecDirectW  with return code 0 (SQL_SUCCESS)
        HSTMT               0x00000000004D6990
        WCHAR *             0x000007FED7E6EE58 [      -3] "SELECT @@IDENTITY\ 0"
        SDWORD                    -3

Furthermore, this behaviour appears to depend on the ODBC driver being used, since a similar test with MySQL Connector/ODBC shows that Access does not call the corresponding MySQL function LAST_INSERT_ID() after inserting a row into a MySQL linked table.

Given that Access is calling SELECT @@IDENTITY, we must modify our trigger as follows (source: here) to reset the @@IDENTITY value back to its original value

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)

MSAccess- Changes to linked table (View) not saved

I found the issue.

There was a trigger defined on the view to handle saving to the table. I altered this in SSQL to add the new column and everything is working now.

Found a hint when I tried to edit the field in SSMS and it wouldn't work there either - was getting a Row failed to retrieve on last operation error.

That lead me to a thread referencing triggers on ExpertsExchange

I will be going back and changing the DoCMD.RunSQL statements to use recordsets.

SQL Server returns different record after insert on linked MS Access table

A bit of looking around (mostly off the link included as "more" by garik), shows that you're stuck with the behavior--it's an Access/SQL Server communication bug. However, there's a workaround described at this link.

It's way too complicated for me to reproduce in detail, and very well explained there, but basicly you save @@IDENTITY to variable at the start trigger, then do a phony #temp insert to spoof the value back to what you want returned at the end.

Linked Access DB record has been changed by another user

Take a look at your linked table in SQL Server 2000. Does it have a field containing the bit datatype? Access will give you this error message in a linked table scenario if you have a bit field which does not have a default value.

It might not be what's wrong in your case, but I've experienced the same in an Access 2007 database and tracked the problem to a bit field with no default value.

How to turn IDENTITY_INSERT on and off using SQL Server 2008?

Via SQL as per MSDN

SET IDENTITY_INSERT sometableWithIdentity ON

INSERT INTO sometableWithIdentity 
    (IdentityColumn, col2, col3, ...)
VALUES 
    (AnIdentityValue, col2value, col3value, ...)

SET IDENTITY_INSERT sometableWithIdentity OFF

The complete error message tells you exactly what is wrong...

Cannot insert explicit value for identity column in table 'sometableWithIdentity' when IDENTITY_INSERT is set to OFF.

---

Related Topics