• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 410
  • Last Modified:

Key violation for appending a record

I have a macro that is trying to execute an append query to a table with an autonumbered ID field (which is my key field) but it keeps failing.  I have all sorts of other append queries to the same table with no issues, so I am completely baffled.  

The way I have this structured is that I choose records from my table (tbl_prepare_financials) and then attempt to duplicate those records.  I am NOT appending the key field as it is my assumption that the autonumbering will occur when the records are appended.  Is this wrong thinking?  I even tried making the select activity ONE query, and then the appned query is based on the select query, but still keep getting 7 key violations.  If I "Un-key" the autonumber field, it works but then creates duplicate IDs.

Any suggestions?
0
jwandmrsquared
Asked:
jwandmrsquared
  • 5
  • 3
  • 3
  • +1
2 Solutions
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
" Is this wrong thinking?  "
No.  Do you have any other Unique Indexes that you have created on one or more fields - or a multi-field index?

Any required fields ?

mx
0
 
jwandmrsquaredAuthor Commented:
The SQL for the second-query:
INSERT INTO tbl_Finance_prepare_budgets_forecasts ( [Finance Contract ID], [Finance Tool Type], [Date of Item], Current_Active, [Effective Month], [Effective Year], Cost, [Forecast Type], Notes, [GA Code], TimeDateChanged, PersonwhoChanged, CashWeek, CashStatus, Check_date, PROD_ID, Total_type, Subtype, pmt_id )
SELECT qry_upd_cash_frcst_upd_sum_line_prtyr_step0.[Finance Contract ID], qry_upd_cash_frcst_upd_sum_line_prtyr_step0.[Finance Tool Type], qry_upd_cash_frcst_upd_sum_line_prtyr_step0.[Date of Item], qry_upd_cash_frcst_upd_sum_line_prtyr_step0.Current_Active, qry_upd_cash_frcst_upd_sum_line_prtyr_step0.[Effective Month], qry_upd_cash_frcst_upd_sum_line_prtyr_step0.[Effective Year], qry_upd_cash_frcst_upd_sum_line_prtyr_step0.Cost, qry_upd_cash_frcst_upd_sum_line_prtyr_step0.[Forecast Type], qry_upd_cash_frcst_upd_sum_line_prtyr_step0.Notes, qry_upd_cash_frcst_upd_sum_line_prtyr_step0.[GA Code], qry_upd_cash_frcst_upd_sum_line_prtyr_step0.Expr3, qry_upd_cash_frcst_upd_sum_line_prtyr_step0.Expr4, qry_upd_cash_frcst_upd_sum_line_prtyr_step0.CashWeek, qry_upd_cash_frcst_upd_sum_line_prtyr_step0.CashStatus, qry_upd_cash_frcst_upd_sum_line_prtyr_step0.Check_date, qry_upd_cash_frcst_upd_sum_line_prtyr_step0.PROD_ID, qry_upd_cash_frcst_upd_sum_line_prtyr_step0.Total_type, qry_upd_cash_frcst_upd_sum_line_prtyr_step0.Expr1, qry_upd_cash_frcst_upd_sum_line_prtyr_step0.Expr2
FROM qry_upd_cash_frcst_upd_sum_line_prtyr_step0;

I have verified field types on all of the above; what is NOT being appended is the ID field (the indexed/no duplicates one) as I thought the system would fill those in for me since it is an autonumber field.
0
 
jwandmrsquaredAuthor Commented:
OH, and I checked all other fields...none are required and all that are indexed are "duplicates OK"
0
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"what is NOT being appended is the ID field (the indexed/no duplicates one) as I thought the system would fill those in for me since it is an autonumber field."
Yes ... the system will do that.  So, you are saying that field is NOT in the query above? What is Finance Contract ID ?

mx
0
 
dqmqCommented:
Looks like your autonumber "seed" is out of sync.  That can happen if you insert a row with an explicit value in the autonumber column.

Open the table in datasheet mode and see if you get the same error there.  when you type in the first non-autonumber field, the autonumber field should self-populate with what access thinks is the "next" value.  I'm thinking that number will be too small.

Suggested fix:
make an exact copy of the problem table structure (emtpy table)
change the autonumber field to a long
open the problem table in datasheet mode and type in a value in the "new" line to see what the next autonumber is.
copy rows from the problem table that have keys higher than the next autonumber
delete rows from the problem table that have keys higher than the next autonumber
run an append query to reload the copied rows with newly assigned keys


Find and fix the insert that is corrupting your autonumber.


 

0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Actually ... do a Compact and Repair on this db now ...

mx
0
 
dqmqCommented:
Compact and Repair can't hurt, may work, but it's a long shot.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Exactly.  Can't Hurt.

mx
0
 
LambertHeenanCommented:
Compact and repair is the usual route to fix problems with autonumber seeds.
0
 
jwandmrsquaredAuthor Commented:
DGMG comment identified the problem perfectly, and compact/repair fixed it.  Once again, I am kept from many moments of frustration thanks to the guys on this site!!!
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Long Live Compact & Repair, DeCompile and DAO !

mx
0
 
dqmqCommented:
Let's hope you find the culprit.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 5
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now