VBA Error Handling

just a simple example

Sub StartPROC()

    Debug.Print „start“ ‚ write to immediate
        
    Dim Con As ADODB.Connection, SQL As String

    Set Con = New ADODB.Connection
    SQL = „Testdb.scripts“
    
    On Error GoTo errHandler
    
    Con.ConnectionString = „DSN=Testdb;Uid=Test;Pwd=test123;“
    Con.Open
    Con.Execute (SQL)
    Con.close
    Set Con = Nothing
    
    Debug.Print „end“ ‚ write to immediate
DoCleanUp:

    ‚ do other clean up here

    Exit Sub ‚important to exit sub before errHandler
    
errHandler:
    Dim ShortErr, ErrorPath As String
    ErrorPath = „StartPROC() „
    ShortErr = Left(Err.Description, 150 – Len(ErrorPath))
    Debug.Print „print: “ & Err.Description & “ “ & Err.Number & “ “ & Err.Source ‚ write to immediate
Resume DoCleanUp

End Sub

more examples here and an explanation of all error handling methods here

Advertisements

VBA Access Sleep Command

add this function to the top of your vba-script
Public Declare Sub Sleep Lib „kernel32“ (ByVal dwMilliseconds As Long)

in the following you can use this funcation to sleep for eg 1 second
Sleep 1000