key violations

[Guest]

I am having a problem appending my query from my form. I do not have a
primary key in the table and all of the fields have no selected in the allow
zero length option. What should I do? The SQL view is:

INSERT INTO [Invoice Processing] ( VendorName, [Invoice#] )
SELECT Forms![Invoice Update Form]![Invoice Update SubForm]!VendorName AS
[Vendor Name], Forms![Invoice Update Form].[Invoice#] AS [Invoice#], *;

 

[Guest]

I had a problem just like this a couple days ago. I succeeded by creating a
whole new table and appending everything to it, then replacing the original
with the new.

A short while later, I discovered the cause. Even tho my original table
didn't have a key, it was at one point linked to the primary key of another
table.

In plain terms, the table I was appending to had a field called ContactIndex
(which was not a key) which had been linked via query to another table (let's
call: "TablePrime") whose primary key was called ContactIndex.

I deleted the relationship, but Access still seemed to recognize the linkage
and gave me a 'key violoations' when I attempted to append records with data
in the ContactIndex which weren't in the "TablePrime" key field (eg, when
appending a record with ContactIndex=13 when TablePrime didn't have a record
with ContactIndex=13).

Hope that is clear enuf.

 

[Guest]

I neglected to mention above that the two tables had been linked with
"Enforce Referential Integry" turned on. As I said, I deleted that
relationship before running the Append query but it still acted as if it were
enforcing referential integrity.
--
JohnTheTemp

I had a problem just like this a couple days ago. I succeeded by creating a
whole new table and appending everything to it, then replacing the original
with the new.

A short while later, I discovered the cause. Even tho my original table
didn't have a key, it was at one point linked to the primary key of another
table.

In plain terms, the table I was appending to had a field called ContactIndex
(which was not a key) which had been linked via query to another table (let's
call: "TablePrime") whose primary key was called ContactIndex.

I deleted the relationship, but Access still seemed to recognize the linkage
and gave me a 'key violoations' when I attempted to append records with data
in the ContactIndex which weren't in the "TablePrime" key field (eg, when
appending a record with ContactIndex=13 when TablePrime didn't have a record
with ContactIndex=13).

Hope that is clear enuf.

--
JohnTheTemp

I am having a problem appending my query from my form. I do not have a
primary key in the table and all of the fields have no selected in the allow
zero length option. What should I do? The SQL view is:

INSERT INTO [Invoice Processing] ( VendorName, [Invoice#] )
SELECT Forms![Invoice Update Form]![Invoice Update SubForm]!VendorName AS
[Vendor Name], Forms![Invoice Update Form].[Invoice#] AS [Invoice#], *;