Append Query not ignoring primary key

[Robyn_P]

Okay I'm stumped, some functionality I'm sure used to work in my database now doesn't. Simple problem I have a table tblTempConnectors that I use as an interim step when importing data to tblConnectors. The data imports fine to tblTempConnectors but then I experience key violations on the import to tblConnectors because the ID numbers are already in use. I've tried creating an append query that doesn't include the [ID] field but still have the same problem. I don't care about the [ID] field they are both auto-numbers. How can I get it just to create new IDs? If it matters there are lots of gaps in the ID number sequence in tblConnectors as old data gets deleted from this table all the time. Hope that makes sense.

[JoeM]

In an Append Query, you should not list any Autonumber fields. They will automatically be added when the records are added to the new table.
So you do not want them to appear in the SELECT clause of your Append Query.

[Robyn_P]

I haven't, This is my append query, the ID field isn't there.

INSERT INTO tblConnectors ( Connector, EquipHarnessID, Type, Contacts, Gender, MateLimit )SELECT tblTempConnectors.Connector, tblTempConnectors.EquipHarnessID, tblTempConnectors.Type, tblTempConnectors.Contacts, tblTempConnectors.Gender, tblTempConnectors.MateLimitFROM tblTempConnectors;

[orange]

Robyn_P,
You should use code tags when posting code---the hash/octothorpe (#)-- in the header of the reply area.

INSERT INTO tblConnectors ( Connector, EquipHarnessID, Type, Contacts,  Gender, MateLimit )
SELECT tblTempConnectors.Connector,  tblTempConnectors.EquipHarnessID
, tblTempConnectors.Type,  tblTempConnectors.Contacts
, tblTempConnectors.Gender,  tblTempConnectors.MateLimitFROM tblTempConnectors;

[Robyn_P]

Sorry, none of the formatting of text wo0rks on the work computers for some reason, not even line breaks. Tried to update it on my phone but fiddly

[Robyn_P]

Update: Just realised when I try and add a new record manually it assigns it an ID that already exists even though it's meant to be an autonumber field. I'm googling for a solution, but if anyone knows what causes this it would be appreciated

[Robyn_P]

Okay, I think this thread has the solution, have to kick all the users out of the database before I can test it, but I think I have this covered. https://support.microsoft.com/en-gb/...a-new-record-i

[Robyn_P]

Yes a compact and repair fixed it, autonumber seeding had gone wrong somewhere.