Arithabort error when inserting on linked access tables

[Nyneave]

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:
SET
IDENTITY_INSERT tablename ON
INSERT
INTO tablename(Field1,Field2,Field3,Field4)
Values
(#####,############,MM/dd/yyyy,'aa')
SET
IDENTITY_INSERT tablenameOFF
Please help me resolve this issue?
nyneave

 

[mdlueck]

Are you running the query in Pass-Through mode such that Access will not mess with the query... since you say it runs correctly in query analyzer.

And what type of database object are you using? The way to configure Pass-Through is different with each type. (ADO vs DAO, etc...)

 

[Nyneave]

I am using ODBC connection with a DSN file the IT department setup for me. On the original issue, I was just using a query in access. I tried a pass through query today and it will not recognize the local table (which I am using to update the external SQL table). I found documents online about using a pass through to update local data with external data (using a make-table query) but I could not find the opposite situation.

 

[mdlueck]

I tried a pass through query today and it will not recognize the local table (which I am using to update the external SQL table).

Aaahhh, now we are getting somewhere! In order to issue a Pass-Through query to the SQL BE DB and download into an Access FE table, then you must use a nested DAO.QueryDef solution, which I document here:

Example of DAO.QueryDef objects downloading records from a SQL BE DB via Pass-Through query and populating a FE temp table with them
http://www.access-programmers.co.uk/forums/showthread.php?p=1119605&posted=1#post1119605

 

[Nyneave]

I am looking at your code (which is a bit complex to me) and it still appears you are updating a local table; which I noted earlier I can do. The issues is I am trying to update the external table (with an insert) with data from the local table. An initial dump of records and then moving forward, the user can create a record which gets created on the external database via the front-end.

 

[mdlueck]

When running Pass-Through queries, those run in the context of the server. That is why you are not able to reference local Access FE tables... "wrong execution context".

It sounded like you wanted to download records from the SQL BE DB and INSERT them into an Access table. In order to do that, you need the Rx I provided which nests two DAO.QueryDef objects, one inside the other. DAO.QueryDef objects always exist in the context of the Access FE DB, so are able to reference each other always. Yet they may be connected to either the FE DB, or to an external DB via updating the Connection String.

So, inner DAO.QueryDef issues a Pass-Through query to SELECT records from the BE DB. By itself that query would open a table window with the result set.

The outer DAO.QueryDef object is connected to the Access FE DB, selects all from the inner DAO.QueryDef, to INSERT the records to an Access FE table.

It is a creative slight of hands.