Solved

ODBC - Insert On A linked Table Failed

Posted on 2006-11-05
2
884 Views
Last Modified: 2012-08-13
HI

i cant understand what is wrong so please help ...

i have an access 2003 frontend that is linked through to a SQL server backend

for some reason i can not append some records to a table. (others work perfecty well)

the message i get when trying to append is:
ODBC --Insert on a linked table 'tblQuality_Audit_Items' failed
[Microsoft][ODBC SQL Server Driver][SQL_Server] Explicit value must be specified for indentity column in table 'Table1'
when IDENTITY_INSERT is set to ON. (#545)

now even if i go straight to the table in access i try to insert a record manually i get the same message. however if i try the same in SQL server i get no message
I have A Indentiy column called ControlNumber. all other fields allow nulls

any ideas?
thanks

ozgirl
0
Comment
Question by:ozgirl
2 Comments
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 17878709
Hi ozgirl,

Apparently, your SQL db has IDENTITY_INSERT set to ON.

From SQL Query Analyzer, run:

    SET IDENTITY_INSERT tblQuality_Audit_Items OFF

Generally speaking, you should never be trying to insert into an identity column.  Defeats the whole purpose of
using an identity column, really.

Regards,

Patrick
0
 

Author Comment

by:ozgirl
ID: 17879042
thanks Patrick

i have just looked at my append query and doh.....

all working well now

thanks
ozgirl
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question