VBA Query that pulls data from both excel table and SQL database

Question

I have been working on this for quite a while and I can't seem to figure it out. I am trying to create a query in excel that combines information from an excel table and database. I can do each of them separately no problem.

Here is the VBA code for the excel query:

    Sub ExcelQuery()
'
    Range("Table_Query_from_Excel_Files5[[#Headers],[Customer:]]").Select
    With Selection.ListObject.QueryTable
        .Connection = Array(Array("ODBC;DSN=Excel Files;DBQ=Z:\OEM Office\Trevor Weinrich\Projects\BOM Template 2.0\BOM template 2017-08-16 1.xlsm;DefaultDir=Z:\OEM Office\Trevor Weinrich\Projects\BOM Template 2.0;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;"))
        .CommandText = Array( _
        "SELECT `BOM$`.`Customer:`" & Chr(13) & "" & Chr(10) & "FROM `BOM$` `BOM$`" & Chr(13) & "" & Chr(10) & "WHERE (`BOM$`.`Customer:` Is Not Null)" _
        )
        .Refresh BackgroundQuery:=False
    End With
'
End Sub

And here is the VBA code for the database query:

Sub DatabaseQuery()
'
    With Selection.ListObject.QueryTable
        .Connection = _
        "ODBC;DSN=OEM;Description=OEM;UID=trevor.weinrich;Trusted_Connection=Yes;APP=Microsoft Office 2016;WSID=DFP-OEM-0913-A;DATABASE=OEM"
        .CommandText = Array( _
        "SELECT DISTINCT p21_view_item_uom.item_id, p21_view_item_uom.unit_of_measure, p21_view_item_uom.purchasing_unit" & Chr(13) & "" & Chr(10) & "FROM OEM.dbo.p21_view_item_uom p21_view_item_uom" & Chr(13) & "" & Chr(10) & "WHERE (p21_view_item_uom.delete_flag=" _
        , "'N')" & Chr(13) & "" & Chr(10) & "ORDER BY p21_view_item_uom.item_id")
        .Refresh BackgroundQuery:=False
    End With
'
End Sub

I want to join these together because there are about 140,000 line items in the database query, and I only care about the the instances where the "item_id" field from the database matches up with the "Customer:" field. I just can't figure out how to join the two of them. I would greatly appreciate the help.

Here is the code where I am just trying to pull in a variable that gives me the error after 165 characters:

Sub Update_Item_Tables()
'
' UOM_Update Macro
'
Dim Items As String
Items = Sheets("UOM").Range("K1").Value

    Sheets("UOM").Visible = True
    Sheets("UOM").Select
    Range("Table_Query_from_OEM[[#Headers],[item_id]]").Select
    With Selection.ListObject.QueryTable
        .Connection = _
        "ODBC;DSN=OEM;Description=OEM;UID=trevor.weinrich;Trusted_Connection=Yes;APP=Microsoft Office 2016;WSID=DFP-OEM-0913-A;DATABASE=OEM"
        .CommandText = Array( _
        "SELECT DISTINCT p21_view_item_uom.item_id, p21_view_item_uom.unit_of_measure, p21_view_item_uom.purchasing_unit" & Chr(13) & "" & Chr(10) & _
        "FROM OEM.dbo.p21_view_item_uom p21_view_item_uom" & Chr(13) & "" & Chr(10) & _
        "WHERE (p21_view_item_uom.item_id In (" _
        , _
        "" & Items & ")) AND (p21_view_item_uom.delete_flag='N')" & Chr(13) & "" & Chr(10) & _
        "ORDER BY p21_view_item_uom.purchasing_unit DESC" _
        )
        .Refresh BackgroundQuery:=False
    End With

End Sub

Answer 1

Yikes... some of that code is... let's call it unique. But I won't ask questions, lets just deal with the problem you are asking about for now.

The goal is to check your long Items string so you only need one Sql command to grab your data. But the string needs to be in chunks less than 165 characters.

As I mentioned before, this is a sloppy way to do it, but if they have you locked out of your SQL database for development purposes, this approach will work:

You can split your Item string into multiple strings each less than 165 characters. Then you can use OR statements in your SQL to use the IN function with each Item string that has data.

