Vez en cuando, he tenido que crear una cadena SQL en VBA y ejecutar con Docmd.RunSql(). Siempre he construido estas cadenas por la concatenación de variables en la cadena, e.g:

Dim mysqlstring as String
mysqlstring = "INSERT INTO MyTable (Field1, Field2, Field3 ...) VALUES ("
mysqlstring = mysqlstring + Me.TextMyField1 + ", " 'parameter comments
mysqlstring = mysqlstring + Me.TextMyField2 + ", " 
mysqlstring = mysqlstring + Me.TextMyField3 + ", " 
...
mysqlstring = mysqlstring + ");"
Docmd.RunSql mysqlstring

VBA no parece tener un único operador de concatenación (como +=) y mientras esto no se ve ideal, al menos puedo comentar cada uno de los parámetros y modificarlos de forma independiente. Esto hace que sea más fácil de leer y a cambio de un monstruo de la cadena concatenada. Pero todavía parece una terrible manera de construir cadenas SQL. Yo tengo uno con cerca de 50 parámetros de trabajo, a fin de 50 líneas de mysqlstring = mysqlstring +.... No lindo.

Por cierto, que descarta el uso de la línea-continuaciones para dar formato a la cadena, ya que hay una límite en el número de línea de las continuaciones que usted puede utilizar en una sola cadena (sugerencia: menos de 50). También, VBA no te deja poner un comentario después de la línea de continuación, grr!

Hasta hace poco, pensé que este era el único camino para construir estas cadenas. Pero recientemente he visto un patrón diferente, la inyección de los parámetros en la cadena, como esta pregunta (VB.NET) que he publicado una respuesta, y se preguntó si no había un equivalente de Parameters.AddWithValue() para VBA, o si incluso sería mejor que la concatenación de cadenas de enfoque. Así que pensé que esta se merece su propia pregunta. Tal vez hay algo que me estoy perdiendo aquí.

Puede que algunos de los Acceso a expertos por favor aclarar cuáles son las mejores prácticas para la construcción de cadenas SQL en Access/VBA.

OriginalEl autor Dale | 2009-11-26

