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).