hello;
Access 2000.  I am appending an access table (1 record) to a sql
server table. I get the message that it can't append the records
because of a key violation. There are 2 primary keys on the sql server
table. The data I am appending is not a duplicate of what already
exists in the sql server table (the data is not the same as any of the
records).

Does anybody know what could be causing this?

Thanks in advance.

Tony

Tony_E wrote:
> hello;
> Access 2000.  I am appending an access table (1 record) to a sql
> server table. I get the message that it can't append the records
> because of a key violation. There are 2 primary keys on the sql server
> table. The data I am appending is not a duplicate of what already
> exists in the sql server table (the data is not the same as any of the
> records).
>
> Does anybody know what could be causing this?
>
> Thanks in advance.
>
Point of order: a table can only have one primary key. That key might
consist of several columns, but it is still a single key. What can be
confusing is that your table can have one or more unique constraints or
indexes in addition to the primary key. For example, one might have a
primary key on an IDENTITY (autonumber) column in addition to a unique index
on the columns that comprise the table's "natural" key (there are
performance advantages to having a unique clustered index on a single
monotonically increasing column such as an IDENTITY column)

"Primary" is not the only type of key that can exist: there might also be
foreign keys (relationships) that are being violated by your insert.

On Mar 28, 12:18=A0pm, "Bob Barrows" <reb01...@NOyahooSPAM.com> wrote:
> Tony_E wrote:
> > hello;
> > Access 2000. =A0I am appending an access table (1 record) to a sql
> > server table. I get the message that it can't append the records
> > because of a key violation. There are 2 primary keys on the sql server
> > table. The data I am appending is not a duplicate of what already
> > exists in the sql server table (the data is not the same as any of the
> > records).
>
> > Does anybody know what could be causing this?
>
> > Thanks in advance.
>
> Point of order: a table can only have one primary key. That key might
> consist of several columns, but it is still a single key. What can be
> confusing is that your table can have one or more unique constraints or
> indexes in addition to the primary key. For example, one might have a
> primary key on an IDENTITY (autonumber) column in addition to a unique in=
dex
> on the columns that comprise the table's "natural" key (there are
> performance advantages to having a unique clustered index on a single
> monotonically increasing column such as an IDENTITY column)
>
> "Primary" is not the only type of key that can exist: there might also be
> foreign keys (relationships) that are being violated by your insert.

Thanks for the response. You were absolutely right, when I tried to
manually add a record to the sql server table, I received a message
ODBC --call failed--insert statement conflicted with COLUMN FOREIGN
KEY  constraint etc etc...dbname table name 'orders', column 'ID.'

What do I do now? What do I have to do to make the append work? I
assume the linked field in the relationship of the record (I am
appending) has to already have that value in a record in the sql
server table, I tried it in test and it did not work--same message.
Any ideas?

Thanks
Tony

Tony_E wrote:
> On Mar 28, 12:18 pm, "Bob Barrows" <reb01...@NOyahooSPAM.com> wrote:
>> Tony_E wrote:
>>> hello;
>>> Access 2000. I am appending an access table (1 record) to a sql
>>> server table. I get the message that it can't append the records
>>> because of a key violation. There are 2 primary keys on the sql
>>> server table. The data I am appending is not a duplicate of what
>>> already exists in the sql server table (the data is not the same as
>>> any of the records).
>>
>>> Does anybody know what could be causing this?
>>
>>> Thanks in advance.
>>
>> Point of order: a table can only have one primary key. That key might
>> consist of several columns, but it is still a single key. What can be
>> confusing is that your table can have one or more unique constraints
>> or indexes in addition to the primary key. For example, one might
>> have a primary key on an IDENTITY (autonumber) column in addition to
>> a unique index on the columns that comprise the table's "natural"
>> key (there are performance advantages to having a unique clustered
>> index on a single monotonically increasing column such as an
>> IDENTITY column)
>>
>> "Primary" is not the only type of key that can exist: there might
>> also be foreign keys (relationships) that are being violated by your
>> insert.
>
> Thanks for the response. You were absolutely right, when I tried to
> manually add a record to the sql server table, I received a message
> ODBC --call failed--insert statement conflicted with COLUMN FOREIGN
> KEY  constraint etc etc...dbname table name 'orders', column 'ID.'
>
> What do I do now? What do I have to do to make the append work?
It sounds as if you need to add a record to the related table with the ID of
the record you are adding to the first table ...it's hard to discuss these
tables in the abstract. In other words, there is a relationship (foreign
key) between a "master" table and a "child" table, so a "master" record has
to be in the "master" table before the "child" record can be added into the
"child" table.

