Performing an Action Query

See Also

RDO

In case your application needs to manipulate tables directly, or perform a maintenance operation (like SQL Server’s DBCC functions), you can use the Execute method to run the query directly. In this case, you don’t need ODBC or SQL Server to create a temporary SP to run the query as it’s only being done once. (If this were a regular operation, it would be more efficient to create an SP to do it.) Note that you can use the RowsAffected property to find out the number of rows affected by this query.

Private Sub ExecuteButton_Click()
   sql = "Begin Transaction " _
    & " Update Authors " _
    & " set Year_Born = 1900 where year_born is null" _
    & " rollback transaction"
   Screen.MousePointer = vbHourglass
   cn.Execute sql, rdExecDirect
   ShowRows = cn.RowsAffected
   Screen.MousePointer = vbDefault
End Sub

ADO

When you need to perform an action query, you can take advantage of the Execute method in ADO. In this case, you have to set a few more properties than you do in RDO, but these properties improve data access performance. This is because ADO doesn’t have to poll the server to determine what to do, or how to handle the query. Note that the new output argument for the Execute method returns the number of rows affected. Generally, you don’t see Visual Basic using arguments passed back to the application; just arguments passed to the object interface.

Private Sub ExecuteButton_Click()
   Dim Qy As New ADODB.Command
   Dim Rows As Long
   sql = "Begin Transaction " _
    & " Update Authors " _
    & " set Year_Born = 1900 where year_born is null" _
    & " rollback transaction"
   Qy.ActiveConnection = cn
   Qy.CommandText = sql
   Qy.CommandType = adCmdText
   Qy.Execute Rows
   MsgBox Rows & " rows would have been affected", vbInformation
End Sub