Prepared Statements in VBA

prepared.vbs

' Creates a prepared statement and populates its parameters
'
' Usage:
'
'    Dim stmt as QueryDef
'    Set stmt = PrepareStatement(CurrentDb, "SELECT * FROM T WHERE A = [aval] AND B = [bval]", _
'                                "aval", 42, _
'                                "bval", "Carrot Cake")
'
Public Function PrepareStatement(db As Database, sql As String, ParamArray params()) As QueryDef
    Dim query As QueryDef
    Set query = db.CreateQueryDef(vbNullString, sql)
     
    Dim i As Integer
    For i = LBound(params) To UBound(params) Step 2
        query.Parameters(params(i)).Value = params(i + 1)
    Next
     
    Set PrepareStatement = query
End Function