> I assume the linked field in the relationship of the record (I am
> appending) has to already have that value in a record in the sql
> server table, I tried it in test and it did not work--same message.

You tried to insert a record into the master table and received exactly the
same message? That means the master table has its own master, I guess. Is
there a dba for that sql server who you can ask for advice? Or  the designer
of the database at least? Again, it's hard to discuss this in the abstract.
I would need to see at least the ddl (data definition language -
http://www.karaszi.com/SQLServer/info_generate_script.asp) scripts for the
tables involved to get more specific.

On Mar 28, 4:48=A0pm, "Bob Barrows" <reb01...@NOyahooSPAM.com> wrote:
> Tony_E wrote:
> > On Mar 28, 12:18 pm, "Bob Barrows" <reb01...@NOyahooSPAM.com> wrote:
> >> Tony_E wrote:
> >>> hello;
> >>> Access 2000. I am appending an access table (1 record) to a sql
> >>> server table. I get the message that it can't append the records
> >>> because of a key violation. There are 2 primary keys on the sql
> >>> server table. The data I am appending is not a duplicate of what
> >>> already exists in the sql server table (the data is not the same as
> >>> any of the records).
>
> >>> Does anybody know what could be causing this?
>
> >>> Thanks in advance.
>
> >> Point of order: a table can only have one primary key. That key might
> >> consist of several columns, but it is still a single key. What can be
> >> confusing is that your table can have one or more unique constraints
> >> or indexes in addition to the primary key. For example, one might
> >> have a primary key on an IDENTITY (autonumber) column in addition to
> >> a unique index on the columns that comprise the table's "natural"
> >> key (there are performance advantages to having a unique clustered
> >> index on a single monotonically increasing column such as an
> >> IDENTITY column)
>
> >> "Primary" is not the only type of key that can exist: there might
> >> also be foreign keys (relationships) that are being violated by your
> >> insert.
>
> > Thanks for the response. You were absolutely right, when I tried to
> > manually add a record to the sql server table, I received a message
> > ODBC --call failed--insert statement conflicted with COLUMN FOREIGN
> > KEY =A0constraint etc etc...dbname table name 'orders', column 'ID.'
>
> > What do I do now? What do I have to do to make the append work?
>
> It sounds as if you need to add a record to the related table with the ID=
 of
> the record you are adding to the first table ...it's hard to discuss thes=
e
> tables in the abstract. In other words, there is a relationship (foreign
> key) between a "master" table and a "child" table, so a "master" record h=
as
> to be in the "master" table before the "child" record can be added into t=
he
> "child" table.
>
> > I assume the linked field in the relationship of the record (I am
> > appending) has to already have that value in a record in the sql
> > server table, I tried it in test and it did not work--same message.
>
> You tried to insert a record into the master table and received exactly t=
he
> same message? That means the master table has its own master, I guess. Is
> there a dba for that sql server who you can ask for advice? Or =A0the des=
igner
> of the database at least? Again, it's hard to discuss this in the abstrac=
t.
> I would need to see at least the ddl (data definition language -http://ww=
w.karaszi.com/SQLServer/info_generate_script.asp) scripts for the
> tables involved to get more specific.- Hide quoted text -
>
> - Show quoted text -

Hello;
I made a mistake in my previous explanation--what I meant was that I
checked the master table, examined a certain record, and attempted to
add record to the child table-using the value that already exists in
the master table. I got the same message.

On Mar 29, 8:21=A0am, Tony_E <bluestealt...@hotmail.com> wrote:
> On Mar 28, 4:48=A0pm, "Bob Barrows" <reb01...@NOyahooSPAM.com> wrote:
>
>
>
>
>
> > Tony_E wrote:
> > > On Mar 28, 12:18 pm, "Bob Barrows" <reb01...@NOyahooSPAM.com> wrote:
> > >> Tony_E wrote:
> > >>> hello;
> > >>> Access 2000. I am appending an access table (1 record) to a sql
> > >>> server table. I get the message that it can't append the records
> > >>> because of a key violation. There are 2 primary keys on the sql
> > >>> server table. The data I am appending is not a duplicate of what
> > >>> already exists in the sql server table (the data is not the same as
> > >>> any of the records).
>
> > >>> Does anybody know what could be causing this?
>
> > >>> Thanks in advance.
>
> > >> Point of order: a table can only have one primary key. That key migh=
t
> > >> consist of several columns, but it is still a single key. What can b=
e
> > >> confusing is that your table can have one or more unique constraints
> > >> or indexes in addition to the primary key. For example, one might
> > >> have a primary key on an IDENTITY (autonumber) column in addition to
> > >> a unique index on the columns that comprise the table's "natural"
> > >> key (there are performance advantages to having a unique clustered
> > >> index on a single monotonically increasing column such as an
> > >> IDENTITY column)
>
> > >> "Primary" is not the only type of key that can exist: there might
> > >> also be foreign keys (relationships) that are being violated by your
> > >> insert.
>
> > > Thanks for the response. You were absolutely right, when I tried to
> > > manually add a record to the sql server table, I received a message
> > > ODBC --call failed--insert statement conflicted with COLUMN FOREIGN
> > > KEY =A0constraint etc etc...dbname table name 'orders', column 'ID.'
>
> > > What do I do now? What do I have to do to make the append work?
>
> > It sounds as if you need to add a record to the related table with the =
ID of
> > the record you are adding to the first table ...it's hard to discuss th=
ese
> > tables in the abstract. In other words, there is a relationship (foreig=
n
> > key) between a "master" table and a "child" table, so a "master" record=
 has
> > to be in the "master" table before the "child" record can be added into=
 the
> > "child" table.
>
> > > I assume the linked field in the relationship of the record (I am
> > > appending) has to already have that value in a record in the sql
> > > server table, I tried it in test and it did not work--same message.
>
> > You tried to insert a record into the master table and received exactly=
 the
> > same message? That means the master table has its own master, I guess. =
Is
> > there a dba for that sql server who you can ask for advice? Or =A0the d=
esigner
> > of the database at least? Again, it's hard to discuss this in the abstr=
act.
> > I would need to see at least the ddl (data definition language -http://=
www.karaszi.com/SQLServer/info_generate_script.asp) scripts for the
> > tables involved to get more specific.- Hide quoted text -
>
> > - Show quoted text -
>
> Hello;
> I made a mistake in my previous explanation--what I meant was that I
> checked the master table, examined a certain record, and attempted to
> add record to the child table-using the value that already exists in
> the master table. I got the same message.- Hide quoted text -
>
> - Show quoted text -

Ok, I got passed that primary key foreign key obstacle--these tables
have a one to one relationship -- I have to add a record to the parent
table before adding a record to the child table.  So when I attempted
to add a record to the parent table I received a Failure message
referencing a TRIGGER. "Insert in linked table "CUSTOMER_ORDER"
failed.  Error in trigger INSERT_CUSTOMER_ORDER.  Is there anything I
can do now to insert to this table?

Tony_E wrote:
> On Mar 29, 8:21 am, Tony_E <bluestealt...@hotmail.com> wrote:
>>
>> Hello;
>> I made a mistake in my previous explanation--what I meant was that I
>> checked the master table, examined a certain record, and attempted to
>> add record to the child table-using the value that already exists in
>> the master table. I got the same message.- Hide quoted text -
>>
>> - Show quoted text -
>
> Ok, I got passed that primary key foreign key obstacle--these tables
> have a one to one relationship -- I have to add a record to the parent
> table before adding a record to the child table.  So when I attempted
> to add a record to the parent table I received a Failure message
> referencing a TRIGGER. "Insert in linked table "CUSTOMER_ORDER"
> failed.  Error in trigger INSERT_CUSTOMER_ORDER.  Is there anything I
> can do now to insert to this table?

Ask the database designer or dba? I hesitate to suggest disabling the
trigger without knowing what business rule it is enforcing.

On Mar 29, 8:52=A0am, "Bob Barrows" <reb01...@NOyahooSPAM.com> wrote:
> Tony_E wrote:
> > On Mar 29, 8:21 am, Tony_E <bluestealt...@hotmail.com> wrote:
>
> >> Hello;
> >> I made a mistake in my previous explanation--what I meant was that I
> >> checked the master table, examined a certain record, and attempted to
> >> add record to the child table-using the value that already exists in
> >> the master table. I got the same message.- Hide quoted text -
>
> >> - Show quoted text -
>
> > Ok, I got passed that primary key foreign key obstacle--these tables
> > have a one to one relationship -- I have to add a record to the parent
> > table before adding a record to the child table. =A0So when I attempted
> > to add a record to the parent table I received a Failure message
> > referencing a TRIGGER. "Insert in linked table "CUSTOMER_ORDER"
> > failed. =A0Error in trigger INSERT_CUSTOMER_ORDER. =A0Is there anything=
 I
> > can do now to insert to this table?
>
> Ask the database designer or dba? I hesitate to suggest disabling the
> trigger without knowing what business rule it is enforcing.- Hide quoted =
text -
>
> - Show quoted text -

Yes I think when the append functionality is run, the trigger will
have to be disabled. In one of my other posts you indicated that to
disable I would have to use a passthrough query or VBA and ADO. Could
you give me a sample of how to do it in a passthrough?

Tony_E wrote:
> On Mar 29, 8:52 am, "Bob Barrows" <reb01...@NOyahooSPAM.com> wrote:
>> Tony_E wrote:
>>> On Mar 29, 8:21 am, Tony_E <bluestealt...@hotmail.com> wrote:
>>
>>>> Hello;
>>>> I made a mistake in my previous explanation--what I meant was that
>>>> I checked the master table, examined a certain record, and
>>>> attempted to add record to the child table-using the value that
>>>> already exists in the master table. I got the same message
>>
>>> Ok, I got passed that primary key foreign key obstacle--these tables
>>> have a one to one relationship -- I have to add a record to the
>>> parent table before adding a record to the child table. So when I
>>> attempted to add a record to the parent table I received a Failure
>>> message referencing a TRIGGER. "Insert in linked table
>>> "CUSTOMER_ORDER" failed. Error in trigger INSERT_CUSTOMER_ORDER. Is
>>> there anything I can do now to insert to this table?
>>
>> Ask the database designer or dba? I hesitate to suggest disabling the
>> trigger without knowing what business rule it is enforcing
>
> Yes I think when the append functionality is run, the trigger will
> have to be disabled. In one of my other posts you indicated that to
> disable I would have to use a passthrough query or VBA and ADO. Could
> you give me a sample of how to do it in a passthrough?

It's not hard to find via google so OK ... I'm just very hesitant here - do
you have any idea what that trigger is actually doing and what the
consequences of bypassing its functionality will be? Before going down this
road you should at least figure that out - you haven't provided the version
of your sql server so it's hard to get into specifics here. Run this search
to find out how to display the text of the trigger so you can see what it's
for:
http://www.google.com/search?sourceid=chrome&ie=UTF-8&q=SQL+Server+trigger+text

Then, after you are satisfied that disabling the trigger is safe, run this
search to see how to do it in your version of sql server:
http://www.google.com/search?hl=en&safe=active&q=SQL+Server+trigger+disable&aq=f&aqi=g1g-j1g-b3&aql=&oq=

If you do disable it, you should consider performing its functionality
yourself just to make sure whatever rules it is enforcing are not violated.