7 Comentarios

  1. 9

    Tengo un parte de horas de la aplicación con un razonablemente complejo conjugado de trabajo de entrada de transacciones de forma. Hay un montón de validación de datos, el cálculo de la tasa y el resto del código. Decidí usar lo siguiente para crear mi SQL Insertar/Actualizar campos.

    Las variables strSQLInsert, strSQLValues, strSQLUpdate se forma a nivel de las cadenas.

    Muchas líneas de los siguientes:

    Call CreateSQLString("[transJobCategoryBillingTypesID]", lngJobCategoryBillingTypesID)

    seguido por:

    If lngTransID = 0 Then
        strSQL = "INSERT into Transactions (" & Mid(strSQLInsert, 3) & ") VALUES (" & Mid(strSQLValues, 3) & ")"
    Else
        strSQL = "UPDATE Transactions SET " & Mid(strSQLUpdate, 3) & " WHERE transID=" & lngTransID & ";"
    End If
    
    conn.Open
    conn.Execute strSQL, lngRecordsAffected, adCmdText

    Nota de que en medio de las líneas de quitar el líder «, «. lngTrans es el valor de la autonumérico primamy kay.

    Sub CreateSQLString(strFieldName As String, varFieldValue As Variant, Optional blnZeroAsNull As Boolean)
    '    Call CreateSQLString("[<fieldName>]", <fieldValue>)
    
    Dim strFieldValue As String, OutputValue As Variant
    
        On Error GoTo tagError
    
        ' if 0 (zero) is supposed to be null
        If Not IsMissing(blnZeroAsNull) And blnZeroAsNull = True And varFieldValue = 0 Then
            OutputValue = "Null"
        ' if field is null, zero length or ''
        ElseIf IsNull(varFieldValue) Or Len(varFieldValue) = 0 Or varFieldValue = "''" Then
            OutputValue = "Null"
        Else
            OutputValue = varFieldValue
        End If
    
        ' Note that both Insert and update strings are updated as we may need the insert logic for inserting
        '    missing auto generated transactions when updating the main transaction
        ' This is an insert
        strSQLInsert = strSQLInsert & ", " & strFieldName
        strSQLValues = strSQLValues & ", " & OutputValue
        ' This is an update
        strSQLUpdate = strSQLUpdate & ", " & strFieldName & " = " & OutputValue
    
        On Error GoTo 0
        Exit Sub
    
    tagError:
    
        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure CreateSQLString of VBA Document Form_LabourEntry"
        Exit Sub
    End Sub

    Veo que los otros carteles son todos utilizando el método Execute. El problema con DoCmd.Ejecutarsql es que se puede omitir los errores. Cualquiera de las siguientes mostrará los mensajes de error recibidos por la consulta. Si se utiliza DAO, uso Currentdb.Ejecutar strSQL,dbfailonerror.. ADO Para uso CurrentProject.Conexión.Ejecutar strCommand, lngRecordsAffected, adCmdText puede quitar el docmd.estableceradvertencias líneas.

    Si vas a utilizar docmd.estableceradvertencias asegúrese de que usted pone la Verdad en cualquier código de control de errores así. De lo contrario, cosas extrañas pueden suceder más adelante, especialmente mientras se está trabajando en la aplicación. Por ejemplo, ya no será el «¿desea guardar los cambios» mensaje si cerca de un objeto. Esto puede significar que los cambios no deseados, supresiones o adiciones deberán ser guardados en su MDB.

    También el rendimiento puede ser significativamente diferente entre los dos métodos. Una publicación declaró currentdb.ejecutar tomó dos segundos mientras docmd.ejecutarsql tomó ocho segundos. Como siempre YMMV.

    Si utiliza CurrentDB, en lugar de asignar CurrentDB a una variable, usted puede devolver los registros afectados.
    Lo cierto es que, sigo olvidando ese detalle. Tengo un fragmento de código para lidiar con la situación.
    Y usted no puede usar SELECT @de IDENTIDAD para la obtención de la Autonumérico PK de la última inserción.

    OriginalEl autor Tony Toews

  2. 4

    Añadir a lo que @astander ha dicho, puede crear una definición de consulta (con parámetros) y guardarlo como parte de la base de datos.

    por ejemplo,

    Parameters dtBegin DateTime, dtEnd DateTime;
    INSERT into myTable (datebegin, dateend) values (dtBegin, dtEnd)

    Asumir, ha guardado con un nombre myTableInsert, puede escribir el código de la siguiente

    dim qd as QueryDef
    set qd = CurrentDB.QueryDefs("myTableInsert")
    qd.Parameters("dtBegin").Value = myTextFieldHavingBeginDate
    qd.Parameters("dtEnd").Value = myTextFieldHavingEndDate    
    qd.Execute

    Nota: no he probado este trozo de código. Pero, supongo que esta debe ser la misma.

    Espero que esto le da suficiente información para empezar.

    Interesante. Se la guardó querydef myTableInsert se muestran en la lista de Consultas?
    Sí. Usted tendrá que crear con la consulta INSERT con los PARÁMETROS de la cláusula, como se muestra arriba.
    Como he dicho en mi post, usted no tiene que crear una consulta, usted puede crear un temporal de consulta utilizando sólo el sql argumento de CreateQueryDef. Por favor vea el enlace proporcionado para obtener más detalles.
    No sé, ¿cómo es diferente que la escritura de SQL en el código. Con un objeto querydef, el SQL es parte de la consulta que se almacenan en la base de datos. Me gusta tu ejemplo, pero todavía se requiere de un grande de la consulta debe ser escrito en el código de VB, que es lo que creo que no es necesario. Espero que lea esto 🙂

    OriginalEl autor shahkalpesh

  3. 4
        Private Sub Command0_Click()
    Dim rec As Recordset2
    Dim sql As String
    Dim queryD As QueryDef
    
        'create a temp query def.
        Set queryD = CurrentDb.CreateQueryDef("", "SELECT * FROM [Table] WHERE Val = @Val")
        'set param vals
        queryD.Parameters("@Val").Value = "T"
        'execute query def
        Set rec = queryD.OpenRecordset
    End Sub

    OriginalEl autor Adriaan Stander

  4. 1

    Como otros han dicho, es probable que sea mejor utilizar parámetros en el primer lugar. Sin embargo, …

    Yo, también, he perdido un operador de concatenación, de haber acostumbrado .= en PHP. En algunos casos, he escrito una función para hacerlo, aunque no es específico para la concatenación de cadenas SQL. Aquí está el código para que yo uso para la creación de una cadena de consulta para un GET de HTTP:

      Public Sub AppendQueryString(strInput As String, _
           ByVal strAppend As String, Optional ByVal strOperator As String = "&")
        strAppend = StringReplace(strAppend, "&", "&amp;")
        strInput = strInput & strOperator & strAppend
      End Sub

    Y un ejemplo de donde me has llamado:

      AppendQueryString strOutput, "InventoryID=" & frm!InventoryID, vbNullstring
      AppendQueryString strOutput, "Author=" & URLEncode(frm!Author)

    …y así sucesivamente.

    Ahora, para la construcción de las cláusulas where de SQL, usted podría considerar la posibilidad de algo así como un contenedor de Aplicación.BuildCriteria:

      Public Sub ConcatenateWhere(ByRef strWhere As String, _
          strField As String, intDataType As Integer, ByVal varValue As Variant)
        If Len(strWhere) > 0 Then
           strWhere = strWhere & " AND "
        End If
        strWhere = strWhere & Application.BuildCriteria(strField, _
           intDataType, varValue)
      End Sub

    Entonces llamada que como:

      Dim strWhere As String
    
      ConcatenateWhere strWhere,"tblInventory.InventoryID", dbLong, 10036
      ConcatenateWhere strWhere,"tblInventory.OtherAuthors", dbText, "*Einstein*"
      Debug.Print strWhere
      strSQL = "SELECT tblInventory.* FROM tblInventory"
      strSQL = strSQL & " WHERE " & strWhere

    …y la Depuración.La impresión sería la salida de esta cadena:

      tblInventory.InventoryID=10036 AND tblInventory.OtherAuthors Like "*Einstein*"

    Variaciones en los que podría ser más útil para usted, es decir, es posible que desee tener un opcional operador de concatenación (por lo que podría tener O), pero probablemente sería hacer que mediante la construcción de una sucesión de DONDE las cadenas y la concatenación de ellos con O línea por línea en el código, ya que había probable es que desee colocar su paréntesis cuidadosamente para asegurarse de que el Y/O de prioridad se ejecuta correctamente.

    Ahora, nada de esto realmente direcciones de la concatenación de los VALORES de una instrucción INSERT, pero me pregunta con qué frecuencia usted está realmente en la inserción de los valores literales en un Acceso de la aplicación. A menos que usted está usando un formulario independiente para la inserción de registros, que será el uso de un formulario para insertar registros, y por lo tanto ninguna instrucción SQL. Así, para VALORES de cláusulas, parece que en una aplicación que no debería tener esto muy a menudo. Si usted está encontrando a sí mismo necesidad de escribir los VALORES de cláusulas como esta, yo sugeriría que usted no está usando el Acceso correctamente.

    Que, dijo, podría usar algo como esto:

      Public Sub ConcatenateValues(ByRef strValues As String, _
          intDatatype As Integer, varValue As Variant)
        Dim strValue As String
    
        If Len(strValues) > 0 Then
           strValues = strValues & ", "
        End If
        Select Case intDatatype
          Case dbChar, dbMemo, dbText
            ' you might want to change this to escape internal double/single quotes
            strValue = Chr(34) & varValue & Chr(34)
          Case dbDate, dbTime
            strValue = "#" & varValue & "#"
          Case dbGUID
            ' this is only a guess
            strValues = Chr(34) & StringFromGUID(varValue) & Chr(34)
          Case dbBinary, dbLongBinary, dbVarBinary
            ' numeric?
          Case dbTimeStamp
            ' text? numeric?
          Case Else
            ' dbBigInt , dbBoolean, dbByte, dbCurrency, dbDecimal, 
            '   dbDouble, dbFloat, dbInteger, dbLong, dbNumeric, dbSingle
            strValue = varValue
        End Select
        strValues = strValues & strValue
      End Sub

    …que iba a concatenar los valores de la lista y, a continuación, usted podría concatenar en toda su cadena SQL (entre los paréntesis de los VALORES (a) de la cláusula).

    Pero como han dicho otros, es probable que sea mejor utilizar parámetros en el primer lugar.

    OriginalEl autor David-W-Fenton

  5. 1

    Por lo que vale, yo uso un formato ligeramente diferente, mediante el Acceso del carácter de salto de línea «_». Yo también uso el operador de concatenación «&». La razón principal es para mejorar la legibilidad:

    Dim db as Database: Set db = Current Db
    Dim sql$
    sql= "INSERT INTO MyTable (Field1, Field2, Field3 ...Fieldn) " & _
         "VALUES (" & _
         Me.TextMyField1 & _
         "," & Me.TextMyField2 & _
         "," & Me.TextMyField3 & _
         ...
         "," & Me.TextMyFieldn & _
         ");"
    db.Execute s
    Set db = nothing

    OriginalEl autor maxhugen

  6. 0

    Me gustaría utilizar el enfoque de arriba, con cada uno de los parámetros en una línea separada es agradable y fácil de depurar y agregar a.

    Sin embargo, si usted realmente no le gustaba de esa manera, entonces usted podría mirar a una consulta de parámetros. Un poco menos flexible, pero en algunos casos ligeramente más rápido.

    O de otra manera sería definir una función pública para la inserción en la tabla y pasar los valores como parámetros.

    Sin embargo me quedaría con lo que usted tiene, pero sería agradable si VBA entendería =+

    Hay más ventajas a los parámetros (tanto de código SQL de acceso a datos y middleware) que el rendimiento por ejemplo fuerte de tipos de datos, valores de parámetro predeterminados, protección contra la inyección SQL, escapar caracteres especiales, etc. Además, estoy seguro de que hay casos en que una Base de datos de Access Motor de PROCEDIMIENTO realiza realmente peor que la de SQL dinámico.
    De acuerdo en que las consultas de parámetros ofrecen muchos beneficios. Sólo lo mencioné el lado del funcionamiento de las cosas como un posible efecto secundario como el acceso se mantenga el plan de consulta y no tener que trabajar como lo haría con una instrucción SQL genera sobre la marcha. Habiendo dicho que con hardware moderno dudo que se diera cuenta cualquier diferencia, así que hay un montón de razones para usar el parámetro cuadernillos pero el rendimiento muy bajo en la lista
    Yo wold sugieren la edición de su publicación en el cambio de la redacción de «me gustaría utilizar el enfoque de arriba», para incluir los carteles nombre. Dependiendo de cómo las preguntas son vistos, el más nuevo, más antiguos y los votos de la secuencia de las respuestas puede cambiar.
    Lo siento Tony, soy un poco nuevo en este sitio y estoy acostumbrado a un foro normal, donde el orden no cambia. Voy a cambiar mi publicación de estilo para referirse a los puestos por su nombre
    Kevin, te escucho. Estoy mucho tiempo Fidonet BBS/NNTP chico mí mismo, así que esta online en el foro de cosas toma un poco de tiempo para acostumbrarse.

    OriginalEl autor Kevin Ross

  7. 0

    Una de las cosas que he hecho en el pasado es crear un sistema para el análisis de código SQL para encontrar parámetros y almacenar los parámetros en una tabla. Me gustaría escribir mi las consultas de MySQL exterior de Acceso. Entonces todo lo que tenía que hacer era abrir el archivo de Acceso y estaría listo para ser actualizado sobre la marcha cada vez que quería correr.

    Que realmente era un proceso complicado, pero yo estaría encantado de desenterrar el código de la próxima semana cuando puedo volver a trabajar si usted está interesado.

    OriginalEl autor Ben McCormack

Dejar respuesta

Please enter your comment!
Please enter your name here