VBA Working with ADO Creating parameterized commands
Example
Any time SQL executed through an ADO connection needs to contain user input, it is considered best practice to parameterize it in order to minimize the chance of SQL injection. This method is also more readable than long concatenations and facilitates more robust and maintainable code (i.e. by using a function that returns an array of Parameter).
In standard ODBC syntax, parameters are given ? "placeholders" in the query text, and then parameters are appended to the Command in the same order that they appear in the query.
Note that the example below uses the OpenDatabaseConnection function from the Making a connection to a data source for 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 = ? WHERE Bar = ?"
.CommandType = adCmdText
'Create the parameters.
Dim fooValue As ADODB.Parameter
Set fooValue = .CreateParameter("FooValue", adNumeric, adParamInput)
fooValue.Value = 42
Dim condition As ADODB.Parameter
Set condition = .CreateParameter("Condition", adBSTR, adParamInput)
condition.Value = "Bar"
'Add the parameters to the Command
.Parameters.Append fooValue
.Parameters.Append condition
.Execute
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: The example above demonstrates a parameterized UPDATE statement, but any SQL statement can be given parameters.
