VBA Working with ADO Executing non-scalar functions
Example
ADO connections can be used to perform pretty much any database function that the provider supports via SQL. In this case it isn't always necessary to use the Recordset returned by the Execute function, although it can be useful for obtaining key assignments after INSERT statements with @@Identity or similar SQL commands. Note that the example below uses the OpenDatabaseConnection function from the Making a connection to a data source example for the purpose of brevity.
Public Sub UpdateTheFoos()
On Error GoTo Handler
Dim database As ADODB.Connection
Set database = OpenDatabaseConnection(SomeDSN)
If Not database Is Nothing Then
Dim update As ADODB.Command
Set update = New ADODB.Command
'Build the command to pass to the data source.
With update
.ActiveConnection = database
.CommandText = "UPDATE Table SET Foo = 42 WHERE Bar IS NULL"
.CommandType = adCmdText
.Execute 'We don't need the return from the DB, so ignore it.
End With
End If
CleanExit:
If Not database Is Nothing And database.State = adStateOpen Then
database.Close
End If
Exit Sub
Handler:
Debug.Print "Error " & Err.Number & ": " & Err.Description
Resume CleanExit
End Sub
Note that commands sent to the data source are vulnerable to SQL injection, either intentional or unintentional. In general, SQL statements should not be created by concatenating user input of any kind. Instead, they should be parameterized (see Creating parameterized commands).
