p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   Access (http://p2p.wrox.com/forumdisplay.php?f=18)
-   -   Append Query Error Message Misleading (http://p2p.wrox.com/showthread.php?t=40713)

SerranoG April 3rd, 2006 10:33 AM

Append Query Error Message Misleading
 
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


All times are GMT -4. The time now is 02:05 PM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.