DB Error Trapping

[eveCalypso]

Good Day All,

I would like to trap Access Errors which originated from a databasis query. I.e. Duplicate Primary Key etc - to give a better error message to the end user.

I have figured out that I can use the Error Event for this, but that means that I will need a list of possible error codes. Is this list available somewhere? (I am using a SQL 2000 back-end)

It is the last major thing I still need to do, and ANY help would be most appreciated.

EvE

 

[vbajock]

It depends on your data access method. Are you using ADO or DAO to process records from SQL server?

 

[eveCalypso]

Sorry about that! I am using ADO.

Regards,
EvE

 

[vbajock]

First, you set up the handler by putting

Dim errLoop As Error
Dim strError As String


On Error Goto AdoError

at the top of your sub or function. ADO errors have a nifty little Error Collection that pretty much tells you everything about your error. Assuming your connection is named Conn1, your error handler would look like this:

'*******
AdoError:
'*******
i = 1

' Process
StrTmp = StrTmp & vbCrLf & "VB Error # " & Str_(Err.Number)
StrTmp = StrTmp & vbCrLf & " Generated by " &_ Err.Source
StrTmp = StrTmp & vbCrLf & " Description " & _Err.Description

' Enumerate Errors collection and display properties of
' each Error object.
Set Errs1 = Conn1.Errors
For Each errLoop In Errs1
With errLoop
StrTmp = StrTmp & vbCrLf & "Error #" & i & ":"
StrTmp = StrTmp & vbCrLf & " ADO Error #"_ & .Number
StrTmp = StrTmp & vbCrLf & " Description "_ & .Description
StrTmp = StrTmp & vbCrLf & " Source " _& .Source
i = i + 1
End With
Next

MsgBox StrTmp
'***************************

'at this point you would code what to do if an error occurred. It could be a select case to handle different errors, resume, resume next, quit, etc, depending on your app. For more info Google "ADO Error Collection"

 

[eveCalypso]

Thank you for your help.

I assume then that its a trial and error business picking up error codes that mean specific things. I find that quite strange - in my eyes there should at least be a standard list, which is what I am after.

Best Regards,
EvE