Microsoft Query in VBA

I just noticed this if it will help anyone explain what to do, but looks like the macro recorder only recorded part of the sql code from the microsoft query. Maybe it is too long?


Is it possible to split the sql code in 2 pcs and place back together at the
".commandText"

Thanks for looking, Mike

It appears to have missed this part of the code:

YEAR=83))
 
GROUP BY ORDER.DIV, ORDER.USER, ORDER.OHCUST, ORDER.CUS, ORDER.MON, ORDER.DAY, ORDER.YEAR, ORDER.LAB, ORDER.OHNUM, ORDER.OHSEQ, ORDER.OHPST, ORDER.OHCOM1, ORDER.OHCOM2, ORDER.OHCOM3, SUFFMSTP.NSUFFX

Try taxing the .CommandText and assigning it to a string variable. Then do .CommandText = StringVariable

    SQLString = "SELECT ORDER.DIV, ORDER.USER, ORDER.OHCUST, ORDER.CUS, ORDER.MON, ORDER.DAY, ORDER.YEAR, ' ', ORDER.LAB, ORDER.OHNUM, ORDER.OHSEQ, ORDER.OHPST, ' ', ' ', ' ', ' ', Count(*), Sum(DET.ODLQYO), ' ', ORDER.OHCOM1, ORDER.OHCOM2, ORDER.OHCOM3," & _
                "SUM(CASE WHEN XREF.RFSUM = 'EA' THEN (DET.ODLQYO / XREF.RFCQTY) WHEN XREF.RFSUM = 'IT' THEN (DET.ODLQYO / XREF.RFCIQY)" & _
                "ELSE DET.ODLQYO END), SUFFMSTP.NSUFFX " & _
                "FROM NBJDTA.DET DET " & _
                "INNER JOIN NBJDTA.ORDER ORDER ON ORDER.DIV = DET.ODDIV AND ORDER.OHNUM = DET.ODNUM AND ORDER.OHSEQ = DET.ODSEQ AND ORDER.LAB = DET.ODLAB " & _
                "INNER JOIN PRODFA.SUFFMSTP SUFFMSTP ON (CASE WHEN ORDER.OHXSR6 <> '' THEN ORDER.OHXSR6 ELSE ORDER.LAB END) = SUFFMSTP.NCO " & _
                "LEFT OUTER JOIN NBJDTA.PDMSTILF XREF ON (CASE WHEN ORDER.OHXSR6 <> '' THEN ORDER.OHXSR6 ELSE ORDER.LAB END) = XREF.RFLAB AND DET.ODPROD = XREF.RFPRD AND XREF.RFSTS = 'ACT' " & _
                "WHERE  ((ORDER.USER='45') AND (ORDER.OHPST=15) AND (ORDER.YEAR=83)) " & _
                "GROUP BY ORDER.DIV, ORDER.USER, ORDER.OHCUST, ORDER.CUS, ORDER.MON, ORDER.DAY, ORDER.YEAR, ORDER.LAB, ORDER.OHNUM, ORDER.OHSEQ, ORDER.OHPST, ORDER.OHCOM1, ORDER.OHCOM2, ORDER.OHCOM3, SUFFMSTP.NSUFFX"

    With Selection.QueryTable
        .Connection = Array(Array( _
        "ODBC;DRIVER={iSeries Access ODBC Driver};SYSTEM=TESTBOX;DBQ=TEST1;DFTPKGLIB=QGPL;LANGUAGEID=ENU;PKG=QGPL/DEFAULT(IBM),2,0,1,0,512;S" _
        ), Array("SL=;SIGNON=;"))
        .CommandText = SQLString
        .Refresh BackgroundQuery:=False
    End With

Thanks for looking, however...

I'm getting an ODBC Error at this line of your script:

.Refresh BackgroundQuery:=False

I have also tried this below: But I get an SQL Syntax Error.

The SQL Script works in the microsoft query wizard so not sure how to correct either error. I will verify the SQL script but i'm sure it works.

Also noticed something when I use the recorder and it brings over all kinds of extra stuff for example the CHR(13) & CHR(10) and seems to add "," to each line of code. This wasn't a problem till the script got larger.

