sql - ¿Cómo uso los parámetros en VBA en los diferentes contextos en Microsoft Access?

ms-access access-vba

Question

He leído mucho sobre la inyección SQL y el uso de parámetros de fuentes como bobby-tables.com . Sin embargo, estoy trabajando con una aplicación compleja en Access, que tiene una gran cantidad de SQL dinámico con concatenación de cadenas en todo tipo de lugares.

Tiene las siguientes cosas que quiero cambiar y agregar parámetros para evitar errores y permitirme manejar nombres con comillas simples, como Jack O''Connel.

Usa:

• DoCmd.RunSQL para ejecutar comandos SQL
• Conjuntos de registros DAO
• Conjuntos de registros ADODB
• Formularios e informes, abiertos con DoCmd.OpenForm y DoCmd.OpenReport , utilizando la concatenación de cadenas en el argumento WhereCondition
• Agregados de dominio como DLookUp que usan concatenación de cadenas

Las consultas se estructuran principalmente de esta manera:

DoCmd.RunSQL "INSERT INTO Table1(Field1) SELECT Field1 FROM Table2 WHERE ID = " & Me.SomeTextbox

¿Cuáles son mis opciones para usar parámetros para estos diferentes tipos de consultas?

_{Esta pregunta está pensada como un recurso, por lo frecuente cómo utilizo los comentarios de parámetros en varias publicaciones}

Answer 1

Hay muchas formas de usar parámetros en las consultas. Intentaré proporcionar ejemplos para la mayoría de ellos, y donde sean aplicables.

Primero, analizaremos las soluciones exclusivas de Access, como formularios, informes y agregados de dominio. Luego, hablaremos sobre DAO y ADO.

Usar valores de formularios e informes como parámetros

En Access, puede usar directamente el valor actual de los controles en formularios e informes en su código SQL. Esto limita la necesidad de parámetros.

Puede consultar los controles de la siguiente manera:

Forms!MyForm!MyTextbox para un control simple en un formulario

Forms!MyForm!MySubform.Form!MyTextbox para un control en un subformulario

Reports!MyReport!MyTextbox para controlar un informe

Implementación de muestra:

DoCmd.RunSQL "INSERT INTO Table1(Field1) SELECT Forms!MyForm!MyTextbox" ''Inserts a single value
DoCmd.RunSQL "INSERT INTO Table1(Field1) SELECT Field1 FROM Table2 WHERE ID = Forms!MyForm!MyTextbox" ''Inserts from a different table

Está disponible para los siguientes usos:

Cuando se usa DoCmd.RunSQL , las consultas normales (en la GUI), las fuentes de registros de formularios e informes, los filtros de formularios e informes, los agregados de dominio, DoCmd.OpenForm y DoCmd.OpenReport

Esto no está disponible para los siguientes usos:

Al ejecutar consultas utilizando DAO o ADODB (por ejemplo, abrir conjuntos de registros, CurrentDb.Execute )

Usando TempVars como parámetros

TempVars en Access son variables disponibles a nivel mundial, que se pueden establecer en VBA o usando macros. Se pueden reutilizar para múltiples consultas.

Implementación de muestra:

TempVars!MyTempVar = Me.MyTextbox.Value ''Note: .Value is required
DoCmd.RunSQL "INSERT INTO Table1(Field1) SELECT Field1 FROM Table2 WHERE ID = TempVars!MyTempVar"
TempVars.Remove "MyTempVar" ''Unset TempVar when you''re done using it

La disponibilidad para TempVars es idéntica a la de los valores de formularios e informes: no disponible para ADO y DAO, disponible para otros usos.

Recomiendo TempVars para usar parámetros al abrir formularios o informes sobre referencias a nombres de control, ya que si el objeto que lo abre se cierra, los TempVars permanecen disponibles. Recomiendo el uso de nombres TempVar únicos para cada formulario o informe, para evitar rarezas al actualizar formularios o informes.

Uso de funciones personalizadas (UDF) como parámetros

