arithabort error on inserting records via a linked table in Access 2003
I posted the issue below to a SQL forum and was informed I have to set the ARITHABORT setting to ON in the connection string. I have no idea how to alter the connection string of an ODBC via Access. Can someone help?
Original email -
I am very frustrated and could really use some help. I have an Access Database linked to SQL Tables. I have a test environment using Access 2007 and SQL 2008 R2. I have a primary key Temp ID field that uses another numeric ID I pass (ie...38092). The Temp ID primary key field is a computed column that uses that passed ID and adds a "T000..." in front of the numerica value. When I run an insert query in Access in this environment; it works great.
In the production environment, I have Access 2003 and SQL 2005. I created the same SQL database and same Access front-end. When I try to do the insert, I get the following error:
ODBC--insert on a linked table "tablename" failed.
INSERT failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods (#1934)
I have seen some articles on setting the ARITHABORT to on in the connection; but I have no control over this setting using linked tables in Access - so I have no idea how to do that?
Also, I ran the insert query in query analyzer and it worked when run as follows:
IDENTITY_INSERT tablename ON
Please help me resolve this issue?