Sub junker2()
    Dim sConn As String
    Dim sSql As String
    Dim oQt As QueryTable
 
 
    sSql = "SELECT ORDER.ODIV, ORDER.USER, ORDER.OHCUST, ORDER.CUS, ORDER.MON, ORDER.DAY, ORDER.YEAR, ' ',"
 
    sSql = sSql & "ORDER.OLAB, ORDER.OHNUM, ORDER.OHSEQ, ORDER.OHPST, ' ', ' ', ' ', ' ', Count(*), Sum(DET.ODLQYO),"
    sSql = sSql & "ORDER.OHCOM1, ORDER.OHCOM2, ORDER.OHCOM3,SUM(CASE WHEN XREF.RFSUM = 'EA' THEN (DET.ODLQYO / XREF.RFCQTY)"
    sSql = sSql & "WHEN XREF.RFSUM = 'IT' THEN (DET.ODLQYO / XREF.RFCIQY)ELSE DET.ODLQYO END), SUFFMSTP.NSUFFX"
 
    sSql = sSql & "FROM NBJDTA.DET DET"
    sSql = sSql & "INNER JOIN NBJDTA.ORDER ORDER ON ORDER.ODIV = DET.ODDIV AND ORDER.OHNUM = DET.ODNUM AND ORDER.OHSEQ =DET.ODSEQ AND ORDER.OLAB = DET.ODLAB"
    sSql = sSql & "INNER JOIN PRODFA.SUFFMSTP SUFFMSTP ON (CASE WHEN ORDER.OHXSR6 <> '' THEN ORDER.OHXSR6 ELSE ORDER.OLAB END) = SUFFMSTP.NCO"
    sSql = sSql & "LEFT OUTER JOIN NBJDTA.PDMSTILF XREF ON (CASE WHEN ORDER.OHXSR6 <> '' THEN ORDER.OHXSR6 ELSE ORDER.OLAB END) = XREF.RFLAB AND DET.ODPROD = XREF.RFPRD AND XREF.RFSTS = 'ACT'"
    sSql = sSql & "WHERE  ((ORDER.USER='45') AND (ORDER.OHPST=15) AND (ORDER.YEAR=83))"
    sSql = sSql & "GROUP BY ORDER.ODIV, ORDER.USER, ORDER.OHCUST, ORDER.CUS, ORDER.MON, ORDER.DAY, ORDER.YEAR, ORDER.OLAB, ORDER.OHNUM, ORDER.OHSEQ, ORDER.OHPST, ORDER.OHCOM1, ORDER.OHCOM2, ORDER.OHCOM3, SUFFMSTP.NSUFFX"
 
 
    With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DRIVER={iSeries Access ODBC Driver};SYSTEM=TESTBOX;DBQ=TEST;DFTPKGLIB=QGPL;LANGUAGEID=ENU;PKG=QGPL/DEFAULT(IBM),2,0,1,0,512;S" _
        ), Array("SL=;SIGNON=;")), Destination:=Range("A2"), Sql:=sSql)
 
 
    .Refresh BackgroundQuery:=False
    End With
 
 
   'Set oQt = ActiveSheet.QueryTables.Add( _
        Connection:=sConn, _
        Destination:=Range("A2"), _
        Sql:=sSql)
    'oQt.Refresh
End Sub

Chr(10) and Chr(13) are just line breaks. You do not need them as part of the SQL statement. They merely ake it easier to read for the human eye. The "," is because it is creating an array out of your SQL statement. Each line in your original SQL statement gets a new row in the array. There is no reason for this. You can get rid of the commas and the Array() function.

For your code you just posted, make sure you include trailing spaces at the end of each line.

When you concatenate this
"ORDER.OHCOM1, ORDER.OHCOM2, ORDER.OHCOM3,SUM(CASE WHEN XREF.RFSUM = 'EA' THEN (DET.ODLQYO / XREF.RFCQTY)"
with this
"WHEN XREF.RFSUM = 'IT' THEN (DET.ODLQYO / XREF.RFCIQY)ELSE DET.ODLQYO END), SUFFMSTP.NSUFFX"

You get this

"ORDER.OHCOM1, ORDER.OHCOM2, ORDER.OHCOM3,SUM(CASE WHEN XREF.RFSUM = 'EA' THEN (DET.ODLQYO / XREF.RFCQTY)WHEN XREF.RFSUM = 'IT' THEN (DET.ODLQYO / XREF.RFCIQY)ELSE DET.ODLQYO END), SUFFMSTP.NSUFFX"

It looks like I had a similar issue with my last code, which may be the reason for the error on Refresh.

Originally Posted by snb

In the method 'querytables.add' you use 4 arguments.
As far as I know it takes only 3.

This line came from the Macro Recorder

With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DRIVER={iSeries Access ODBC Driver};SYSTEM=TESTBOX;DBQ=TEST;DFTPKGLIB=QGPL;LANGUAGEID=ENU;PKG=QGPL/DEFAULT(IBM),2,0,1,0,512;S" _
        ), Array("SL=;SIGNON=;")), Destination:=Range("A2")

I added this

, Sql:=sSql)

to try this method out:

