Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2009-04-09
4
Medium Priority
?
1,491 Views
Last Modified: 2013-11-27
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_Form_BeforeUpdate:
    Exit Sub
 
Err_Form_BeforeUpdate:
    MsgBox err.Number & " - " & err.Description
    Resume Exit_Form_BeforeUpdate
End Sub

Open in new window

0
Comment
Question by:Bill543
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 22

Accepted Solution

by:
Kelvin Sparks earned 1000 total points
ID: 24113051
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.


Kelvin
0
 

Author Comment

by:Bill543
ID: 24113084
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.
0
 
LVL 44

Assisted Solution

by:Leigh Purvis
Leigh Purvis earned 1000 total points
ID: 24113098
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

0
 

Author Comment

by:Bill543
ID: 24113238
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.
0

Featured Post

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question