ODBC Connection String with Multiple Variable "WHERE" requirements

[Dodoblaster]

Hi,

I am a little out of my element with this one:

With VBA in Excel 2010 I am connecting to a SQL server by using ODBC.

This is a two part process:

Part one:
I have created a connection string that gets me the following data: A, B, C, D, E from sheet tpoPurchOrder
Where B is equal to "1"
And
Where C is equal to a changing field under Sheets("Macros").Range("B2")

Here is part one

Sub Part1()
Sheets("Open PO by Vendor").Select
Sheets("Open PO by Vendor").Cells.Clear
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "ODBC;DSN=Connection;Description=Description;UID=USER;PWD=PASSWORD;APP=Microsoft Office 2010;WSID=Workstation;DATABASE=Database" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandText = Array( _
        "SELECT tpoPurchOrder.A, tpoPurchOrder.B, tpoPurchOrder.C, tpoPurchOrder.D, tpoPurchOrder.E, tpoPurchOrder.D" & Chr(13) & "" & Chr(10) & "FROM Databse.dbo.tpoPurchOrder tpoPurchOrder" & Chr(13) & "" & Chr(10) & "WHERE (tpoPurchOrder.CompanyID='DII') AND (tpoPu" _
        , _
        "rchOrder.B=1) AND (tpoPurchOrder.C=" & Sheets("Macros").Range("B2").Value & ")" & Chr(13) & "" & Chr(10) & "ORDER BY tpoPurchOrder.TranNo")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table_Query_from_Mas_5004"
        .Refresh BackgroundQuery:=False
    End With
End Sub

Part one works perfectly.

Part two is where I have problems.
Part two is a little different because I have the same connection, but what changes is the table that I am looking at "tpoPOLine" instead of "tpoPurchOrder" and the where is now going to have a variable number of commands.

I want part two to be depend on part one. Under part one I want the result from column E to be a where statement in part two.

As follows:

In particular: *E3 from Part 1*, etc.

        "SELECT tpoPOLine.Status, tpoPOLine.POKey, tpoPOLine.ItemKey, tpoPOLine.POLineNo, tpoPOLine.UnitCost, tpoPOLine.ExtAmt" & Chr(13) & "" & Chr(10) & "FROM mas500_DII_app.dbo.tpoPOLine tpoPOLine" & Chr(13) & "" & Chr(10) & "WHERE (tpoPOLine.POKey=*E2 from Part 1*) OR (" _
        , _
        "tpoPOLine.POKey=*E3 from Part 1* ) OR (tpoPOLine.POKey=*E4 from Part 1*)" & Chr(13) & "" & Chr(10) & "ORDER BY tpoPOLine.POKey" _
        )

Now my problem is that sometimes the E column from part one ends up being 1 row, sometimes it ends up being 50 rows. I would like the code to change accordingly.

My not so great solution:
1. Pull everything from the SQL server filter once in excel. This is not as efficient as the database has ~300,000 rows in the tpoPOline table and would take a lot longer then needed.
2. Create a nested if table and

Change

WHERE (tpoPOLine.POKey=*E2 from Part 1*) OR (tpoPOLine.POKey=*E3 from Part 1* ) OR (tpoPOLine.POKey=*E4 from Part 1*)"

to the value of that nested if table

