[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 9091
  • Last Modified:

Try Catch exceptions ??

I do most of my programming in vb.net and in a lot of cases I swallow the GUI error message in the Try Catch End Try loop and write to a table or log file and report a more friendly warning back to the user.

I need to know how to do the equivalent in VBA.

My vb.net method

Try

Catch oex As OleDbException
          messagebox.show("blah blah blah")
Catch ex As Exception
          messagebox.show("blah blah blah")
Finally
   If dc.State = ConnectionState.Open Then dc.Close
End Try


Okay, now onto my VBA code I'm trying to add a similar method to to trap on whether the update command was successful or not.

Dim Connection As New ADODB.Connection
Dim RecordSet As New ADODB.RecordSet
Dim sqlUpdate As String

sqlUpdate = "Update Material Set Matl_Desription ='" & descr & "' Where Material='" & matl & "'"

Connection.Open ConnectionString
RecordSet.Open sqlUpdate, Connection, adOpenStatic, adLockReadOnly

Connection.Close: Set Connection = Nothing


I'm trying to catch errors if the update statement has a problem in order to create an audit trail for a series of update statement my program will be running.
0
JMO9966
Asked:
JMO9966
3 Solutions
 
appariCommented:
you have to use on error goto statement as follows

on error goto ErrX
Dim Connection As New ADODB.Connection
Dim RecordSet As New ADODB.RecordSet
Dim sqlUpdate As String

sqlUpdate = "Update Material Set Matl_Desription ='" & descr & "' Where Material='" & matl & "'"

Connection.Open ConnectionString
RecordSet.Open sqlUpdate, Connection, adOpenStatic, adLockReadOnly

Connection.Close: Set Connection = Nothing

goto safeExit

ErrX:
  ' handle the errors here
  resume safeExit

safeExit:
   exit sub/exit function
0
 
jjardineCommented:
you would need to look at the On ERROR Resume Next  or ON Error GOTO  Label    settings.

DIM Connection as New ADODB.Connection
Dim RecordSet as New ADODB.Recordset
Dim sqlUpdate as String

sqlUpdate = "...."
ON ERROR GOTO Error_Handler
Connection.Open ConnectionString
RecordSet.Open ....

Exit Sub
Error_Handler:   ' Handle your errors here.    Should be an Err.Number,  Err.Description  etc.
Connection.Close
End Sub
0
 
carlsiyCommented:
'VBA sample same as VB 6
Private Sub DivideByZero()
    On Error GoTo DivideByZero_Err
    Dim intNumber As Integer
    intNumber = 1
    MsgBox "Here's a common error: " & intNumber / 0
  DivideByZero_End:
    Exit Sub
  DivideByZero_Err:
    Select Case Err.Number
        Case 11 ' Division by zero.
            MsgBox "You can't divide " & intNumber & " by 0."
        Case Else ' Unanticipated error.
            MsgBox "Error " & Err.Number & " in procedure DivideByZero: " & _
                Err.Description & "."
    End Select
    Resume Next
 End Sub

' Visual Basic .NET Equivalent
Private Sub DivideByZero()
    Dim intNumber As Integer = 1
    Try
        intNumber /= 0
        MsgBox("Here's a common error: " & intNumber)
    Catch e As System.OverflowException
        MsgBox("You can't divide " & intNumber & " by 0.")
    Catch e As System.Exception ' Unanticipated error.
        MsgBox("Error in " & e.Source & ": " & e.Message)
    Finally
        ' Recovery code goes here.
    End Try
End Sub
0
 
JMO9966Author Commented:
Thanks guys!!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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