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

MS Access - how to trap SQL primary key violation error?

I have an Access 2003 frontend database with linked tables to an SQL 2000 backend. When a user tries to insert a duplicate value in primary key fields during data entry, the SQL server error message pops up with some verbage (...violation of primary key contraint...) that's hard for the user to understand. My current VBA error trap routine does not capture the SQL errors.
How can I prevent the SQL error message from showing and show my own message instead?
Private Sub Form_BeforeUpdate(Cancel As Integer)
   On Error GoTo Err_Form_BeforeUpdate
....VBA body code here...
    Exit Sub
    MsgBox err.Number & " - " & err.Description
    Resume Exit_Form_BeforeUpdate
End Sub

Open in new window

  • 2
2 Solutions
Kelvin SparksCommented:
I'd suggest you do a check for the duplicate (Count of the number of records where PK = your submitted data and if count >0 then tell the user else proceed.

Bill543Author Commented:
Thanks Kelvin - I'll try that and let you know. If its not too slow, I'll do it - my concern is that there are some "speed" issues due to server location.
Leigh PurvisDatabase DeveloperCommented:
Personally, I'd have to say that an initial check of the Count is both an extra hit on the database which is best avoided if possible and also open to the possibility of another insert performed between the check and the subsequent attempt to insert (however small a timeframe that may be, it's still a legitimate concern ;-)
Instead you can intercept the apparent complaint in the form's Error event.
Using code - something like:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
    If DataErr = 3146 Then
        MsgBox "The Primary Key value already exists for another record", vbExclamation
        Response = acDataErrContinue
    End If
End Sub

Open in new window

Bill543Author Commented:
Thanks LPurvis. I've incorporated Kelvin's solution on the "After Update" property of the primary key field and it seems quick enough (probably because the database only has around 1000 records).

I'll try your solution once I get some shut eye.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now