Al igual que TempVars, puede usar una función personalizada y variables estáticas para almacenar y recuperar valores.

Implementación de muestra:

Option Compare Database
Option Explicit

Private ThisDate As Date


Public Function GetThisDate() As Date
    If ThisDate = #12:00:00 AM# Then
        '' Set default value.
        ThisDate = Date
    End If 
    GetThisDate = ThisDate
End Function


Public Function SetThisDate(ByVal NewDate As Date) As Date
    ThisDate = NewDate
    SetThisDate = ThisDate
End Function

y entonces:

SetThisDate SomeDateValue '' Will store SomeDateValue in ThisDate.
DoCmd.RunSQL "INSERT INTO Table1(Field1) SELECT Field1 FROM Table2 WHERE [SomeDateField] = GetThisDate()"

Además, se puede crear una única función con un parámetro opcional para configurar y obtener el valor de una variable estática privada:

Public Function ThisValue(Optional ByVal Value As Variant) As Variant
    Static CurrentValue As Variant
    '' Define default return value.
    Const DefaultValue  As Variant = Null

    If Not IsMissing(Value) Then
        '' Set value.
        CurrentValue = Value
    ElseIf IsEmpty(CurrentValue) Then
        '' Set default value
        CurrentValue = DefaultValue
    End If
    '' Return value.
    ThisValue = CurrentValue
End Function

Para establecer un valor:

ThisValue "Some text value"

Para obtener el valor:

CurrentValue = ThisValue

En una consulta:

ThisValue "SomeText"  '' Set value to filter on.
DoCmd.RunSQL "INSERT INTO Table1(Field1) SELECT Field1 FROM Table2 WHERE [SomeField] = ThisValue()"

Usando DoCmd.SetParameter

Los usos de DoCmd.SetParameter son bastante limitados, por lo que seré breve. Le permite establecer un parámetro para usar en DoCmd.OpenForm , DoCmd.OpenReport y algunas otras declaraciones DoCmd , pero no funciona con DoCmd.RunSQL , filtros, DAO y ADO.

Implementación de muestra

DoCmd.SetParameter "MyParameter", Me.MyTextbox
DoCmd.OpenForm "MyForm",,, "ID = MyParameter"

Usando DAO

En DAO, podemos usar el objeto DAO.QueryDef para crear una consulta, establecer parámetros y luego abrir un conjunto de registros o ejecutar la consulta. Primero configura el SQL de las consultas, luego usa la colección QueryDef.Parameters para establecer los parámetros.

En mi ejemplo, voy a usar tipos de parámetros implícitos. Si desea hacerlos explícitos, agregue una declaración PARAMETERS a su consulta.

Implementación de muestra

''Execute query, unnamed parameters
With CurrentDb.CreateQueryDef("", "INSERT INTO Table1(Field1) SELECT Field1 FROM Table2 WHERE Field1 = ?p1 And Field2 = ?p2")
    .Parameters(0) = Me.Field1
    .Parameters(1) = Me.Field2
    .Execute
End With

''Open recordset, named parameters
Dim rs As DAO.Recordset
With CurrentDb.CreateQueryDef("", "SELECT Field1 FROM Table2 WHERE Field1 = FirstParameter And Field2 = SecondParameter")
    .Parameters!FirstParameter = Me.Field1 ''Bang notation
    .Parameters("SecondParameter").Value = Me.Field2 ''More explicit notation
    Set rs = .OpenRecordset
End With

Si bien esto solo está disponible en DAO, puede configurar muchas cosas en los conjuntos de registros DAO para que usen parámetros, como conjuntos de registros de formularios, conjuntos de registros de cuadros de lista y conjuntos de registros de cuadros combinados. Sin embargo, dado que Access usa el texto, y no el conjunto de registros, al ordenar y filtrar, esas cosas pueden resultar problemáticas si lo hace.

Usando ADO

Puede usar parámetros en ADO utilizando el objeto ADODB.Command . Use Command.CreateParameter para crear parámetros y luego Command.Parameters a la colección Command.Parameters .

