Append query key violation

  • Thread starter Thread starter TinaMWhite
  • Start date Start date
T

TinaMWhite

In Access 2007, I am getting a key violation on an append query. I am trying
to append from one table to another table within the same database. The
destination table (Incident Log) is my main data table. The source table
(Pending Incident Log Updates) is an intermediary table for users to enter
data without accessing the main table. The idea is to review entries in the
source table (Pending Incident Log Updates), make any adjustments necessary,
and then append the destination table (Incident Log) with the adjusted
record. I will then empty (delete the records) in the source table (Pending
Incident Log Updates). The primary key of both tables is an autonumber
field, but I have tried leaving the field out of the query and still got the
error. Any assistance would be greatly appreciated. Also, please answer in
as elementary language as possible as I am still teaching myself how to use
Access. I have attached the query (in SQL) below:

This is the append query:

INSERT INTO [Incident Log] ( [Date of Incident], [Sections Involved],
[Description of Incident], Resolution, [Resolution Date], [Potential Error
Quantified], [Governing Document], [Control Objective and Activity], [Date
Modified], [Time Modified] )
SELECT [Pending Incident Log Updates].[Date of Incident], [Pending Incident
Log Updates].[Sections Involved], [Pending Incident Log Updates].[Description
of Incident], [Pending Incident Log Updates].Resolution, [Pending Incident
Log Updates].[Resolution Date], [Pending Incident Log Updates].[Potential
Error Quantified], [Pending Incident Log Updates].[Governing Document],
[Pending Incident Log Updates].[Control Objective and Activity], [Pending
Incident Log Updates].[Date Modified], [Pending Incident Log Updates].[Time
Modified]
FROM [Pending Incident Log Updates];
 
A key violation error happens when Access finds a value for one/more
field(s) already in the destination. By implication, whatever you are
trying to add Access is saying already exists in the destination table.

Note that the key violation can happen if you try to stuff a primary key in
that already exists (you knew that, you've already tried leaving out the
autonumber primary key field, right?). It can also happen if your
destination table contains an index (unique, no duplicates) on one (or more)
fields. Does your destination table have any unique indexes?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
what is your primary key in that table basically it is telling you
that that primary key already exists

the only other thing i can think of is a forign key problem where you
are entering a forign key that dosnt exist

a way to check what keys exist on that table is to open it up and then
view the indices.

Regards
Kelvan
 
Yes, the destination table has one unique index which is the primary key
which is the autonumber field. Could this be because the autonumber key
fields in the two tables are not at the same autonumber?

Jeff Boyce said:
A key violation error happens when Access finds a value for one/more
field(s) already in the destination. By implication, whatever you are
trying to add Access is saying already exists in the destination table.

Note that the key violation can happen if you try to stuff a primary key in
that already exists (you knew that, you've already tried leaving out the
autonumber primary key field, right?). It can also happen if your
destination table contains an index (unique, no duplicates) on one (or more)
fields. Does your destination table have any unique indexes?

Regards

Jeff Boyce
Microsoft Office/Access MVP

TinaMWhite said:
In Access 2007, I am getting a key violation on an append query. I am
trying
to append from one table to another table within the same database. The
destination table (Incident Log) is my main data table. The source table
(Pending Incident Log Updates) is an intermediary table for users to enter
data without accessing the main table. The idea is to review entries in
the
source table (Pending Incident Log Updates), make any adjustments
necessary,
and then append the destination table (Incident Log) with the adjusted
record. I will then empty (delete the records) in the source table
(Pending
Incident Log Updates). The primary key of both tables is an autonumber
field, but I have tried leaving the field out of the query and still got
the
error. Any assistance would be greatly appreciated. Also, please answer
in
as elementary language as possible as I am still teaching myself how to
use
Access. I have attached the query (in SQL) below:

This is the append query:

INSERT INTO [Incident Log] ( [Date of Incident], [Sections Involved],
[Description of Incident], Resolution, [Resolution Date], [Potential Error
Quantified], [Governing Document], [Control Objective and Activity], [Date
Modified], [Time Modified] )
SELECT [Pending Incident Log Updates].[Date of Incident], [Pending
Incident
Log Updates].[Sections Involved], [Pending Incident Log
Updates].[Description
of Incident], [Pending Incident Log Updates].Resolution, [Pending Incident
Log Updates].[Resolution Date], [Pending Incident Log Updates].[Potential
Error Quantified], [Pending Incident Log Updates].[Governing Document],
[Pending Incident Log Updates].[Control Objective and Activity], [Pending
Incident Log Updates].[Date Modified], [Pending Incident Log
Updates].[Time
Modified]
FROM [Pending Incident Log Updates];
Click to expand...
Click to expand...
 
In both tables, there is only one index (each), which is the primary key,
which is an autonumber field. Could this be because the autonumbers are not
at the same "new" number?
 
There is NO relationship between an Autonumber field in table1 and an
Autonumber field in table2. They are independent of each other.

Regards

Jeff Boyce
Microsoft Office/Access MVP

