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

He leído mucho sobre la inyección de SQL y el uso de parámetros de fonts como bobby-tables.com . Sin embargo, estoy trabajando con una aplicación compleja en Access, que tiene una gran cantidad de SQL dynamic 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 concatenación de cadenas en el argumento WhereCondition
  • Agregados de dominio como DLookUp que usan concatenación de cadenas

Las consultas están estructuradas 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 la frecuencia con la que uso los parámetros de los parámetros en varias publicaciones

Hay muchas formas de usar parámetros en las consultas. Trataré de proporcionar ejemplos para la mayoría de ellos, y dónde son aplicables.

Primero, discutiremos 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 un control en un informe

Implementación de ejemplo:

 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 

Esto está disponible para los siguientes usos:

Al usar DoCmd.RunSQL , las consultas normales (en la GUI), las fonts de registro de formularios e informes, filtros de formularios e informes, agregados de dominio, DoCmd.OpenForm y DoCmd.OpenReport

Esto no está disponible para los siguientes usos:

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


Usando TempVars como parámetros

TempVars en Access son variables disponibles en todo el mundo, que se pueden establecer en VBA o utilizando macros. Se pueden reutilizar para múltiples consultas.

Implementación de ejemplo:

 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 a TempVars el uso de parámetros al abrir formularios o informes sobre la referencia a nombres de control, ya que si el objeto que se abre se cierra, los TempVars permanecen disponibles. Recomiendo usar nombres únicos de TempVar para cada formulario o informe, para evitar rarezas al actualizar formularios o informes.


Usar 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 ejemplo:

 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 establecer 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, así 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 que sean explícitos, agregue una statement de 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 establecer muchas cosas en los conjuntos de registros DAO para que utilicen parámetros, como conjuntos de registros de formularios, conjuntos de registros de cuadros de listas 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 Command.Parameters a la colección Command.Parameters . ADO requiere que sea más explícito que cualquiera de las formas anteriores de usar parámetros. Si bien algunos de los argumentos (como la longitud) se pueden omitir a veces, no lo recomiendo.

Implementación de ejemplo:

 '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 = ?p1 And Field2 = ?p2" .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, named 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" .Parameters.Append .CreateParameter("@FirstParameter", adVarWChar, adParamInput, Len(Me.Field1), Me.Field1) .Parameters.Append .CreateParameter("@SecondParameter", adInteger, adParamInput, 8, Me.Field2) Set rs = .Execute 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 cualquier lugar de su aplicación.

Construí una clase de generador de consultas bastante básica para resolver el problema de la concatenación de cadenas y para 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í:

SELECT * FROM tblEmployees WHERE 1 = 1 AND (StartDate> # 3/29/2018 # OR StatusChangeDate> # 3/29/2018 #) Y (StatusIndicator IN (‘A’, ‘L’) O Grade> 10) AND ( Salario> 9999.99) Y (Retirado = Falso) ORDEN POR ID ASC;

Cada predicado está envuelto en parens para manejar las cláusulas Y / O enlazadas, y los parámetros con el mismo nombre solo tienen que declararse una vez. El código completo está en mi github y se reproduce a continuación. También tengo una versión para consultas de paso a través de Oracle que usa parámetros ADODB. Eventualmente, 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 (eg, 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 ' =============================================================================