Puede usar la colección .Parameters en ADO para declarar parámetros explícitamente, o pasar una matriz de parámetros al método Command.Execute para pasar parámetros implícitamente.

ADO no admite parámetros con nombre. Si bien puede pasar un nombre, no se procesa.

Implementación de muestra:

''Execute query, unnamed parameters
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
With cmd
    Set .ActiveConnection = CurrentProject.Connection ''Use a connection to the current database
    .CommandText = "INSERT INTO Table1(Field1) SELECT Field1 FROM Table2 WHERE Field1 = ? And Field2 = ?"
    .Parameters.Append .CreateParameter(, adVarWChar, adParamInput, Len(Me.Field1), Me.Field1) ''adVarWChar for text boxes that may contain unicode
    .Parameters.Append .CreateParameter(, adInteger, adParamInput, 8, Me.Field2) ''adInteger for whole numbers (long or integer)
    .Execute
End With

''Open recordset, implicit parameters
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
With cmd
    Set .ActiveConnection = CurrentProject.Connection ''Use a connection to the current database
    .CommandText = "SELECT Field1 FROM Table2 WHERE Field1 = @FirstParameter And Field2 = @SecondParameter"
     Set rs = .Execute(,Array(Me.Field1, Me.Field2))
End With

Se aplican las mismas limitaciones que abrir los conjuntos de registros DAO. Si bien esta forma se limita a ejecutar consultas y abrir conjuntos de registros, puede usar esos conjuntos de registros en otra parte de su aplicación.

Answer 2

He creado una clase de generador de consultas bastante básica para evitar el desorden de la concatenación de cadenas y manejar la falta de parámetros con nombre. Crear una consulta es bastante simple.

Public Function GetQuery() As String

    With New MSAccessQueryBuilder
        .QueryBody = "SELECT * FROM tblEmployees"

        .AddPredicate "StartDate > @StartDate OR StatusChangeDate > @StartDate"
        .AddPredicate "StatusIndicator IN (@Active, @LeaveOfAbsence) OR Grade > @Grade"
        .AddPredicate "Salary > @SalaryThreshhold"
        .AddPredicate "Retired = @IsRetired"

        .AddStringParameter "Active", "A"
        .AddLongParameter "Grade", 10
        .AddBooleanParameter "IsRetired", False
        .AddStringParameter "LeaveOfAbsence", "L"
        .AddCurrencyParameter "SalaryThreshhold", 9999.99@
        .AddDateParameter "StartDate", #3/29/2018#

        .QueryFooter = "ORDER BY ID ASC"
        GetQuery = .ToString

    End With

End Function

La salida del método ToString () se ve así:

SELECCIONE * DE tblEmpleados DONDE 1 = 1 Y (Fecha de inicio> # 3/29/2018 # O StatusChangeDate> # 3/29/2018 #) Y (StatusIndicator IN (''A'', ''L'') O Grado> 10) Y ( Salario> 9999.99) Y (Retirado = Falso) ORDEN POR ID ASC;

Cada predicado está envuelto en parens para manejar cláusulas AND / OR vinculadas, y los parámetros con el mismo nombre solo deben declararse una vez. El código completo está en mi github y se reproduce a continuación. También tengo una version para consultas passthrough de Oracle que usa parámetros ADODB. Finalmente, me gustaría envolver ambos en una interfaz IQueryBuilder.

VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  ''True
END
Attribute VB_Name = "MSAccessQueryBuilder"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = True
''@Folder("VBALibrary.Data")
''@Description("Provides tools to construct Microsoft Access SQL statements containing predicates and parameters.")

Option Explicit

Private Const mlngErrorNumber As Long = vbObjectError + 513
Private Const mstrClassName As String = "MSAccessQueryBuilder"
Private Const mstrParameterExistsErrorMessage As String = "A parameter with this name has already been added to the Parameters dictionary."

Private Type TSqlBuilder
    QueryBody As String
    QueryFooter As String
End Type

Private mobjParameters As Object
Private mobjPredicates As Collection
Private this As TSqlBuilder


'' =============================================================================
'' CONSTRUCTOR / DESTRUCTOR
'' =============================================================================

Private Sub Class_Initialize()
    Set mobjParameters = CreateObject("Scripting.Dictionary")
    Set mobjPredicates = New Collection
End Sub


'' =============================================================================
'' PROPERTIES
'' =============================================================================

''@Description("Gets or sets the query statement (SELECT, INSERT, UPDATE, DELETE), exclusive of any predicates.")
Public Property Get QueryBody() As String
    QueryBody = this.QueryBody
End Property
Public Property Let QueryBody(ByVal Value As String)
    this.QueryBody = Value
End Property

''@Description("Gets or sets post-predicate query statements (e.g., GROUP BY, ORDER BY).")
Public Property Get QueryFooter() As String
    QueryFooter = this.QueryFooter
End Property
Public Property Let QueryFooter(ByVal Value As String)
    this.QueryFooter = Value
End Property


'' =============================================================================
'' PUBLIC METHODS
'' =============================================================================

''@Description("Maps a boolean parameter and its value to the query builder.")
''@Param("strName: The parameter''s name.")
''@Param("blnValue: The parameter''s value.")
Public Sub AddBooleanParameter(ByVal strName As String, ByVal blnValue As Boolean)
    If mobjParameters.Exists(strName) Then
        Err.Raise mlngErrorNumber, mstrClassName & ".AddBooleanParameter", mstrParameterExistsErrorMessage
    Else
        mobjParameters.Add strName, CStr(blnValue)
    End If
End Sub

'' =============================================================================

''@Description("Maps a currency parameter and its value to the query builder.")
''@Param("strName: The parameter''s name.")
''@Param("curValue: The parameter''s value.")
Public Sub AddCurrencyParameter(ByVal strName As String, ByVal curValue As Currency)
    If mobjParameters.Exists(strName) Then
        Err.Raise mlngErrorNumber, mstrClassName & ".AddCurrencyParameter", mstrParameterExistsErrorMessage
    Else
        mobjParameters.Add strName, CStr(curValue)
    End If
End Sub

'' =============================================================================

''@Description("Maps a date parameter and its value to the query builder.")
''@Param("strName: The parameter''s name.")
''@Param("dtmValue: The parameter''s value.")
Public Sub AddDateParameter(ByVal strName As String, ByVal dtmValue As Date)
    If mobjParameters.Exists(strName) Then
        Err.Raise mlngErrorNumber, mstrClassName & ".AddDateParameter", mstrParameterExistsErrorMessage
    Else
        mobjParameters.Add strName, "#" & CStr(dtmValue) & "#"
    End If
End Sub

'' =============================================================================

''@Description("Maps a long parameter and its value to the query builder.")
''@Param("strName: The parameter''s name.")
''@Param("lngValue: The parameter''s value.")
Public Sub AddLongParameter(ByVal strName As String, ByVal lngValue As Long)
    If mobjParameters.Exists(strName) Then
        Err.Raise mlngErrorNumber, mstrClassName & ".AddNumericParameter", mstrParameterExistsErrorMessage
    Else
        mobjParameters.Add strName, CStr(lngValue)
    End If
End Sub

'' =============================================================================

''@Description("Adds a predicate to the query''s WHERE criteria.")
''@Param("strPredicate: The predicate text to be added.")
Public Sub AddPredicate(ByVal strPredicate As String)
    mobjPredicates.Add "(" & strPredicate & ")"
End Sub

'' =============================================================================

''@Description("Maps a string parameter and its value to the query builder.")
''@Param("strName: The parameter''s name.")
''@Param("strValue: The parameter''s value.")
Public Sub AddStringParameter(ByVal strName As String, ByVal strValue As String)
    If mobjParameters.Exists(strName) Then
        Err.Raise mlngErrorNumber, mstrClassName & ".AddStringParameter", mstrParameterExistsErrorMessage
    Else
        mobjParameters.Add strName, "''" & strValue & "''"
    End If
End Sub

'' =============================================================================

''@Description("Parses the query, its predicates, and any parameter values, and outputs an SQL statement.")
''@Returns("A string containing the parsed query.")
Public Function ToString() As String

Dim strPredicatesWithValues As String

    Const strErrorSource As String = "QueryBuilder.ToString"

    If this.QueryBody = vbNullString Then
        Err.Raise mlngErrorNumber, strErrorSource, "No query body is currently defined. Unable to build valid SQL."
    End If
    ToString = this.QueryBody

    strPredicatesWithValues = ReplaceParametersWithValues(GetPredicatesText)
    EnsureParametersHaveValues strPredicatesWithValues

    If Not strPredicatesWithValues = vbNullString Then
        ToString = ToString & " " & strPredicatesWithValues
    End If

    If Not this.QueryFooter = vbNullString Then
        ToString = ToString & " " & this.QueryFooter & ";"
    End If

End Function


'' =============================================================================
'' PRIVATE METHODS
'' =============================================================================

''@Description("Ensures that all parameters defined in the query have been provided a value.")
''@Param("strQueryText: The query text to verify.")
Private Sub EnsureParametersHaveValues(ByVal strQueryText As String)

Dim strUnmatchedParameter As String
Dim lngMatchedPoisition As Long
Dim lngWordEndPosition As Long

    Const strProcedureName As String = "EnsureParametersHaveValues"

    lngMatchedPoisition = InStr(1, strQueryText, "@", vbTextCompare)
    If lngMatchedPoisition <> 0 Then
        lngWordEndPosition = InStr(lngMatchedPoisition, strQueryText, Space$(1), vbTextCompare)
        strUnmatchedParameter = Mid$(strQueryText, lngMatchedPoisition, lngWordEndPosition - lngMatchedPoisition)
    End If

    If Not strUnmatchedParameter = vbNullString Then
        Err.Raise mlngErrorNumber, mstrClassName & "." & strProcedureName, "Parameter " & strUnmatchedParameter & " has not been provided a value."
    End If

End Sub

'' =============================================================================

''@Description("Combines each predicate in the predicates collection into a single string statement.")
''@Returns("A string containing the text of all predicates added to the query builder.")
Private Function GetPredicatesText() As String

Dim strPredicates As String
Dim vntPredicate As Variant

    If mobjPredicates.Count > 0 Then
        strPredicates = "WHERE 1 = 1"
        For Each vntPredicate In mobjPredicates
            strPredicates = strPredicates & " AND " & CStr(vntPredicate)
        Next vntPredicate
    End If

    GetPredicatesText = strPredicates

End Function

'' =============================================================================

''@Description("Replaces parameters in the predicates statements with their provided values.")
''@Param("strPredicates: The text of the query''s predicates.")
''@Returns("A string containing the predicates text with its parameters replaces by their provided values.")
Private Function ReplaceParametersWithValues(ByVal strPredicates As String) As String

Dim vntKey As Variant
Dim strParameterName As String
Dim strParameterValue As String
Dim strPredicatesWithValues As String

    Const strProcedureName As String = "ReplaceParametersWithValues"

    strPredicatesWithValues = strPredicates
    For Each vntKey In mobjParameters.Keys
        strParameterName = CStr(vntKey)
        strParameterValue = CStr(mobjParameters(vntKey))

        If InStr(1, strPredicatesWithValues, "@" & strParameterName, vbTextCompare) = 0 Then
            Err.Raise mlngErrorNumber, mstrClassName & "." & strProcedureName, "Parameter " & strParameterName & " was not found in the query."
        Else
            strPredicatesWithValues = Replace(strPredicatesWithValues, "@" & strParameterName, strParameterValue, 1, -1, vbTextCompare)
        End If
    Next vntKey

    ReplaceParametersWithValues = strPredicatesWithValues

End Function

'' =============================================================================