TinaMWhite said:
Yes, the destination table has one unique index which is the primary key
which is the autonumber field. Could this be because the autonumber key
fields in the two tables are not at the same autonumber?

Jeff Boyce said:
A key violation error happens when Access finds a value for one/more
field(s) already in the destination. By implication, whatever you are
trying to add Access is saying already exists in the destination table.

Note that the key violation can happen if you try to stuff a primary key
in
that already exists (you knew that, you've already tried leaving out the
autonumber primary key field, right?). It can also happen if your
destination table contains an index (unique, no duplicates) on one (or
more)
fields. Does your destination table have any unique indexes?

Regards

Jeff Boyce
Microsoft Office/Access MVP

TinaMWhite said:
In Access 2007, I am getting a key violation on an append query. I am
trying
to append from one table to another table within the same database.
The
destination table (Incident Log) is my main data table. The source
table
(Pending Incident Log Updates) is an intermediary table for users to
enter
data without accessing the main table. The idea is to review entries
in
the
source table (Pending Incident Log Updates), make any adjustments
necessary,
and then append the destination table (Incident Log) with the adjusted
record. I will then empty (delete the records) in the source table
(Pending
Incident Log Updates). The primary key of both tables is an autonumber
field, but I have tried leaving the field out of the query and still
got
the
error. Any assistance would be greatly appreciated. Also, please
answer
in
as elementary language as possible as I am still teaching myself how to
use
Access. I have attached the query (in SQL) below:

This is the append query:

INSERT INTO [Incident Log] ( [Date of Incident], [Sections Involved],
[Description of Incident], Resolution, [Resolution Date], [Potential
Error
Quantified], [Governing Document], [Control Objective and Activity],
[Date
Modified], [Time Modified] )
SELECT [Pending Incident Log Updates].[Date of Incident], [Pending
Incident
Log Updates].[Sections Involved], [Pending Incident Log
Updates].[Description
of Incident], [Pending Incident Log Updates].Resolution, [Pending
Incident
Log Updates].[Resolution Date], [Pending Incident Log
Updates].[Potential
Error Quantified], [Pending Incident Log Updates].[Governing Document],
[Pending Incident Log Updates].[Control Objective and Activity],
[Pending
Incident Log Updates].[Date Modified], [Pending Incident Log
Updates].[Time
Modified]
FROM [Pending Incident Log Updates];
Click to expand...
Click to expand...
Click to expand...
 
I recently had the same problem with a database and (after many hours of
debugging) discovered that the problem field was a lookup field in the
original table. Neither the original table nor the table I was appending had
a primary key, but the lookup field table did have one that was automatically
"attaching" to the values in the lookup field. Once I redefined the lookup
field and eliminated the primary key in its table, the append query worked
just fine.

I'm learning to be more careful about automatically creating primary keys.
Sometimes they create more trouble than they solve problems.
 
There's a good chance that the problems resulted from the use of the lookup
datatype in your table. This wouldn't be the first time folks have run into
issues that were resolved by changing that datatype back to the type of the
underlying key field in the table being looked-up.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
TinaMWhite said:
In Access 2007, I am getting a key violation on an append query. I am trying
to append from one table to another table within the same database. The
destination table (Incident Log) is my main data table. The source table
(Pending Incident Log Updates) is an intermediary table for users to enter
data without accessing the main table. The idea is to review entries in the
source table (Pending Incident Log Updates), make any adjustments necessary,
and then append the destination table (Incident Log) with the adjusted
record. I will then empty (delete the records) in the source table (Pending
Incident Log Updates). The primary key of both tables is an autonumber
field, but I have tried leaving the field out of the query and still got the
error. Any assistance would be greatly appreciated. Also, please answer in
as elementary language as possible as I am still teaching myself how to use
Access. I have attached the query (in SQL) below:

This is the append query:

INSERT INTO [Incident Log] ( [Date of Incident], [Sections Involved],
[Description of Incident], Resolution, [Resolution Date], [Potential Error
Quantified], [Governing Document], [Control Objective and Activity], [Date
Modified], [Time Modified] )
SELECT [Pending Incident Log Updates].[Date of Incident], [Pending Incident
Log Updates].[Sections Involved], [Pending Incident Log Updates].[Description
of Incident], [Pending Incident Log Updates].Resolution, [Pending Incident
Log Updates].[Resolution Date], [Pending Incident Log Updates].[Potential
Error Quantified], [Pending Incident Log Updates].[Governing Document],
[Pending Incident Log Updates].[Control Objective and Activity], [Pending
Incident Log Updates].[Date Modified], [Pending Incident Log Updates].[Time
Modified]
FROM [Pending Incident Log Updates];

I note that your query fields are delimited by "[]" brackets. I had a similar error problem and if I remember correctly I dragged selected the fields from the table into append to Query matrix. This caused [] brackets to enclose the field. Re-selecting (after 8 hours frustration) from the dropdown list in the append to box removed the brackets and the append query ran without error.
Click to expand...
Bob Wright
 
Back
Top