locked
How to return success/failure message in a VBA program that uses ADODB RRS feed

  • Question

  • I have this little VBA program that uses ADODB to execute a SQL Server Agent job remotely.  How do I add a message box at the end that would give some feedback to the user, if the execution command was successfully sent to the server or if it failed?

    Sub Test_SSIS()
       
       Dim conn As Object
       Set conn = CreateObject("ADODB.Connection")
       conn.Provider = "sqloledb"
       conn.Properties("Prompt") = adPromptComplete
       conn.Open = "Data Source=MYSERVER;USER ID=JOHNDOE;"
       
       conn.Execute "exec msdb.dbo.sp_start_job 'Test_remote_job_execution'"
       
    End Sub

    Monday, March 2, 2015 2:55 PM

Answers

  • You can use the ExecuteComplete event of the ADODB Connection object to display a message to the user and include error information if desired.  First, create a new class module and rename it as "Listener" (without quotes).  Ensure that a reference to the Microsoft ActiveX Data Objects 6.1 Library has been set in the VBA editor (Tools menu > References...).

    Then, insert the following code in the class module:

    Public WithEvents conn As ADODB.Connection

    Private Sub conn_ExecuteComplete(ByVal RecordsAffected As Long, ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pCommand As ADODB.Command, ByVal pRecordset As ADODB.Recordset, ByVal pConnection As ADODB.Connection)
        Dim strMessage As String
        If adStatus = adStatusErrorsOccurred Then
            strMessage = "Job completed, but with one or more errors."
        Else
            strMessage = "Job completed successfully."
        End If
        MsgBox strMessage
    End Sub

    Next, modify your VBA program to include the Listener object:

    Sub Test_SSIS()
      
       'Declare Listener class
       Dim objListener As Listener
      
       Dim conn As Object
       Set conn = CreateObject("ADODB.Connection")
       conn.Provider = "sqloledb"
       conn.Properties("Prompt") = adPromptComplete
       conn.Open = "Data Source=MYSERVER;USER ID=JOHNDOE;"
      
       'Create Listener object
       Set objListener = New Listener
       'Point the Listener object's connection variable to your SQL Server connection
       Set objListener.conn = conn
      
       conn.Execute "exec msdb.dbo.sp_start_job 'Test_remote_job_execution'"
      
       'Clean up
       Set objListener = Nothing
       Set conn = Nothing
    End Sub

    You can examine the parameters of the ExecuteComplete event handler to discover information relevant to your requirements.




    Tuesday, March 3, 2015 10:13 PM

All replies

  • You can use the ExecuteComplete event of the ADODB Connection object to display a message to the user and include error information if desired.  First, create a new class module and rename it as "Listener" (without quotes).  Ensure that a reference to the Microsoft ActiveX Data Objects 6.1 Library has been set in the VBA editor (Tools menu > References...).

    Then, insert the following code in the class module:

    Public WithEvents conn As ADODB.Connection

    Private Sub conn_ExecuteComplete(ByVal RecordsAffected As Long, ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pCommand As ADODB.Command, ByVal pRecordset As ADODB.Recordset, ByVal pConnection As ADODB.Connection)
        Dim strMessage As String
        If adStatus = adStatusErrorsOccurred Then
            strMessage = "Job completed, but with one or more errors."
        Else
            strMessage = "Job completed successfully."
        End If
        MsgBox strMessage
    End Sub

    Next, modify your VBA program to include the Listener object:

    Sub Test_SSIS()
      
       'Declare Listener class
       Dim objListener As Listener
      
       Dim conn As Object
       Set conn = CreateObject("ADODB.Connection")
       conn.Provider = "sqloledb"
       conn.Properties("Prompt") = adPromptComplete
       conn.Open = "Data Source=MYSERVER;USER ID=JOHNDOE;"
      
       'Create Listener object
       Set objListener = New Listener
       'Point the Listener object's connection variable to your SQL Server connection
       Set objListener.conn = conn
      
       conn.Execute "exec msdb.dbo.sp_start_job 'Test_remote_job_execution'"
      
       'Clean up
       Set objListener = Nothing
       Set conn = Nothing
    End Sub

    You can examine the parameters of the ExecuteComplete event handler to discover information relevant to your requirements.




    Tuesday, March 3, 2015 10:13 PM
  • Fabulous, that worked like a charm!  Thanks so much!!!!!
    Tuesday, March 3, 2015 10:41 PM