Identity Insert Problem

Question

I am having in issue with a table when entering records on a subform.  This occurs infrequently where we get this or a similar error:

ODBC---insert on a linked table 'Order Entry ST Materials' failed.  [Microsoft][ODBC SQL Server Driver][SQL Server]Explicit value must be specified for identity column in table 'Order Entry ST Materials' when IDENTITY_INSERT  is set to ON. (#545)

In the mail form, I populate this table through code, copying data from aother table.  The problem may occur after this procedure is run then a user enter the next record manually.  There are other conditions, and one other table exhibits this behavior sometimes.  Identity_Insert on the SQL Server table is never addressed anywhere in the code of this database and there are no procedures running on in the SQL Server database. 

I have tried creating a new table and moving the data into it, tried a new SQL Server database and moving the object and tablers into that, tried a new subform and also tried setting the Identity_Insert for the table to Off using a pass through query from Access.

Has anyone experience this and is there a solution? 

---

God Bless, Mark A. Sam

Answer 1

Hello Mark,

It looks like there are 2 common issues that cause this error which are discussed in knowledge article 908711.

908711 You receive an "Explicit value must be specified for identity column" error message when you use the replication feature to run an INSERT statement on a table in SQL Server 2005

http://support.microsoft.com/default.aspx?scid=kb;EN-US;908711

After you verify the "not for replication" option, I would then add some type of logging to the database to see if your code or the user is not including the identity column before the record is committed.

Regards,

Dennis