Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
| Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 2nd, 2012, 01:19 PM
Registered User
Points: 5, Level: 1
Points: 5, Level: 1 Points: 5, Level: 1 Points: 5, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2012
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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:
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




Similar Threads
Thread Thread Starter Forum Replies Last Post
Updating of linked table in Access rmccafferty Access 2 August 28th, 2009 12:07 PM
Inserting additional records to table Neal SQL Server 2000 2 October 4th, 2007 06:08 AM
Inserting unique records from a table to another elygp SQL Server 2000 0 September 10th, 2007 01:58 AM
Access MDB with a Linked SQL Server Table..... timmaher Classic ASP Databases 0 September 28th, 2005 03:57 AM
Access and update linked SQL Server 2000 table Lizu Access 9 May 10th, 2004 12:42 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.