Hello I am new to this forum. I am trying to use the append query on two tables that are identical in data type, from TblA to TblB, but cannot append due to key violations for each row.

Both TblA and TablB have an autonumber primary key, although TblA does not need to have a primary key. Any suggestions as to what I should be looking for first?

Thanks

Would it work for you if you did not append the pk field to tblB?

no. Access freezes up and won't append.

Something else is lurking about... Are any of the fields in table two set to no duplicates?

The easiest way to troubleshoot this is to try to enter one record by hand. Then you will get an ACTUAL error message instead of the entirely too vague key violation error.

no problems adding data to either table

Something else is lurking about... Are any of the fields in table two set to no duplicates?

yes. should that be changed?

Could be - Keep track of what you change so you can back up if it messes something else up and so that you can run the append again at a later date...

Edit: If fact I do all of this on copies of the originals - :)

but I can't have duplicates - each record is unique

What does your append query look like if you change it to a select query? Is it inadvertantly creating duplicate records?

What does your append query look like if you change it to a select query? Is it inadvertantly creating duplicate records?

by changing it to a select query, there are no duplicate records

When you tried to enter a record by hand, did you try to enter one of the records that gave you a key violation error when you tried to append it using the query?

Per chance do you have an autonumber field linked to another autonumber field? That is a common error when people set up relationships and aren't too familiar with how it all works. That could cause your problem.

When you tried to enter a record by hand, did you try to enter one of the records that gave you a key violation error when you tried to append it using the query?

I don't know which record gave me a key violation. I wanted to append 4414 records from one table to the next, and the error said there is 0 records with a type conversion error, 4414 records with a key violation error, 0 records with lock violations, 0 records with validation rule violations.

Per chance do you have an autonumber field linked to another autonumber field?

What do you mean by "linked"?

In your relationships... between Table A and Table B.

No linkage.

Can you post your table structure?

Both Tables have:

Field 1: Primary Key - Autonumber
Fields 2 - 7: Text
Field 8: Number
Fields 9 & 10: Date/Time
Fields 11 - 15: Number

Is this what you're looking for?

Both Tables have:

Field 1: Primary Key - Autonumber
Fields 2 - 7: Text
Field 8: Number
Fields 9 & 10: Date/Time
Fields 11 - 15: Number

Is this what you're looking for?

So, when you are trying to copy the data from one table to the other are you remembering to NOT include the autonumber field?

It gives me "Invalid Argument" when I try to run the append query

What is the SQL to your Append Query?

INSERT INTO tblHoldings ( FundID, ReportID, CUSIP, Issuer, Type1ID, Type2ID, FixFloat, IssueDate, MaturityDate, EffectiveDays, FaceRate, FaceValue, MktValue, AmtCost )
SELECT tblHoldingsNew.FundID, tblHoldingsNew.ReportID, tblHoldingsNew.CUSIP, tblHoldingsNew.Issuer, tblHoldingsNew.Type1ID, tblHoldingsNew.Type2ID, tblHoldingsNew.FixFloat, tblHoldingsNew.IssueDate, tblHoldingsNew.MaturityDate, tblHoldingsNew.EffectiveDays, tblHoldingsNew.FaceRate, tblHoldingsNew.FaceValue, tblHoldingsNew.MktValue, tblHoldingsNew.AmtCost
FROM tblHoldingsNew;

That is very strange, it looks like it should work. What if you put only one record in your tblHoldingsNew table, and just try to append that?