Append query key violation

[Rob Amor]

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

[Mariostg]

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.

[NeoPa]

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?).

[Rob Amor]

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. :)

[Rob Amor]

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_Drop out_Date
GP
Ref_Scan
Address
PostCode

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

Both tables have the same field names. Could this be what is causing the trouble?

[Mariostg]

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.

[Rob Amor]

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

INSERT INTO Gen_Users ( First_Name, Last_Name, Contact_Tel, Contact_Tel2, Graduation_Dropout_Date, GP, Ref_Scan, Address1, Address2, Address3, PostCode )
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
FROM Client
WHERE (((Client.MoveToGenUserYes)=True));

[imgnothumb]http://bytes.com/attachments/attachment/4264d1291039903/query.jpg[/imgnothumb]

[Mariostg]

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. "

[dsatino]

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.

[Rob Amor]

Thanks guys. My pk autonumbers are set to long integer and increment. Compact and repair does not fix the problem.

[Rob Amor]

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.