Key Violations in Append Queries

G

Guest

I am trying to append rows in a history query to my tables and I get the big
old key Violations error. It is the same old 3 that keep comming up and they
dont dissapear from the datasheet. This is the field that the join is
attached to with the primary key. The numbers below are the same record ID
that keep reappearing and never go away and I get the same old error. Am I
missing something or?
FormId
390
487
705

Here is the complete SQL for the query.

INSERT INTO tbl_History_Form ( FormId, FormNumber, Description,
DigitalSignature, FileName, EffDate, Attachment, FormAvailable, DateChanged )
SELECT DEV_dbo_TblForm.FormId, DEV_dbo_TblForm.FormNumber,
DEV_dbo_TblForm.Description, DEV_dbo_TblForm.DigitalSignature,
DEV_dbo_TblForm.FileName, DEV_dbo_TblForm.EffDate,
DEV_dbo_TblForm.Attachment, DEV_dbo_TblForm.FormAvailable, Now() AS
DateChanged
FROM dbo_TblForm LEFT JOIN DEV_dbo_TblForm ON dbo_TblForm.FormId =
DEV_dbo_TblForm.FormId
WHERE (((DEV_dbo_TblForm.Description) Not Like [dbo_TblForm].[description]))
OR (((DEV_dbo_TblForm.FileName) Not Like [dbo_TblForm].[filename])) OR
(((DEV_dbo_TblForm.EffDate)<>[dbo_TblForm].[effdate])) OR
(((DEV_dbo_TblForm.DigitalSignature)<>[dbo_TblForm].[digitalsignature])) OR
(((DEV_dbo_TblForm.Attachment)<>[dbo_TblForm].[attachment])) OR
(((DEV_dbo_TblForm.FormAvailable)<>[dbo_TblForm].[formavailable]))
WITH OWNERACCESS OPTION;

Any help would be appreciated
 
T

Tom Ellison

Dear Troy:

Normally, if a primary key or other unique index is created, it is because
the design of the database requires uniqueness for the key involved.
Whoever designed the database supposedly felt this was necessary. Why I do
not know. But this designed feature is working as it should (again assuming
there's good reason for it).

I suppose this is the FormId column in your query. What datatype is this
column? What is its purpose? How does the database use this column? Are
there one or more other tables that are related on this column, and if so,
what are the relationships?

It may be that you should just omit this from the append query, and allow it
to assign new values for that column, but that is not certain. Otherwise,
you're going to be altering the design of the database, and you need to know
what you're doing in that case.

Tom Ellison
 
G

Guest

Yes the form ID is a number data type and it is joined to another table with
the same data type. Both carry the same data. One is linked and the other is
on SQL. I have no clue why it is this way I was the luck one to get to fix
it. Pretty hard when you have all this garboage to filter though. Just seems
like those same 3 records stay thoughout this append query and the other
update and append queries I run following the history one. Any ideas?

Tom Ellison said:
Dear Troy:

Normally, if a primary key or other unique index is created, it is because
the design of the database requires uniqueness for the key involved.
Whoever designed the database supposedly felt this was necessary. Why I do
not know. But this designed feature is working as it should (again assuming
there's good reason for it).

I suppose this is the FormId column in your query. What datatype is this
column? What is its purpose? How does the database use this column? Are
there one or more other tables that are related on this column, and if so,
what are the relationships?

It may be that you should just omit this from the append query, and allow it
to assign new values for that column, but that is not certain. Otherwise,
you're going to be altering the design of the database, and you need to know
what you're doing in that case.

Tom Ellison


troy said:
I am trying to append rows in a history query to my tables and I get the
big
old key Violations error. It is the same old 3 that keep comming up and
they
dont dissapear from the datasheet. This is the field that the join is
attached to with the primary key. The numbers below are the same record ID
that keep reappearing and never go away and I get the same old error. Am I
missing something or?
FormId
390
487
705

Here is the complete SQL for the query.

INSERT INTO tbl_History_Form ( FormId, FormNumber, Description,
DigitalSignature, FileName, EffDate, Attachment, FormAvailable,
DateChanged )
SELECT DEV_dbo_TblForm.FormId, DEV_dbo_TblForm.FormNumber,
DEV_dbo_TblForm.Description, DEV_dbo_TblForm.DigitalSignature,
DEV_dbo_TblForm.FileName, DEV_dbo_TblForm.EffDate,
DEV_dbo_TblForm.Attachment, DEV_dbo_TblForm.FormAvailable, Now() AS
DateChanged
FROM dbo_TblForm LEFT JOIN DEV_dbo_TblForm ON dbo_TblForm.FormId =
DEV_dbo_TblForm.FormId
WHERE (((DEV_dbo_TblForm.Description) Not Like
[dbo_TblForm].[description]))
OR (((DEV_dbo_TblForm.FileName) Not Like [dbo_TblForm].[filename])) OR
(((DEV_dbo_TblForm.EffDate)<>[dbo_TblForm].[effdate])) OR
(((DEV_dbo_TblForm.DigitalSignature)<>[dbo_TblForm].[digitalsignature]))
OR
(((DEV_dbo_TblForm.Attachment)<>[dbo_TblForm].[attachment])) OR
(((DEV_dbo_TblForm.FormAvailable)<>[dbo_TblForm].[formavailable]))
WITH OWNERACCESS OPTION;

Any help would be appreciated
Click to expand...
Click to expand...
 
T

Tom Ellison

Dear Troy:

Are the rows with the same ID values duplicates in all respects, or just in
the ID value?

The decision to be made is whether these are new rows. If so, they would
need new ID values, and the related rows would need the matching new ID
values as well. This presumes that such ID values are hidden from users.
If so, then you need only be sure to change them consistently and things
should function properly.

Tom Ellison


troy said:
Yes the form ID is a number data type and it is joined to another table
with
the same data type. Both carry the same data. One is linked and the other
is
on SQL. I have no clue why it is this way I was the luck one to get to fix
it. Pretty hard when you have all this garboage to filter though. Just
seems
like those same 3 records stay thoughout this append query and the other
update and append queries I run following the history one. Any ideas?

Tom Ellison said:
Dear Troy:

Normally, if a primary key or other unique index is created, it is
because
the design of the database requires uniqueness for the key involved.
Whoever designed the database supposedly felt this was necessary. Why I
do
not know. But this designed feature is working as it should (again
assuming
there's good reason for it).

I suppose this is the FormId column in your query. What datatype is this
column? What is its purpose? How does the database use this column?
Are
there one or more other tables that are related on this column, and if
so,
what are the relationships?

It may be that you should just omit this from the append query, and allow
it
to assign new values for that column, but that is not certain.
Otherwise,
you're going to be altering the design of the database, and you need to
know
what you're doing in that case.

Tom Ellison


troy said:
I am trying to append rows in a history query to my tables and I get the
big
old key Violations error. It is the same old 3 that keep comming up and
they
dont dissapear from the datasheet. This is the field that the join is
attached to with the primary key. The numbers below are the same record
ID
that keep reappearing and never go away and I get the same old error.
Am I
missing something or?
FormId
390
487
705

Here is the complete SQL for the query.

INSERT INTO tbl_History_Form ( FormId, FormNumber, Description,
DigitalSignature, FileName, EffDate, Attachment, FormAvailable,
DateChanged )
SELECT DEV_dbo_TblForm.FormId, DEV_dbo_TblForm.FormNumber,
DEV_dbo_TblForm.Description, DEV_dbo_TblForm.DigitalSignature,
DEV_dbo_TblForm.FileName, DEV_dbo_TblForm.EffDate,
DEV_dbo_TblForm.Attachment, DEV_dbo_TblForm.FormAvailable, Now() AS
DateChanged
FROM dbo_TblForm LEFT JOIN DEV_dbo_TblForm ON dbo_TblForm.FormId =
DEV_dbo_TblForm.FormId
WHERE (((DEV_dbo_TblForm.Description) Not Like
[dbo_TblForm].[description]))
OR (((DEV_dbo_TblForm.FileName) Not Like [dbo_TblForm].[filename])) OR
(((DEV_dbo_TblForm.EffDate)<>[dbo_TblForm].[effdate])) OR
(((DEV_dbo_TblForm.DigitalSignature)<>[dbo_TblForm].[digitalsignature]))
OR
(((DEV_dbo_TblForm.Attachment)<>[dbo_TblForm].[attachment])) OR
(((DEV_dbo_TblForm.FormAvailable)<>[dbo_TblForm].[formavailable]))
WITH OWNERACCESS OPTION;

Any help would be appreciated
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

The numbers are the same in each filed as the rest in the tables. That is why
it is so strange. I even added a test field and that does not append. It now
returns with the other 3 records and cant be removed from the query results.
Some records append and some dont.

Tom Ellison said:
Dear Troy:

Are the rows with the same ID values duplicates in all respects, or just in
the ID value?

The decision to be made is whether these are new rows. If so, they would
need new ID values, and the related rows would need the matching new ID
values as well. This presumes that such ID values are hidden from users.
If so, then you need only be sure to change them consistently and things
should function properly.

Tom Ellison


troy said:
Yes the form ID is a number data type and it is joined to another table
with
the same data type. Both carry the same data. One is linked and the other
is
on SQL. I have no clue why it is this way I was the luck one to get to fix
it. Pretty hard when you have all this garboage to filter though. Just
seems
like those same 3 records stay thoughout this append query and the other
update and append queries I run following the history one. Any ideas?

Tom Ellison said:
Dear Troy:

Normally, if a primary key or other unique index is created, it is
because
the design of the database requires uniqueness for the key involved.
Whoever designed the database supposedly felt this was necessary. Why I
do
not know. But this designed feature is working as it should (again
assuming
there's good reason for it).

I suppose this is the FormId column in your query. What datatype is this
column? What is its purpose? How does the database use this column?
Are
there one or more other tables that are related on this column, and if
so,
what are the relationships?

It may be that you should just omit this from the append query, and allow
it
to assign new values for that column, but that is not certain.
Otherwise,
you're going to be altering the design of the database, and you need to
know
what you're doing in that case.

Tom Ellison


I am trying to append rows in a history query to my tables and I get the
big
old key Violations error. It is the same old 3 that keep comming up and
they
dont dissapear from the datasheet. This is the field that the join is
attached to with the primary key. The numbers below are the same record
ID
that keep reappearing and never go away and I get the same old error.
Am I
missing something or?
FormId
390
487
705

Here is the complete SQL for the query.

INSERT INTO tbl_History_Form ( FormId, FormNumber, Description,
DigitalSignature, FileName, EffDate, Attachment, FormAvailable,
DateChanged )
SELECT DEV_dbo_TblForm.FormId, DEV_dbo_TblForm.FormNumber,
DEV_dbo_TblForm.Description, DEV_dbo_TblForm.DigitalSignature,
DEV_dbo_TblForm.FileName, DEV_dbo_TblForm.EffDate,
DEV_dbo_TblForm.Attachment, DEV_dbo_TblForm.FormAvailable, Now() AS
DateChanged
FROM dbo_TblForm LEFT JOIN DEV_dbo_TblForm ON dbo_TblForm.FormId =
DEV_dbo_TblForm.FormId
WHERE (((DEV_dbo_TblForm.Description) Not Like
[dbo_TblForm].[description]))
OR (((DEV_dbo_TblForm.FileName) Not Like [dbo_TblForm].[filename])) OR
(((DEV_dbo_TblForm.EffDate)<>[dbo_TblForm].[effdate])) OR
(((DEV_dbo_TblForm.DigitalSignature)<>[dbo_TblForm].[digitalsignature]))
OR
(((DEV_dbo_TblForm.Attachment)<>[dbo_TblForm].[attachment])) OR
(((DEV_dbo_TblForm.FormAvailable)<>[dbo_TblForm].[formavailable]))
WITH OWNERACCESS OPTION;

Any help would be appreciated
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

I did forget to add that the target history table does not have the primary
key set. Also the records seem to move into the history tables but they still
show up in the datasheet view of the append query. Yes I sdtill get the key
violation error but the records move into the table. They also stay in the
datasheet view of the append query. Does this help at all?

troy said:
The numbers are the same in each filed as the rest in the tables. That is why
it is so strange. I even added a test field and that does not append. It now
returns with the other 3 records and cant be removed from the query results.
Some records append and some dont.

Tom Ellison said:
Dear Troy:

Are the rows with the same ID values duplicates in all respects, or just in
the ID value?

The decision to be made is whether these are new rows. If so, they would
need new ID values, and the related rows would need the matching new ID
values as well. This presumes that such ID values are hidden from users.
If so, then you need only be sure to change them consistently and things
should function properly.

Tom Ellison


troy said:
Yes the form ID is a number data type and it is joined to another table
with
the same data type. Both carry the same data. One is linked and the other
is
on SQL. I have no clue why it is this way I was the luck one to get to fix
it. Pretty hard when you have all this garboage to filter though. Just
seems
like those same 3 records stay thoughout this append query and the other
update and append queries I run following the history one. Any ideas?

:

Dear Troy:

Normally, if a primary key or other unique index is created, it is
because
the design of the database requires uniqueness for the key involved.
Whoever designed the database supposedly felt this was necessary. Why I
do
not know. But this designed feature is working as it should (again
assuming
there's good reason for it).

I suppose this is the FormId column in your query. What datatype is this
column? What is its purpose? How does the database use this column?
Are
there one or more other tables that are related on this column, and if
so,
what are the relationships?

It may be that you should just omit this from the append query, and allow
it
to assign new values for that column, but that is not certain.
Otherwise,
you're going to be altering the design of the database, and you need to
know
what you're doing in that case.

Tom Ellison


I am trying to append rows in a history query to my tables and I get the
big
old key Violations error. It is the same old 3 that keep comming up and
they
dont dissapear from the datasheet. This is the field that the join is
attached to with the primary key. The numbers below are the same record
ID
that keep reappearing and never go away and I get the same old error.
Am I
missing something or?
FormId
390
487
705

Here is the complete SQL for the query.

INSERT INTO tbl_History_Form ( FormId, FormNumber, Description,
DigitalSignature, FileName, EffDate, Attachment, FormAvailable,
DateChanged )
SELECT DEV_dbo_TblForm.FormId, DEV_dbo_TblForm.FormNumber,
DEV_dbo_TblForm.Description, DEV_dbo_TblForm.DigitalSignature,
DEV_dbo_TblForm.FileName, DEV_dbo_TblForm.EffDate,
DEV_dbo_TblForm.Attachment, DEV_dbo_TblForm.FormAvailable, Now() AS
DateChanged
FROM dbo_TblForm LEFT JOIN DEV_dbo_TblForm ON dbo_TblForm.FormId =
DEV_dbo_TblForm.FormId
WHERE (((DEV_dbo_TblForm.Description) Not Like
[dbo_TblForm].[description]))
OR (((DEV_dbo_TblForm.FileName) Not Like [dbo_TblForm].[filename])) OR
(((DEV_dbo_TblForm.EffDate)<>[dbo_TblForm].[effdate])) OR
(((DEV_dbo_TblForm.DigitalSignature)<>[dbo_TblForm].[digitalsignature]))
OR
(((DEV_dbo_TblForm.Attachment)<>[dbo_TblForm].[attachment])) OR
(((DEV_dbo_TblForm.FormAvailable)<>[dbo_TblForm].[formavailable]))
WITH OWNERACCESS OPTION;

Any help would be appreciated
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Records remain in Append and Update queries 5
How do I avoid key violations in append queries 7

Top