title: Stop Access from using wrong identity when appending to linked table on SQL server
body: <p><strong>TL:DR; version:</strong></p>
  
  <p>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.</p>
  
  <p>Is there any way to stop/work around this behaviour?</p>
  
  <p><strong>MCVE:</strong></p>
  
  <p>I have the following table:</p>
  
  <pre><code>CREATE TABLE MyTable(
      [ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
      [Col1] [nvarchar](255) NULL,
      [Col2] [nvarchar](255) NULL
  )
  </code></pre>
  
  <p>The table is seeded with the following information (before creating the trigger)</p>
  
  <pre><code>INSERT INTO [MyTable]
             ([Col1]
             ,[Col2])
       VALUES
             ('Col1'
             ,'Col2')
  GO 10
  </code></pre>
  
  <p>And the following table that logs changes:</p>
  
  <pre><code>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
  )
  </code></pre>
  
  <p>This table has the following trigger attached to it:</p>
  
  <pre><code>CREATE TRIGGER MyTableTrigger ON MyTable AFTER Insert, Update
      AS
  BEGIN 
      SET NOCOUNT ON;
      INSERT INTO MyTable_Changes(ID, Col1, Col2)
      SELECT * FROM Inserted
  END
  </code></pre>
  
  <p>MyTable is a linked table in Microsoft Access, using the following ODBC connection string:</p>
  
  <pre><code> ODBC;DRIVER=SQL Server;SERVER=my\server;Trusted_Connection=Yes;APP=Microsoft Office 2010;DATABASE=MyDB;
  </code></pre>
  
  <p>I'm using Access 2010, and an .accdb file</p>
  
  <p><strong>The problem:</strong></p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p><strong>What I've tried:</strong></p>
  
  <p>I've read numerous posts (like <a href="http://allenbrowne.com/ser-40.html" rel="nofollow noreferrer">this one</a>). I've tried a compact &amp; 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.</p>
  
  <p>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.</p>
  
  <p><strong>Picture:</strong> (left: before adding the record, right: after)
  <a href="https://i.stack.imgur.com/WrAYN.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/WrAYN.jpg" alt="enter image description here"></a></p>
  
  <p>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.</p>