Dim sConn As String
  Dim sSql As String
  Dim oQt As QueryTable
 
  sConn = "ODBC;DSN=MS Access 97 Database;"
  sConn = sConn & "DBQ=C:\Program Files\Microsoft Office\"
  sConn = sConn & "Office\Samples\Northwind.mdb;"
  sConn = sConn & "DefaultDir=C:\Program Files\Microsoft Office\Office\Samples;"
  sConn = sConn & "DriverId=281;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"
 
  sSql = "SELECT Customers.CustomerID, Customers.CompanyName, Customers.City "
  sSql = sSql & "FROM `C:\Program Files\Microsoft Office\Office\Samples\Northwind`"
  sSql = sSql & ".Customers Customers "
  sSql = sSql & "WHERE (Customers.City='Berlin') "
  sSql = sSql & "ORDER BY Customers.CompanyName"
 
 Set oQt = ActiveSheet.QueryTables.Add( _
      Connection:=sConn, _
      Destination:=Range("a1"), _
      Sql:=sSql)
 
  oQt.Refresh

But I could not get the sConn part to work for me.

Would you have any ideas?

Thank You, Mike

I got this working it was just a manner of putting the spaces in the right places and or adding some.

Much like whiz said...

For anyone doing projects like this I found it easier to do my sql outside the VBA editor and then copy it back into the script. I also did a small bit of code at a time till it all worked correcly.

Any Thanks All for the help...

Mike

Sub junker2()
    
    Dim sSql As String
    
    
    With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DRIVER={iSeries Access ODBC Driver};SYSTEM=TESTBOX;DBQ=TEST;DFTPKGLIB=QGPL;LANGUAGEID=ENU;PKG=QGPL/DEFAULT(IBM),2,0,1,0,512;S" _
        ), Array("SL=;SIGNON=;")), Destination:=Range("A2"))
        
              
       
    strSQL = "SELECT ORDER.ODIV, ORDER.ODIVI, ORDER.DACUST, ORDER.CUS, ORDER.OMON, ORDER.ODAY, ORDER.OYER, ' ', ORDER.OLAB,  SUFP.NSUFFX,ORDER.DANUM, ORDER.DASEQ, ORDER.DAPST, ' ',SUM(CASE WHEN XREF.RFSUM = 'EA' THEN (ODET.LQDO / XREF.RFCQTY) WHEN XREF.RFSUM = 'IT' THEN (ODET.LQDO / XREF.RFCIQY) "
    strSQL = strSQL & "ELSE ODET.LQDO END),' ', Count(*), Sum(ODET.LQDO), ' ', ORDER.CCO1, ORDER.CCO2, ORDER.CCO3,ORDER.DASCTY, ORDER.DASST, ORDER.OZIP "
    strSQL = strSQL & "FROM NYSYC.ODET ODET "
    strSQL = strSQL & "INNER JOIN NYSYC.ORDER ORDER ON ORDER.ODIV = ODET.ODIVI AND ORDER.DANUM = ODET.ODNUM AND ORDER.DASEQ = ODET.ODSEQ AND ORDER.OLAB = ODET.ODLAB "
    strSQL = strSQL & "INNER JOIN PRO6.SUFP SUFP ON (CASE WHEN ORDER.OSR3 <> '' THEN ORDER.OSR3 ELSE ORDER.OLAB END) = SUFP.ONC "
    strSQL = strSQL & "LEFT OUTER JOIN NYSYC.PLIFT XREF ON (CASE WHEN ORDER.OSR3 <> '' THEN ORDER.OSR3 ELSE ORDER.OLAB END) = XREF.RFLAB AND ODET.ODPROD = XREF.RFPRD AND XREF.RFSTS = 'ACT' "
    strSQL = strSQL & "WHERE  ((ORDER.ODIVI='45') AND (ORDER.DAPST=15) AND (ORDER.OYER=83)) "
    strSQL = strSQL & "GROUP BY ORDER.ODIV, ORDER.ODIVI, ORDER.DACUST, ORDER.CUS, ORDER.OMON, ORDER.ODAY, ORDER.OYER, ORDER.OLAB, ORDER.DANUM, ORDER.DASEQ, ORDER.DAPST, ORDER.CCO1, ORDER.CCO2, ORDER.CCO3, SUFP.NSUFFX, ORDER.OZIP, ORDER.DASCTY, ORDER.DASST "
 
    .CommandText = strSQL
    .Refresh BackgroundQuery:=False
    End With
 
   
End Sub

Yeah, I see what you mean. I added the spaces in to the second script and now that one gets the ODBC Error, But not the SQL Error.

I'll try to work with both scripts some to see if I can get something back.

Let me know if you have any more thoughts.

Thanks for the help.

Mike

I believe Sql:=sSql) should be CommandText := sSql

I could be wrong.