What error number for Key Violations (A2000 (SR-1))

Topic

New Reply

I’m trying to find out how to trap the error message about being unable to append all the data to the table because records were lost due to key violations. The error message does not provide an error number. When I searched this forum, I found the following post that mentioned that the error number for key violations is 3022.

http://www.wopr.com/cgi-bin/w3t/showflat.p…ew=&sb=&o=&vc=1

However, 3022 is not trapping my key violations. Any help will be appreciated.

Thanks,

Randy

Reply | Quote

Replies

Error numbers 10011 and 10015 are about not being able to append records due to key violations, 10509 and 10515 same for update queries, and 10512 and 10518 same for delete queries.

Reply | Quote

Thanks for the reply Hans, but none of those numbers work either. Here’s the function… maybe it’s something that I’m doing wrong. I can’t seem to trap the error number – have tried to display it in a msgbox and tried to debug.print it, but it doesn’t seem to generate one.

Function AppendCashControlExcel()
On Error GoTo AppendCashControlExcel_Err

””DoCmd.SetWarnings False
DoCmd.TransferSpreadsheet acImport, 8, “CashControl”, “C:BCCRDatabaseCashControl.xls”, True, “ALL!”
DoCmd.SetWarnings True

AppendCashControlExcel_Exit:
Exit Function

AppendCashControlExcel_Err:
If Err.Number = 3022 Then
MsgBox “One or more records were omitted from the import because they had ” & _
“the same ‘Ref ID’, ‘Total’, and ‘GL TransactionDate’.” & vbCrLf & vbCrLf & _
“You may have attempted to load the prior day’s file again. Ensure that you ” & _
“have replaced the prior ‘CashControl.xls’ file with the current one.”, , “Processing Error”
Resume AppendCashControlExcel_Exit
End If
MsgBox Err.Number & ” ” & Error
Resume AppendCashControlExcel_Exit
End Function

Thanks,

Randy

Reply | Quote

Here’s maybe another way to solve this problem, just my 2 cents worth.

1. Input the spreadsheet to a ‘temporary’ table with no indexes.
2. Do a join between both tables (temporary table and your normal table CashControl) and check for any matches and alert the user if there are any, and stop the process here.
3. Then, if all ok, do an append query to add the records from the temporary table to the CashControl table.

If you keep having problems, send the database and spreadsheet (deleting all sensitive data of course) and I’m sure someone can shed some light on it.

Cheers,
Pat

Reply | Quote

Actually, I believe that’s an error that you aren’t going to be able to trap except with an On Error Resume Next before the DoCmd.TransferSpreadsheet line and a test in the next line after it (If Err0 Then … etc.). If you put a breakpoint in at that point, you can bring up the immediate window and type in

? Err

That should print the specific error number. It won’t do you much good though, because you will only know that some of the records you’re trying to import already exist in the target table. It will NOT tell you which ones.

Reply | Quote