Append Query Error Message Misleading

SerranoG · April 3rd, 2006, 10:33 AM

I have a backend table that is appended to an empty front end local copy of table (same structure) in the database. The reason is to allow the user to use data from the back end located on the network at a local level to speed them up (it's painfully slow otherwise because users are scattered across the state).

In Access XP (2002), I'm getting a key violation error message when the query that imports the data to the local table runs. The local table is completely empty, so I don't know why there would be a problem. The only indexed field that is set to Indexed (No Duplicates, autonumber) is lngRecNo. However, my query does not try to put data into that field; it only tries to populate the other fields in that table. The table does have a foreign key set to Indexed (Duplicates OK).

Can anyone think of why the key violation would happen? Thanks in advance for your help.

mmcdonal · April 3rd, 2006, 11:47 AM

"my query does not try to put data into that field" ...

If the field is there in the empty table, and you are NOT trying to put data into it, then remove it.

Alternatively, if the user is not updating data in the local table (since it is an import,) leave the field in the empty table but change it to number or text instead of autonumber.

If you try to add records to a table with an emptry autonumber field, you will get this error.

Am I on the right track here?

HTH

mmcdonal

SerranoG · April 3rd, 2006, 12:52 PM

Quote:

quote:If the field is there in the empty table, and you are NOT trying to put data into it, then remove it.

The error persisted. D'oh!

Quote:

quote:Alternatively, if the user is not updating data in the local table (since it is an import,)

Actually, yes, the user is updating the data and then it's exported replacing the network data.

Quote:

quote:leave the field in the empty table but change it to number or text instead of autonumber.

Initially, I tried that and it didn't work either.

What did finally work was to make the query a MAKE TABLE query where the local table was created from scratch (hence eliminating the autonumber field as in your first suggestion). It would error out as an APPEND QUERY.

Quote:

quote:If you try to add records to a table with an emptry autonumber field, you will get this error.

Because the field is autonumber, wouldn't it always populate itself in an append query and, therefore, this would never be an issue?

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division