First, in your VBA, you need to:

'Delcare your Variables

Dim SQLText As String
Dim Items2 As String
Dim Items3 As String
Dim boolItemFlag2 As Boolean
Dim boolItemFlag3 As Boolean
Dim i As Integer

' Initialize Variables

boolItemFlag2 = False
boolItemFlag3 = False

Then you can do your processing to build your 'Items' string. I'm assuming it's comma delimited something like this:

Items = "'123456','234567'"

After it's built you can:

' Get your Items list and check for length over 164

If Not IsNull(Len(Items)) Then
   If Len(Items) > 164 Then
      boolItemFlag2 = True
      i = 0

      ' Find last comma delimiter before the cut off of 164

      While (Mid(Items, 164 - i, 1) <> ",")
         i = i + 1
      Wend

      ' Set Items2 to everything after the last comma (before pos 164).  Then Left Trim away possible spaces.

      Items2 = LTrim(Mid(Items, 164 - i + 1, Len(Items) - 164 + i))

      ' Reset Items to everything up to that comma we found but not including it.

      Items = Left(Items, 164 - i - 1)

      ' Your Item list is now split into 2 different string variables

   End If
End If

If Not IsNull(Len(Items2)) Then
   If Len(Items2) > 164 Then
      boolItemFlag3 = True
      ' Use the same logic above to split Items2 into the Items3 variable.
      ' You may need to duplicate again for Items4 etc if your Item List is huge.
      ' If you'd need beyond Items5, I'd probably go back to the two query approach.
      ' But it's your choice.
   End If
End If

Then, after you have your Item lists split into variables that are each small enough, you can build a unique SQL string (called SQLText) and insert OR statements to include items strings that have data:

' Build your SQL String Here (after you have determined how many item strings you have)

SQLText = "SELECT DISTINCT p21_view_item_uom.item_id, p21_view_item_uom.unit_of_measure, p21_view_item_uom.purchasing_unit" & Chr(13) & "" & Chr(10) & _
     "FROM OEM.dbo.p21_view_item_uom p21_view_item_uom" & Chr(13) & "" & Chr(10) & _
     "WHERE (p21_view_item_uom.item_id In (" _
     & _                  ' <-- I changed your comma into an & because I have no clue...
     "" & Items & ")"

If boolItemFlag2 Then   ' Add this OR statement if Items2 has data
     SQLText = SQLText & " OR p21_view_item_uom.item_id In (" & Items2 & ")"
End If

If boolItemFlag3 Then   ' Add this OR statement if Items3 has data
     SQLText = SQLText & " OR p21_view_item_uom.item_id In (" & Items3 & ")"
End If

' Add more OR statements if you have Item lists beyond 3.

' Now tack on your remaining SQL code:

SQLText = SQLText & ") AND (p21_view_item_uom.delete_flag='N')" & Chr(13) & "" & Chr(10) & _
     "ORDER BY p21_view_item_uom.purchasing_unit DESC"

Now the String SQLText will have your complete SQL code that you want to send to the server. You can execute this message box if you want to verify the string is correct:

MsgBox SQLText

You need to make one more modification. When you call the database, you need to put the variable SQLText into the commandtext array (in place of your old code):

' Now you can put your SQLText variable into your ODBC call:

With Selection.ListObject.QueryTable
    .Connection = _
"ODBC;DSN=OEM;Description=OEM;UID=trevor.weinrich;Trusted_Connection=Yes;APP=Microsoft Office 2016;WSID=DFP-OEM-0913-A;DATABASE=OEM"
    .CommandText = Array(SQLText)
    .Refresh BackgroundQuery:=False
End With

Hope that helps :)

Answer 2

Run your Excel query.

Loop through the result set and build a list of OR conditions, such as item_id = 'ABC' Or item_id = 'PQR' and so on.

Instead of using an IN condition, use the (huge) list of OR conditions in a single large string.

Concatenate this list of OR conditions with rest of your query as Criterion for your database query.

While the length of an IN phrase may have a restriction, the length of the complete SQL statement may be higher.

Of course, if the no. of customers going to appear in your query is too large, this will have unexpected issues depending on how large it is.