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

Question

TL:DR; version:

If I insert a record into a linked table that has a trigger that inserts a record in a different table, Access displays the global identity (the identity of that different table) instead of the correct primary key, and fills the columns with the values of the record with the corresponding identity if a record with the corresponding identity exists.

Is there any way to stop/work around this behaviour?

MCVE:

I have the following table:

CREATE TABLE MyTable(
    [ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [Col1] [nvarchar](255) NULL,
    [Col2] [nvarchar](255) NULL
)

The table is seeded with the following information (before creating the trigger)

INSERT INTO [MyTable]
           ([Col1]
           ,[Col2])
     VALUES
           ('Col1'
           ,'Col2')
GO 10

And the following table that logs changes:

CREATE TABLE MyTable_Changes(
    [ID] [int] NOT NULL,
    [Col1] [nvarchar](255) NULL,
    [Col2] [nvarchar](255) NULL,
    [IDChange] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY
)

This table has the following trigger attached to it:

CREATE TRIGGER MyTableTrigger ON MyTable AFTER Insert, Update
    AS
BEGIN 
    SET NOCOUNT ON;
    INSERT INTO MyTable_Changes(ID, Col1, Col2)
    SELECT * FROM Inserted
END

MyTable is a linked table in Microsoft Access, using the following ODBC connection string:

 ODBC;DRIVER=SQL Server;SERVER=my\server;Trusted_Connection=Yes;APP=Microsoft Office 2010;DATABASE=MyDB;

I'm using Access 2010, and an .accdb file

The problem:

I'm inserting records through the GUI. I've inserted several records before enabling the trigger, and the identity seed for MyTable is 100, but for MyTable_Changes, the identity seed is 10.

When I add a new record to MyTable, and I set Col1 equal to "A", after inserting, the ID column of the inserted record displays as 11, and Col2 displays as the value of Col2 for ID 11. Col1 displays normally. After hitting F5, the record displays like I just added it.

What I've tried:

I've read numerous posts (like this one). I've tried a compact & repair, changing the seed on the table in Access (which doesn't work since it's a linked table), but haven't been able to solve it.

For now, I've included a work-around that can open linked tables as a datasheet form and requeries after updating, then navigates to the last record, but this is far from optimal, since it increases the time it takes to add records, and people can't use the navpane to open tables and add records.

Picture: (left: before adding the record, right: after)

Note that both Col1 and Col2 changed to the values corresponding with ID 1 after updating. After refreshing, the record I had added (ID 11, Col1 a, Col2 Null) properly showed.

Desmoid

MS Access is always the problem – Belinda

It might be worth a try to use the SQL Server Native Client ODBC driver, instead of the SQL Server driver. Because IMO the ODBC driver is at fault here, returning the wrong data after the INSERT. – Prosecute

@Prosecute Thanks for the tip. Unfortunately, using SQL Server Native Client 10.0, the problem also occurs, and I can't use (and haven't tried) later versions, since they will not be available to people using the database. – Desmoid

Ah, forgot the BEGIN while copy-pasting. Will try your suggestion now – Desmoid

@Parfait Adding GO inside the trigger actually introduces a syntax error (Msg 102, Level 15, State 1, Procedure MyTableTrigger, Line 19 [Batch Start Line 7] Incorrect syntax near 'GO'). As far as I could find, GO is not an actual keyword, but used by SSMS to separate batches, and thus cannot be used inside triggers. As for wrapping the stored procedure inside a BEGIN .... END block, that unfortunately didn't help – Desmoid

A possible workaround might be to tweak your trigger as described here. – Archenteron

@GordThompson Your workaround works great! Many thanks – Desmoid

Answer 1

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)

Archenteron

Answer 2

In the end, I've tried numerous workarounds to solve this problem. For anyone encountering it in the future, here are some of the working ones, and my considerations.

At first, I just moved data entry to a form, and added the following code:

Private Sub Form_AfterInsert()
    Me.Requery
End Sub

While working, this had numerous disadvantages.

Then, I just incremented the identity seed of my _Changes table to beyond that of the normal table:

DBCC CHECKIDENT ('MyTable_Changes', RESEED, 10000);

This avoids @@IDENTITY existing in MyTable, so the wrong data will no longer be displayed after adding a row. This works, but Access will no longer fetch defaults for blank columns after adding. However, for others, this might not be relevant and might be the simplest solution.

I also tried changing the identity column to a GUID

