Executing Parameterized Queries
Parameterized queries can be reused and mitigate SQL injection attacks. You can execute SELECT and other SQL data manipulation statements as parameterized queries. Parameterized queries take arrays as input.
Example: Searching Microsoft Access
The following example macro executes a parameterized search: When you run the macro, you are prompted to enter a search value.
Sub DoSelectParams()
On Error GoTo Error
pShipCountry = InputBox("ShipCountry:", "Get ShipCountry")
If pShipCountry = False Then
Exit Sub
End If
Dim module As New ExcelComModule
module.SetProviderName ("Access")
Cursor = Application.Cursor
Application.Cursor = xlWait
Dim nameArray
nameArray = Array("ShipCountryParam")
Dim valueArray
valueArray = Array(pShipCountry)
Query = "SELECT ShipName, ShipCity FROM Orders WHERE ShipCountry = @ShipCountryParam"
module.SetConnectionString ("DataSource=C:\\My.accdb")
If module.Select(Query, nameArray, valueArray) Then
Dim ColumnCount As Integer
ColumnCount = module.GetColumnCount
For Count = 0 To ColumnCount - 1
Application.ActiveSheet.Cells(1, Count + 1).Value = module.GetColumnName(Count)
Next
Dim RowIndex As Integer
RowIndex = 2
While (Not module.EOF)
For columnIndex = 0 To ColumnCount - 1
If Conversion.CInt(module.GetColumnType(columnIndex)) = Conversion.CInt(vbDate) And Not IsNull(module.GetValue(columnIndex)) Then
Application.ActiveSheet.Cells(RowIndex, columnIndex + 1).Value = Conversion.CDate(module.GetValue(columnIndex))
Else
Application.ActiveSheet.Cells(RowIndex, columnIndex + 1).Value = module.GetValue(columnIndex)
End If
Next
module.MoveNext
RowIndex = RowIndex + 1
Wend
MsgBox "The SELECT query was successful."
Else
MsgBox "The SELECT query failed."
End If
module.Close
Application.Cursor = Cursor
Exit Sub
Error:
MsgBox "ERROR: " & Err.Description
module.Close
Application.Cursor = Cursor
End Sub