ERROR: 3155, ODBC--insert on a linked table [table name] failed. Microsoft ODBC SQL Server Driver - SQL Server - The target table [table name] of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause (#334).
This is an odd situation for a Microsoft Access to SQL Server 2016 user's machine. There are quite a few Access to SQL Server users in this office using the same software but only one of the users is seeing this error and it only started on 3/3/2022. All the code was working before that point. The code continues to work for other users in the office.
Facts of this situation:
No other users in the office are experiencing this issue while running the same code. It's just a problem with this one Windows 11 machine. Many of the other users have older Windows 10 machines that also had Windows updates in the AM of 3/3/2023. They all work.
The problem machine is one of the newest machines in the office and it's running Windows 11.
The after update trigger on the table in question is very simple and just takes the int identity primary keys from the insert temp table and inserts them in another table with the current date/time. The trigger does not have an OUTPUT clause. The trigger does have an INSERT INTO clause. I attempted to rewrite the trigger to not use any data from the insert table but any trigger at all on the table in question caused the same issue from the problem machine.
The trigger in question has been in use for many years with no prior issues and continues to work for other users.
I tried:
I ruled out the Access code as the culprit by simply inserting into the linked Access table directly. With the trigger in place the direct inserts into the access linked table (sidestepping all Access code) also failed with the same error above. So it is an Access/ODBC/SQL Server insert issue.
I also was able to insert direclty into the table in SQL Server with T-SQL without raising the same error but with the trigger firing just fine. The other Access users were also inserting into the same table with the same Access front-end with no issues using Access to ODBC driver 17 to SQL Server 2016.
When I disabled the trigger and tried it from the problem Windows 11 machine, the same error came up for another table with a different trigger on another table elsewhere in the database.
The problem machine had ODBC SQL Server Driver 18 installed. I uninstalled driver 18 and installed ODBC SQL Server Driver 17 and rebooted and tried it again and the same issue occurred on the problem machine with driver 17. ODBC SQL Server Driver 18 has been an issue on other machines so I left ODBC Driver 17 installed on the problem machine but the problem persists.
This seems like an Access to ODBC 17/18 to SQL Server 2016 issue but the exact cause is still a mystery. I anticipate that the issue was caused by the Windows Updates on 3/3/2023, but the other option is a virus. I scanned the machine with Windows Defender and no viruses were found. It could be a virus or the Windows updates from 3/3/2023. My hunch is that it's the Windows update that caused it.
If anyone else has seen this issue or has any other insight please let me know. I'm not finding any hits on this error recently. Please comment if you're seeing this error from a recent Windows update. With little traffic on the issue the virus possibility is still of concern.
