yet another APPEND query Key Violation...

[tranchemontaigne]

I've reviewed a lot of posts about this problem, but haven't found anything that works for me.

No matter what field I pick, I am unable to append values from the source table TBMain to the destination table t10_Person. All records intended to be appended are selected only once, and all 22K records generate fields key violation errors

Failed queries look as follows

INSERT INTO t10_Person ( t10_ReportYear )
SELECT TbMain.ReportYear
FROM TbMain;

INSERT INTO t10_Person ( t10_LastName )
SELECT TbMain.LastName
FROM TbMain;

(1) I've duplicated this problem with different data types such as memo, text, long, date-time, etc regardless of which field I pick

(2) I've copies the source table into a new table then deleted the old table. I've also deleted the destination table and recreated it devoid of any records

(3) I've reviewed those pesky settings such as "allow zero length string" (set to yes for all text fields), "required" (set to no for all fields), "indexed" (set to either no or yes allow duplicates), "validation rule" (no validation rules are present) and find that the source table and destination table are the same.

(4) I've tried running the APPEND query as a SELECT query and pasting the result set and duplicated the key violation error

(5) I've tried to manually create a record by typing values into the destination table and find that this works 100%

(6) I've run compact and repair on the DB 10 times consecutively to attempt to address any low level corruption)

Any thoughts would be appreciated. This is one more of those MS Access items I hope to better understand.
________
Buy easy vape vaporizer

 

[boblarson]

Are you using an Autonumber for the PK? Do you even have a PK defined?

 

[tranchemontaigne]

Source table has an autonumber primary key. Destination table has an autonumber primary key. Neither field is called by my APPEND queries.
________
Extreme vaporizer review

 

[boblarson]

And if you add a new record manually, does the autonumber work properly?

 

[tranchemontaigne]

yes - when I manually create records in both the source table and destination table the autonumber primary key increments correctly.

Just out of curiosity, I dropped the autonumber field from the the destination table to see if it would make any difference. The append query still fails.

I've restored the autonumber field since this test was completed.
________
Honda nsx history

 

[boblarson]

1. Is the file size of the database close to 2Gb?

2. Is the database being appended an Access database or linked SQL Server?

3. What happens if you import everything in the destination database to a new, blank Access shell? Does the append still fail if you try to append to that new one?

 

[tranchemontaigne]

1. DB is just over 10 MB in size - it contains only tables and 1 query

2. All tables are contained directly within a single MS Access file

3. Problem is able to be duplicated after importing the tables and query into a new (empty) MS Access DB
________
MEXICO CITY HOTEL

 

[boblarson]

Well, I think I'm out of ideas at the moment.

 

[tranchemontaigne]

Bob,

Thanks for your efforts. I've never been this stumped by an APPEND query before either...
________
Volvo v50 picture

 

[vbaInet]

Are the tables you're inserting into linked to any other tables?

 

[tranchemontaigne]

vbaInet,

There are no linked tables in the DB
________
TOYOTA COROLLA E110

 

[vbaInet]

Can you post a stripped down version of your db so we can see what's happening?

 

[tranchemontaigne]

This is about as stripped down as I can get it. All records contain bogus information.
________
HOTELS IN MEXICO CITY

 

[boblarson]

This is about as stripped down as I can get it. All records contain bogus information.

1. I have been looking at this and cannot figure it out. I think the table is hosed because

2. I created a make table query using your same query and then put in the data and it worked.

3. I then clear that data out and then do an append query and it works.

A couple of things to note though.

Your Autonumber in the main table should be INDEXED (NO DUPLICATES) not letting duplicates as it currently is. Your append query should be appending that ID into the t10 table as well as the memo field.

In the t10 table you need to make sure your main id from the main table is set to long integer and duplicates okay.

 

[vbaInet]

Your fields and its properties make it impossible for it to insert. In some of your fields you set a Default Value of 0 AND a Index of Yes(No Duplicates). For every other record after the first insert, it will be violating the Index rule.

There was also another with a Required property set to YES. Violates this too.

Side note: you have a whole load of columns that suggests your db could be suffering from normalization problems.

 

[boblarson]

Your fields and its properties make it impossible for it to insert. In some of your fields you set a Default Value of 0 AND a Index of Yes(No Duplicates). For every other record after the first insert, it will be violating the Index rule.

t10_RecordNum

was set to REQUIRED No Duplicates

and I changed that (can't believe I missed it) and then it worked.

There was also another with a Required property set to YES. Violates this too.

I must have fixed that one but forgot about it, so don't know which it was.

Side note: you have a whole load of columns that suggests your db could be suffering from normalization problems.

I completely agree.