Problems with Append Query

[spectrolab]

Hi,

Not sure if anyone else has this problem or has already been answered. I have an append query that is supposed to add rows to a table when table A is updated. If you look at the query itself in datasheet mode, the data is there, but it is not appending to the table. This only happens on occasions, both tables are quite large (>3000 records), but I have an even larger table and query and they work fine, any ideas?

 

[GabrielR]

first check that you are referencing the right table to append to.

Second check that you are referencing the right fields to be appended.

 

[spectrolab]

Thanks Gabriel, I checked that seems to be fine. It only occurs on occasions, i.e. if there are 6 records to append, 4 might be added and 2 aren't, it is very strange, it has recently got worse as the tables have gotten larger.

 

[Pat Hartman]

Doesn't the append query tell you why it didn't add 2 rows? Turn warnings back on. The usual reason is duplicate keys or invalid data. Make sure your text fields have the Allow Zero Length string proprerty set to yes. The default in some versions of Access is no. If it is set to no and your append query has a row with null in that field, the row will not be appended.

 

[spectrolab]

Thanks for the reply Phil.
The problem with the error message is that the table that it is appending from has 3000+ records in it so it says can not add 2998 records due to primary key violation etc. The primary key is a generated number and should be unique for each record, i.e when the record is added in the form it gives it a unique sequential autonumber, this is used in the appended table to prevent duplicates. Is there any way to set up an append query that only adds unique records to the table , or do you have to have a primary key with no duplicates as I have now? What it does now is tries to append the entire table to the existing table (actually, 2 other existing tables) and only allows records with a "new" primary key to be added.