valor - refrescar pantalla excel vba

Intentando usar variables como valores vba a sql

Question

Dim connection As New adodb.connection        
Dim sql As String
Dim years As Integer
years = 2011

Así que tengo algunas buenas variables declaradas aquí. Ahora voy a mostrar la declaración que funciona perfectamente, sin problemas:

sql = "insert into crimeData(reportYear) values(2011)"
connection.Execute sql

Observe que codifiqué en el año. Ahora, si lo intento:

sql = "insert into crimeData(reportYear) values(years)"
connection.Execute sql

Sustituyendo el nombre de la variable como valor, obtengo el error: No value given for one or more required parameters

Es cierto que estoy asumiendo que debe haber un problema de sintaxis simple o algún meta con variables que me falta. Por favor iluminame. ¿Cómo puedo intercambiar correctamente una variable aquí (para poder usar esto programáticamente, en lugar de la codificación de valores)?

Answer 1

Si tiene una necesidad más exigente de concatenar SQL, puede beneficiarse de esta función:

'' Converts a value of any type to its string representation.
'' The function can be concatenated into an SQL expression as is
'' without any delimiters or leading/trailing white-space.
''
'' Examples:
''   SQL = "Select * From TableTest Where [Amount]>" & CSql(12.5) & "And [DueDate]<" & CSql(Date) & ""
''   SQL -> Select * From TableTest Where [Amount]> 12.5 And [DueDate]< #2016/01/30 00:00:00#
''
''   SQL = "Insert Into TableTest ( [Street] ) Values (" & CSql(" ") & ")"
''   SQL -> Insert Into TableTest ( [Street] ) Values ( Null )
''
'' Trims text variables for leading/trailing Space and secures single quotes.
'' Replaces zero length strings with Null.
'' Formats date/time variables as safe string expressions.
'' Uses Str to format decimal values to string expressions.
'' Returns Null for values that cannot be expressed with a string expression.
''
'' 2016-01-30. Gustav Brock, Cactus Data ApS, CPH.
''
Public Function CSql( _
    ByVal Value As Variant) _
    As String

    Const vbLongLong    As Integer = 20
    Const SqlNull       As String = " Null"

    Dim Sql             As String
    Dim LongLong        As Integer

    #If Win32 Then
        LongLong = vbLongLong
    #End If
    #If Win64 Then
        LongLong = VBA.vbLongLong
    #End If

    Select Case VarType(Value)
        Case vbEmpty            ''    0  Empty (uninitialized).
            Sql = SqlNull
        Case vbNull             ''    1  Null (no valid data).
            Sql = SqlNull
        Case vbInteger          ''    2  Integer.
            Sql = Str(Value)
        Case vbLong             ''    3  Long integer.
            Sql = Str(Value)
        Case vbSingle           ''    4  Single-precision floating-point number.
            Sql = Str(Value)
        Case vbDouble           ''    5  Double-precision floating-point number.
            Sql = Str(Value)
        Case vbCurrency         ''    6  Currency.
            Sql = Str(Value)
        Case vbDate             ''    7  Date.
            Sql = Format(Value, " /#yyyy//mm//dd hh/:nn/:ss/#")
        Case vbString           ''    8  String.
            Sql = Replace(Trim(Value), "''", "''''")
            If Sql = "" Then
                Sql = SqlNull
            Else
                Sql = " ''" & Sql & "''"
            End If
        Case vbObject           ''    9  Object.
            Sql = SqlNull
        Case vbError            ''   10  Error.
            Sql = SqlNull
        Case vbBoolean          ''   11  Boolean.
            Sql = Str(Abs(Value))
        Case vbVariant          ''   12  Variant (used only with arrays of variants).
            Sql = SqlNull
        Case vbDataObject       ''   13  A data access object.
            Sql = SqlNull
        Case vbDecimal          ''   14  Decimal.
            Sql = Str(Value)
        Case vbByte             ''   17  Byte.
            Sql = Str(Value)
        Case LongLong           ''   20  LongLong integer (Valid on 64-bit platforms only).
            Sql = Str(Value)
        Case vbUserDefinedType  ''   36  Variants that contain user-defined types.
            Sql = SqlNull
        Case vbArray            '' 8192  Array.
            Sql = SqlNull
        Case Else               ''       Should not happen.
            Sql = SqlNull
    End Select

    CSql = Sql & " "

End Function