Using an Excel VBA-defined variable as an "IN" statement of SQL command text

Question

I am trying to pass a variable into the command text of a SQL Query in my VBA code, but keep running into a "Type mismatch" error. I have written a simple equation in my spreadsheet that re-creates the syntax of an IN statement and I just want to plop that into the Query.

So, Cell A1 contains the following:

(1, 2, 3)

And the relevant snippets of my VBA are as follows:

Dim x as String
x = Sheets("SheetName").Range("A1")

And then in the long mess of code that is the re-creation of the command text of my query (that I created via the "record" feature), I am trying to inject the variable as follows:

"Where table.field in " & x & " and table2.field <> 0"

If it matters:

• The field in the table is stored as (int, not null).
• I've used the MsgBox(x) to verify that it is storing the string appropriately
• The string is not greater than the 255 character limit
• I have tried using the standard '?' feature to pass a parameter with MSQuery, also with no luck

I have gotten this kind of trick to work in the past, but only ever with a single variable. The list of what needs to be put into the IN statement is going to constantly change, so I was just trying to avoid having to call each one out explicitly, or have to return 100% of the data and then filter it out after the fact.

Any help would be appreciated! Open to other ways of accomplishing the same thing; just had hopes this would work as it is quite easy since the spreadsheet is already set up.

Update:

Working on this again today, I have discovered that I oversimplified the example, I knew the string character limit was not being breached and so I assumed length of the IN clause would be OK. As is so often the case, I should not have assumed this.

What I have found is that the length of the IN clause is a factor, even when under the 255 limit. Everything will work fine when the complete length of Cell A1 contains a max of 210 characters; by way of another example:

(1, 2, 3, 4, 5, 6, 7, 8, 9 ,10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 19000101, 20160101, 20160530, 20160704, 20160905, 20161124, 20161125, 20161223, 20161226, 20170102, 20170103456789123)

If I tack another value onto the end of that last number, to jump the character count up to 211, I will encounter the "Type mismatch" error.

I attempted to avoid this by creating another variable in which to house the remainder of the string, but this too produced the same error. It would appear if I try to stuff anything over 210 into a single portion of the code, I get the error.

With the assumption that Cell A1 now contains the 210-length version, the below code will execute fine, if A1 contains more than 210 characters, it will give me "Type mismatch":

Dim x As String
x = Sheets("SheetName").Range("A1")

    With ActiveWorkbook.Connections("Query from Database"). _
        ODBCConnection
        .BackgroundQuery = False
        .CommandText = Array( _
        "USE Database" & Chr(13) & "" & Chr(10) & "SELECT var.UOM, var.UOMClass" & Chr(13) & "" & Chr(10) & "FROM dbo.Variance var" & Chr(13) & "" & Chr(10) & "WHERE var.UOMC" _
        , "lass in" & x & "and var.ModType <>0")
        .CommandType = xlCmdSql
        .Connection = _
        "ODBC;DSN=Database;UID=user01;Trusted_Connection=Yes;APP=Microsoft Office 2010;WSID=COM1077;DATABASE=Database"
        .RefreshOnFileOpen = False
        .SavePassword = False
        .SourceConnectionFile = ""
        .SourceDataFile = ""
        .ServerCredentialsMethod = xlCredentialsMethodIntegrated
        .AlwaysUseConnectionFile = False
    End With
    With ActiveWorkbook.Connections("Query from Database")
        .Name = "Query from Database"
        .Description = ""
    End With
    ActiveWorkbook.Connections("Query from Database").Refresh

For the moment I have just hard-coded the ~230-length IN statement into the actual command text, and the query runs just fine, but as this list will not only change but grow, I am still curious if a solution exists.

Answer 1

Each element of the CommandText array is limited to a maximum length of 255 characters, so you need to make sure you don't exceed that. One approach is to build your SQL as a single string and then pass it to a function which will return an array of parts, each of which are within the limit.

See example below - SplitMeUp will return an array of strings each of max length 200 characters

Sub Tester()
    Dim s As String, arr, n, sep
    For n = 1 To 200
        s = s & sep & n
        sep = "-"
    Next n

    arr = SplitMeUp(s)

    Debug.Print Join(arr, vbLf)

End Sub



Function SplitMeUp(strIn As String)
    Const MAX_LEN As Long = 200
    Dim rv(), n, i
    n = Application.Ceiling(Len(strIn) / MAX_LEN, 1)
    ReDim rv(0 To n - 1)
    For i = 1 To n
        rv(i - 1) = Mid(strIn, 1 + ((i - 1) * MAX_LEN), MAX_LEN)
    Next i
    SplitMeUp = rv
End Function

In your use case:

.CommandText = SplitMeUp( _
    "USE Database " & vbCrLf & "SELECT var.UOM, var.UOMClass" & _
     vbCrLf & " FROM dbo.Variance var WHERE var.UOMClass in" & _
     x & " and var.ModType<>0")