469,306 Members | 1,881 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,306 developers. It's quick & easy.

Append query key violation

I am trying to move records from one table to another using an append query. Neither table is connected via any relationship. All fields being appended have matching data types. No field is set to required, all allow zero length. Both tables have autonumber primary keys but the append query does not include either of them. If I set the table to be appended primary key "indexed" to "no", the append query works but the new record gives a duplicated primary key. If I set "indexed" to "yes(No Duplicates)" I get a key violation message and the query doesn't work. It appears that the record is trying to keep its autonumber when it moves over. I've searched for an answer but I am stuck and my brain hurts.

If anyone could help I would greatly appreciate it.

Thanks
Nov 26 '10 #1
10 9708
Mariostg
332 100+
You might want to clarify how you define primary key. By definition, a primary key cannot be duplicated. In Access, when you set a field primary key, it will be automatically set to Index, No Duplicate.
Maybe you should post some details of your table structure and query statement.
Nov 26 '10 #2
NeoPa
32,173 Expert Mod 16PB
I'm guessing this may be linked to a problem Sue was having that you helped with earlier Mario (Why is Access trying to re-use an AutoNumber when I add a record?).
Nov 27 '10 #3
I copied the table and renamed the broken one as per the link NeoPa posted. The copy appears to work? So far so good. Thanks for all your help I will re-post if it falls over again. :)
Nov 29 '10 #4
Ok I spoke too soon. I can't append the table due to a key violation again.

The table to be appended is called “Gen_Users” Pkey = Client_Ref

The table the records are coming from is called “Clients” Pkey = Client_Ref_Ref

The fields I am working with on the query are:

First_Name
Last_Name
Contact_Tel
Graduation_Dropout_Date
GP
Ref_Scan
Address
PostCode

The field which decides which records will move in the Client table is a yes/no box MoveToGenUserYes (Set to criteria True in the query)

Both tables have the same field names. Could this be what is causing the trouble?
Nov 29 '10 #5
Mariostg
332 100+
In your tables, how are the values of the pk assigned? Are they autonumber? When you append Clients data to Gen_Users, is the primary key appended too i.e. part of the insert into statement?

Actually, can you post your append query statement? I am curious.
Nov 29 '10 #6
The pk's are assigned to autonumber. Neither of them are in the query. I have attached a screen grab of the query.

Here is the sql

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO Gen_Users ( First_Name, Last_Name, Contact_Tel, Contact_Tel2, Graduation_Dropout_Date, GP, Ref_Scan, Address1, Address2, Address3, PostCode )
  2. SELECT Client.First_Name, Client.Last_Name, Client.Contact_Tel, Client.Contact_Tel2, Client.Graduation_Dropout_Date, Client.GP, Client.Ref_Scan, Client.Address1, Client.Address2, Client.Address3, Client.PostCode
  3. FROM Client
  4. WHERE (((Client.MoveToGenUserYes)=True));

Attached Images
File Type: jpg Query.jpg (25.8 KB, 3813 views)
Nov 29 '10 #7
Mariostg
332 100+
In case you haven't check this yet:
Autonumber field in Access 2007 or in Access 2003

And also, open your table in design view, select the AutoNumber field, and ensure Field Size is "Long Integer", and New Values is "Increment."
Nov 29 '10 #8
dsatino
393 256MB
Try compacting and repairing your DB. I've had an issue with this before and that fixed it. I think what happened was that I deleted the record with the last incremental auto number. The auto increment tried to reuse it, but it was still indexed.
Nov 29 '10 #9
Thanks guys. My pk autonumbers are set to long integer and increment. Compact and repair does not fix the problem.
Nov 29 '10 #10
I have fixed it. The pk autonumber just wasn't working in the Gen_Users table, so...... I created a new database and imported the Gen_Users table structure into it. I then imported the information by appending it to the new table in the new database. I then imported that new table from the new database into the original database which gives me a brand new working table. Deleted the old Gen_Users table, renamed the new table to Gen_Users and the pk problem is fixed. Thanks for your help all it really helped me work through the problem.
Nov 29 '10 #11

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by John | last post: by
2 posts views Thread by JMCN | last post: by
1 post views Thread by David Barger | last post: by
4 posts views Thread by pmacdiddie | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Geralt96 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.