Stop access using incorrect authentication when adding to a 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 into another table, Access displays the global identifier (the identifier of this other table) instead of the correct primary key and populates the columns using the record value with the corresponding identifier, if there is a record with the corresponding identifier.

Is there any way to stop / circumvent this behavior?

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 following information is placed in the table (before creating the trigger)

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

And the following table that records the 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
)

The following trigger has been added to this table:

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 - Microsoft Access, ODBC:

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

Access 2010 .accdb

:

. , MyTable - 100, MyTable_Changes 10.

MyTable, Col1 "A", 11, Col2 Col2 ID 11. Col1 . F5 , .

:

(, ). , Access ( , ), .

, , , , , , navpane .

: (: , : )

, Col1 Col2 , ID 1 . , (ID 11, Col1 a, Col2 Null), .

Answer 1

ODBC , Access SELECT @@IDENTITY ( SCOPE_IDENTITY()) SQL Server :

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

, ODBC, MySQL Connector/ODBC , Access MySQL LAST_INSERT_ID() MySQL .

, Access SELECT @@IDENTITY, (: ) reset @ @IDENTITY

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)

Answer 2

, . , , .

:

Private Sub Form_AfterInsert()
    Me.Requery
End Sub

.

_Changes , :

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

@@IDENTITY, MyTable, . , Access . .

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()
)

( @@IDENTITY ), , ( , "-", OUTPUT ID ), , GUID .

, , . , , .

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

Answer 3

- .

Access. .

, Access , , ctrl-s, . "" PK "" , .

, , , Access. Access, re-link.

And as a quick test, after you have linked the table, turn it over to design mode to make sure Access sees PK (access will not see PK if it is, for example, a view, but you can select PK when connecting to the view through the GUI).

edit - I run your scripts - played a little. I set the first table in auto inc to 1000. Screenshot after entering three rows:

From what I see, this is correct.

edit # 2: From a quick search on the Internet - we see that you issue DOES, but I don’t understand why my example works 100% simply.