Solved

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

Posted on 2009-04-09
4
1,249 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
  • 2
4 Comments
 
LVL 22

Accepted Solution

by:
Kelvin Sparks earned 250 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 250 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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Problem with SqlConnection 5 115
Access printing shortcut in Ms Access 10 35
Mssql SQL query 14 27
Access Migration to Sql Server 2 19
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

706 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now