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