Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old April 3rd, 2006, 10:33 AM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default 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.
__________________
Greg Serrano
Michigan Dept. of Environmental Quality
Air Quality Division
Reply With Quote
  #2 (permalink)  
Old April 3rd, 2006, 11:47 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

"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
Reply With Quote
  #3 (permalink)  
Old April 3rd, 2006, 12:52 PM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL Update query (error message) Neal SQL Server 2000 1 August 3rd, 2007 08:57 PM
Append Query Cybersurfer Access 1 February 13th, 2006 01:02 PM
append query? bph Access 2 November 23rd, 2004 12:44 PM
Query String Too Long....Max Length Error Message phungleon Classic ASP Databases 14 May 28th, 2004 12:25 PM
append query stoneman Access 2 November 12th, 2003 09:17 PM



All times are GMT -4. The time now is 06:21 AM.


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