The nested if table would be something like =if(isblank(E2),"E1",if(isblank(E3)... etc for ~50 rows. I know the syntax is incorrect but you get my point.

3. Rerun the query for each value in column E. That would require the connection to happen ~50 times which would not be that great, as well as I would have to copy and paste the data after each run as the tables cannot overlap.


What I am looking for is a way to run this only once, on one sheet, without writing a nested if table with 50 if's.

Any help would be great.

 

[jsotola]

this maybe?

.CommandText = Array("SELECT tpoPOLine.Status, tpoPOLine.POKey, tpoPOLine.ItemKey, ", _
                            "tpoPOLine.POLineNo, tpoPOLine.UnitCost, tpoPOLine.ExtAmt" & vbCrLf, _
                     "FROM mas500_DII_app.dbo.tpoPOLine tpoPOLine" & vbCrLf, _
                     "WHERE (tpoPOLine.POKey ", _
                     "IN (SELECT tpoPurchOrder.E" & vbCrLf, _
                         "FROM Databse.dbo.tpoPurchOrder tpoPurchOrder" & vbCrLf, _
                         "WHERE (tpoPurchOrder.CompanyID='DII') ", _
                         "AND (tpoPurchOrder.B=1) ", _
                         "AND (tpoPurchOrder.C=" & Sheets("Macros").Range("B2").Value & ")" & vbCrLf, _
                     ")) & vbCrLf ", _
                     "ORDER BY tpoPOLine.POKey")
                     
                     


                             
use [COLOR=#ff0000]vbCrLf [/COLOR]instead of  [COLOR=#ff0000]Chr(13) & "" & Chr(10)[/COLOR]

 

[Fazza]

FWIW, a format I usually use

  Debug.Print Join$(Array( _
      "SELECT list of fields", _
      "FROM sampletable", _
      "WHERE list of criteria", _
      "HAVING whatever", _
      "ORDER BY 3"), vbCr)

 

[Dodoblaster]

this maybe?

.CommandText = Array("SELECT tpoPOLine.Status, tpoPOLine.POKey, tpoPOLine.ItemKey, ", _
                            "tpoPOLine.POLineNo, tpoPOLine.UnitCost, tpoPOLine.ExtAmt" & vbCrLf, _
                     "FROM mas500_DII_app.dbo.tpoPOLine tpoPOLine" & vbCrLf, _
                     "WHERE (tpoPOLine.POKey ", _
                     "IN (SELECT tpoPurchOrder.E" & vbCrLf, _
                         "FROM Databse.dbo.tpoPurchOrder tpoPurchOrder" & vbCrLf, _
                         "WHERE (tpoPurchOrder.CompanyID='DII') ", _
                         "AND (tpoPurchOrder.B=1) ", _
                         "AND (tpoPurchOrder.C=" & Sheets("Macros").Range("B2").Value & ")" & vbCrLf, _
                     ")) & vbCrLf ", _
                     "ORDER BY tpoPOLine.POKey")
                     
                     


                             
use [COLOR=#ff0000]vbCrLf [/COLOR]instead of  [COLOR=#ff0000]Chr(13) & "" & Chr(10)[/COLOR]

Thanks for this! Led me in the right direction I think.
I tried this and I got

Run-time error '1004':
SQL Syntax Error

Been looking at the code for about an hour now and do not know why it is giving it. Tried changing some things but nothing came close, only breaking it more.

The macro stops running only after setting up the connection and thus it yields some results that I think are significant (but what do I know huh)
In the connection properties under Command text I have the following:

SELECT tpoPOLine.Status, tpoPOLine.POKey, tpoPOLine.ItemKey, tpoPOLine.POLineNo, tpoPOLine.UnitCost, tpoPOLine.ExtAmt
FROM mas500_DII_app.dbo.tpoPOLine tpoPOLine
WHERE (tpoPOLine.POKey IN (SELECT tpoPurchOrder.POkey
FROM Databse.dbo.tpoPurchOrder tpoPurchOrder
WHERE (tpoPurchOrder.CompanyID='DII') AND (tpoPurchOrder.Status=1) AND (tpoPurchOrder.Vendkey=3446)
)) & vbCrLf ORDER BY tpoPOLine.POKey

Which leads me to believe the Syntax error is due to " & vbCrLf " on the last line.

Here is the changed code:

        .CommandText = Array("SELECT tpoPOLine.Status, tpoPOLine.POKey, tpoPOLine.ItemKey, ", _
                                    "tpoPOLine.POLineNo, tpoPOLine.UnitCost, tpoPOLine.ExtAmt" & vbCrLf, _
                            "FROM mas500_DII_app.dbo.tpoPOLine tpoPOLine" & vbCrLf, _
                            "WHERE (tpoPOLine.POKey ", _
                            "IN (SELECT tpoPurchOrder.POkey" & vbCrLf, _
                                "FROM mas500_DII_app.dbo.tpoPurchOrder tpoPurchOrder" & vbCrLf, _
                                "WHERE (tpoPurchOrder.CompanyID='DII') ", _
                                "AND (tpoPurchOrder.Status=1) ", _
                                "AND (tpoPurchOrder.Vendkey=" & Sheets("Macros").Range("B2").Value & ")" & vbCrLf, _
                            ")) & vbCrLf ", _
                            "ORDER BY tpoPOLine.POKey")

 

[Dodoblaster]

By changing the last line in the Command text from Connection Properties from

)) & vbCrLf ORDER BY tpoPOLine.POKey

To this:

))
ORDER BY tpoPOLine.POKey

I was able to get this to work.

Now my question is, how do I make that change in the VB macro?

 

[Dodoblaster]

Figured it out myself shortly after posting this:

The problem with the original code was on the second to last line:

")) & vbCrLf ", _

To work it should be

"))" & vbCrLf , _

Thank you again Jsotola!