CREATE TABLE MyTable_Changes(
    [ID] [int] NOT NULL,
    [Col1] [nvarchar](255) NULL,
    [Col2] [nvarchar](255) NULL,
    [IDChange]  uniqueidentifier ROWGUIDCOL PRIMARY KEY NOT NULL
                CONSTRAINT [DF_MyTable_Changes_IDChange] DEFAULT newsequentialid()
)

This worked (since @@IDENTITY no longer got changed), fetching defaults worked, it added some complexity (I was inserting into two tables joined by a one-many relationship and had to use the OUTPUT clause to fetch the ID on one of them), and my boss decided GUIDs were unintuitive and shouldn't be used.

In the end, Gord Thompson's answer was the one I went with. I modified the code to use a table variable instead of a temporary table to make the scope of the table more explicit.

CREATE TRIGGER MyTableTrigger ON MyTable AFTER Insert, Update
    AS
BEGIN 
    SET NOCOUNT ON;
    -- Capture @@identity
    DECLARE @identity int;
    SET @identity=@@identity;
    -- Inserts here
    INSERT INTO MyTable_Changes(ID, Col1, Col2)
    SELECT * FROM Inserted;

    -- reset @@identity
    DECLARE @strsql varchar(255)
    SET @strsql='
        DECLARE @t Table(id INTEGER IDENTITY(' + cast(@identity as varchar(10)) + ',1) PRIMARY KEY);
        INSERT INTO @t DEFAULT VALUES;
        '
    EXEC(@strsql);
END

Desmoid

Answer 3

You must be doing something else on that form.

I would suggest you delete, and re-create the linked table from Access. I am unable to re-create your effect.

Keep in mind that Access does not and will not display the auto number id until you move to a new record, or you hit ctrl-s to save the current record. However, there really no reason to worry or care about this "lack" of PK id on the Access form until such time a save has occurred.

From what I can see is that you linked to the wrong table from Access. So I much suggest you delete the linked table in Access, re-link.

And as a quick test, after you link the table, flip it into design mode to ensure that Access sees the PK (access will not see the PK if this is for example a view – but you “can” select the PK when linking to a view via the GUI).

edit - I run your scripts - played a bit. I set the first table to auto inc at 1000. The screen shot after entering 3 rows is this:

From what I can see, this is correct.

edit#2: From a quick search on the internet - we see that you issue DOES exist, but I am at a loss as to why my example works 100% just fine.

Kelter

I'm not using any forms. I'm adding data directly into a linked table. I already recreated the table. An essential part for recreating the problem is that the seed in the actual table is higher than that of the changes table. The main problem is that after inserting the record, both the wrong primary key and the wrong data (data corresponding with that PK) are displayed, leading users to think that the insert went wrong. – Desmoid

Note that I have been able to replicate it both on my home machine (Access 2016, SQL server 2016, self-hosted sample db) and my work database (Access 2010, SQL server 2014). I've added more info to replicate to the question – Desmoid

I've added an image to more clearly demonstrate the problem. In design view, Access correctly identifies the ID column as a primary key and an autonumber field. – Desmoid

You stated that the main table starts at 100, but the screen cap does not concur. So how come the identity seed of 100 is not working? – Kelter

I added a screen shot of my results. It looks correct to me. I am now curious as to what is different here. – Kelter

The difference is that the main table must have been prefilled with records that are not in the changes table, not only have a different seed. The size of the difference is not relevant. Sorry if that was unclear – Desmoid

That would suggest that if I deleted all records in the changes table, then things should start to fail? I just did that, and it still works fine. I may ask within our private group – see what other experts come up with. I am as noted more curious as to why my setup works! I even ran the sql profiler on this – and see a select @@identity – and it seems to work just fine (to be fair, I see 2 select @@identity being sent by Access). Only long shot left is when you flip the linked tables into design mode, does it show PK for the linked table? I am using a2010 + sql2014. - I see you check PK idea – Kelter

I believe the bug only occurs if the identity value that has just been added to the changes table already occurs in the real table. If you cause that to happen, you will likely be able to replicate – Desmoid

Ok, I can now re-produce. It seems that simply dropping the trigger, and then re-creating the trigger - I now see your behaviour. So the "trick" seems if you dropped the trigger, and then re-created it. I look into this further - possible will edit or remove my response. I VERY much appreciate the follow up since it was MORE important that I can re-produce your issue. – Kelter